Query with multiple inner joins (1 Viewer)

[AlliCarr]

Hi All,

I have a mailing list form in my database were I need to filter organisations by 4 geographical areas. i.e. UK region, local authority, constituency and combined authority. Sometimes I will need to search for and send emails to all organisations for a certain region or local authority. The issue I am having is that not all organisations will have a combined authority so, since I have included these in the query that the form is based on, I only get records where there is a combined authority. I need to be able to identify organisations using the other criteria regardless of whether there is a combined authority or not.

I have attempted to write my own query in Access in SQL view following some advice I have found on various forums but I keep getting a syntax error and I'm not sure why. I have never written my own query in SQL view before so any help would be appreciated.

This is the query. When I save I keep getting the syntax error on join expression and it highlights the 'RegionT.Region' in the first line.

SELECT OrganisationDetailsT.OrganisationName, OrganisastionDetailsT.NonMember, ContactsT.FirstName, ContactsT.LastName, ContactsT.Email, ContactsT.MainContact, RegionT.Region, LocalAuthorityT.LocalAuthority, ConstituencyT.Constituency, CombinedAuthorityT.CombinedAuthority
FROM (((((OrganisationDetailsT INNER JOIN ContactsT ON OrganisationDetailsT.ContactID = ContactsT.ContactID)
INNER JOIN OrganisationDetailsT ON RegionT.RegionID = OrganisationDetailsT.RegionID)
INNER JOIN OrganisationDetailsT ON LocalAuthorityT.LocalAuthorityID = OrganisationDetailsT.LocalAuthorityID)
INNER JOIN OrganisationDetailsT ON ConstituencyT.ConstituencyID = OrganisationDetailsT.ConstituencyID)
INNER JOIN OrganisationDetailsT ON CombinedAuthorityT.CombinedAuthorityID = OrganisationDetailsT.CombinedAuthorityID)
WHERE (RegionT.Region = [Forms]![MailingsF]![RegionSearch]
OR [Forms]![MailingsF]![RegionSearch] IS NULL)
AND (LocalAuthorityT.LocalAuthority = [Forms]![MailingsF]![LASearch]
OR [Forms]![MailingsF]![LASearch] IS NULL)
AND (ConstituencyT.Constituency = [Forms]![MailingsF]![ConstSearch]
OR [Forms]![MailingsF]![ConstSearch] IS NULL)
AND (CombinedAuthorityT.CombinedAuthority = [Forms]![MailingsF]![CombAuthSearch]
OR [Forms]![MailingsF]![CombAuthSearch] IS NULL);

And I have included the SQL view from the original query I made using design view in case it helps.

SELECT OrganisationDetailsT.OrganisationName, ContactsT.FirstName, ContactsT.LastName, ContactsT.Email, ContactsT.MainContact,
RegionT.Region, LocalAuthorityT.LocalAuthority, [FirstName] & " " & [LastName] AS Expr1, ConstituencyT.Constituency,
CombinedAuthorityT.CombinedAuthority, OrganisationDetailsT.NonMember
FROM ((ConstituencyT INNER JOIN (RegionT INNER JOIN LocalAuthorityT ON RegionT.RegionID = LocalAuthorityT.RegionID)
ON ConstituencyT.RegionID = RegionT.RegionID) INNER JOIN (OrganisationDetailsT INNER JOIN CombinedAuthorityT
ON OrganisationDetailsT.CombinedAuthorityID = CombinedAuthorityT.CombinedAuthorityID) 
ON (RegionT.RegionID = OrganisationDetailsT.RegionID) AND (LocalAuthorityT.LocalAuthorityID = OrganisationDetailsT.LocalAuthorityID)
AND (ConstituencyT.ConstituencyID = OrganisationDetailsT.ConstituencyID) AND (LocalAuthorityT.CombinedAuthorityID = CombinedAuthorityT.CombinedAuthorityID)) INNER JOIN ContactsT 
ON OrganisationDetailsT.OrganisationURN = ContactsT.OrganisationURN
WHERE (((ContactsT.MainContact)=True) AND ((OrganisationDetailsT.NonMember)=False)
AND ((IIf(IsNull([Forms]![MailingsF]![RegionSearch]),[Region] Like "*",[Region]=[forms]![MailingsF]![RegionSearch]))<>False)
AND ((IIf(IsNull([Forms]![MailingsF]![LASearch]),[LocalAuthority] Like "*",[LocalAuthority]=[forms]![MailingsF]![LASearch]))<>False)
AND ((IIf(IsNull([Forms]![MailingsF]![ConstSearch]),[Constituency] Like "*",[Constituency]=[forms]![MailingsF]![ConstSearch]))<>False)
AND ((IIf(IsNull([Forms]![MailingsF]![CombAuthSearch]),[CombinedAuthority] Like "*",[CombinedAuthority]=[forms]![MailingsF]![CombAuthSearch]))<>False));

 

[plog]

Your syntax is all wonky. You don't INNER JOIN the same data source that's in the FROM. Here's how a FROM and INNER JOIN should work:

...FROM FromTable INNER JOIN JoinTable ON FromTable.Field = JoinTable.Field...

If you look at the SQL of your code throwing the syntax error you will see that you just keep repeating OrganizationsDetailT after every declaration of 'INNER JOIN'.

That's your syntax error. Of course that will probably just lead you to a logical error. My guess is those should be LEFT JOINs not INNER. But make those fixes I identified first and see if it does fix it

 

[AlliCarr]

Your syntax is all wonky. You don't INNER JOIN the same data source that's in the FROM. Here's how a FROM and INNER JOIN should work:

...FROM FromTable INNER JOIN JoinTable ON FromTable.Field = JoinTable.Field...

If you look at the SQL of your code throwing the syntax error you will see that you just keep repeating OrganizationsDetailT after every declaration of 'INNER JOIN'.

That's your syntax error. Of course that will probably just lead you to a logical error. My guess is those should be LEFT JOINs not INNER. But make those fixes I identified first and see if it does fix it

Thanks for your help. I did get it to save making the changes but then I had other criteria to add in and I couldn't get it to work. However, I've managed to figure out how to get the results I need using design view in Access.

 

[Pat Hartman]

Design view is extremely helpful when you are unfamiliar with SQL syntax.

 

[MajP]

@AlliCarr
When posting SQl or when troubleshooting there are a lot of free SQL formatters on line. Unfortunately Access removes all formatting, which makes it challenging. This becomes much easier to read.

SELECT organisationdetailst.organisationname,
       contactst.firstname,
       contactst.lastname,
       contactst.email,
       contactst.maincontact,
       regiont.region,
       localauthorityt.localauthority,
       [firstname] & "" & [lastname] AS Expr1,
       constituencyt.constituency,
       combinedauthorityt.combinedauthority,
       organisationdetailst.nonmember
FROM   ((constituencyt
         INNER JOIN (regiont
                     INNER JOIN localauthorityt
                             ON regiont.regionid = localauthorityt.regionid)
                 ON constituencyt.regionid = regiont.regionid)
        INNER JOIN (organisationdetailst
                    INNER JOIN combinedauthorityt
                            ON organisationdetailst.combinedauthorityid =
                               combinedauthorityt.combinedauthorityid)
                ON ( regiont.regionid = organisationdetailst.regionid )
                   AND ( localauthorityt.localauthorityid =
                         organisationdetailst.localauthorityid )
                   AND ( constituencyt.constituencyid =
                         organisationdetailst.constituencyid )
                   AND ( localauthorityt.combinedauthorityid =
                         combinedauthorityt.combinedauthorityid ))
       INNER JOIN contactst
               ON organisationdetailst.organisationurn =
                  contactst.organisationurn
WHERE  ( ( ( contactst.maincontact ) = true )
         AND ( ( organisationdetailst.nonmember ) = false )
         AND ( ( Iif(Isnull([forms] ! [mailingsf] ! [regionsearch]), [region]
                 LIKE "*",
                       [region]
                         = [forms] ! [mailingsf] ! [regionsearch]) ) <> false )
         AND ( ( Iif(Isnull([forms] ! [mailingsf] ! [lasearch]),
                 [localauthority] LIKE "*",
                         [localauthority] = [forms] ! [mailingsf] ! [lasearch])
               ) <>
               false )
         AND ( ( Iif(Isnull([forms] ! [mailingsf] ! [constsearch]),
                 [constituency] LIKE "*",
                         [constituency] = [forms] ! [mailingsf] ! [constsearch])
               ) <>
               false )
         AND ( ( Iif(Isnull([forms] ! [mailingsf] ! [combauthsearch]),
                         [combinedauthority] LIKE "*", [combinedauthority] =
                     [forms] ! [mailingsf] ! [combauthsearch]
                       ) ) <> false ) );