Note: I edited the code below to correct the script syntax.
Using part of Sebastian SQL Script, I have an access database within my office that I have these tables linked via ODBC. What I would like to do is create an equivalent using MS Access query. The SQL script works on the SQL database when I query the records, but I would like to do the same in MS Access. Can anybody translate the query in MS Access?
SELECT Top 1000 UP.PortalID, U.CreatedOnDate, U.UserID, UserName, FirstName, LastName, Displayname, Email,
Street, City, PostalCode -- << User Profile Properties
FROM dbo.dnn_Users U
INNER JOIN dbo.dnn_UserPortals UP ON U.UserID = UP.UserID
-- access each Profile Property:
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Street FROM dbo.dnn_ProfilePropertyDefinition PD INNER JOIN dbo.dnn_UserProfile P ON
PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Street' ) P_Street ON P_TypeOfAccess.PortalID = UP.PortalID AND P_Street.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue City FROM dbo.dnn_ProfilePropertyDefinition PD INNER JOIN dbo.dnn_UserProfile P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'City' ) P_City ON P_kusfCompanyCode.PortalID = UP.PortalID AND P_City.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue PostalCode FROM dbo.dnn_ProfilePropertyDefinition PD INNER JOIN dbo.dnn_UserProfile P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'PostalCode' ) P_City ON P_PostalCode.PortalID = UP.PortalID AND P_PostalCode.UserID = U.UserID
Order by PortalID, UserID,LastName, FirstName
I have been trying to recreate it in access, but I am running into syntax problems.
I appreciate any help,
Chuck