How to convert SQL Query for Access 2007?

Question

Here is my query. I need it to be formatted so that it can run in Access 2007.

SELECT DISTINCT(meters.meterNo),  readings.momentaryIntCount, 
readings.readingDate, 
ServiceLocations.servLoc
FROM readings
inner join EndPoints on readings.endPointId = endpoints.endPointId  
join Meters on endpoints.meterId = meters.meterId
join spus on endpoints.spuid = spus.spuid
join ServiceLocations on meters.serviceLocationId = ServiceLocations.serviceLocationId
left join firmware on endpoints.firmwareId = firmware.firmwareId
    left join virtualgroupassoc v on EndPoints.endpointid = v.objectid
left join groups g on v.groupid = g.groupid and g.grouptypeid in (4, 24) and g.active = 1
WHERE readings.readingDate = '01-01-2015'
and firmware.version >= '18'
and quality = 0
and g.name is null
ORDER BY meterNo

Answer 1

Joining multiple tables in Access is a bit of a bother. You have to nest the joins.

For example if you want to do this in SQL:

SELECT 
    a.columna
    , b.columnb
    , c.columnc
FROM tablea AS a 
LEFT JOIN tableb AS b 
    ON a.id = b.id 
LEFT JOIN tablec AS c 
    ON a.id = c.id

You'll need to do this in Access:

SELECT 
    a.columna
    , b.columnb
    , c.columnc
FROM (
    (tablea AS a) 
    LEFT JOIN tableb AS b ON a.id = b.id) 
    LEFT JOIN tablec AS c ON a.id = c.id

So for your query:

SELECT 
    DISTINCT(meters.meterNo)
    , readings.momentaryIntCount
    , readings.readingDate
    , ServiceLocations.servLoc
FROM ((((((
    (readings)
    INNER JOIN EndPoints 
        ON readings.endPointId = EndPoints.endPointId)  
    JOIN Meters 
        ON EndPoints.meterId = Meters.meterId)
    JOIN spus 
        ON EndPoints.spuid = spus.spuid)
    JOIN ServiceLocations 
        ON meters.serviceLocationId = ServiceLocations.serviceLocationId)
    LEFT JOIN firmware 
        ON EndPoints.firmwareId = firmware.firmwareId)
    LEFT JOIN virtualgroupassoc v 
        ON EndPoints.endpointid = v.objectid)
    LEFT JOIN groups g 
        ON v.groupid = g.groupid AND g.grouptypeid in (4, 24) AND g.active = 1
WHERE readings.readingDate = '01-01-2015'
    and firmware.version >= '18'
    and quality = 0
    and g.name is null
ORDER BY meterNo

Resource:

http://nm1m.blogspot.com/2007/10/multiple-left-joins-in-ms-access.html