Help with a query, please

[MatMac]

Hi. The following query (in blue) does not work properly. If anyone can help me correct it I would be very grateful.

In my database, a RESEARCH record can comprise any number of ADVISORS (linked by ResAdList table) and any number of CONTACTS (linked by ResCoList).

I have a form RESEARCHMANAGE with two combo boxes, SELECTADVISOR and SELECTCONTACT, these boxes return advisor and contact ID numbers respectively.

I want to retrieve research records where CONTACT ID and/or ADISOR ID are as selected by the combo boxes. (I.e. if one of selectcontact or selectadvisor are left null, then the query just returns rsearch records by advisor or by contact respectively.)

The WHERE clause in the query below works correctly.

The problem is that, due to the join properties between tables, research records are not returned by the query in cases where no associated advisors or contacts exist.

Below is a summary of what I require:

If both SELECTADVISOR and SELECTCONTACT are chosen...

- all RESEARCH records where both the ADVISOR and the CONTACT were present.

If SELECTADVISOR only is chosen...

- all RESEARCH records where the ADVISOR was present - for all contacts - even if no contacts at all are listed for the RESEARCH record (the latter part is what does not work).

If SELECTCONTACT only is chosen...

- all RESEARCH records where the CONTACT was present - for all advisors - even if no advisors at all are listed for the RESEARCH record (the latter part is what does not work).


SELECT Research.resID, Research.resSelect, Advisor.adID, Contact.coID
FROM (Research INNER JOIN (Advisor INNER JOIN ResAdList ON Advisor.adID = ResAdList.adID) ON Research.resID = ResAdList.resID) INNER JOIN (Contact INNER JOIN ResCoList ON Contact.coID = ResCoList.coID) ON Research.resID = ResCoList.resID
WHERE (((Advisor.adID) Like IIf(IsNull([Forms]![ResearchManage]![selectadvisor]),'*',[Forms]![ResearchManage]![selectadvisor])) AND ((Contact.coID) Like IIf(IsNull([Forms]![ResearchManage]![selectcontact]),'*',[Forms]![ResearchManage]![selectcontact])));

 

[jal]

I'm a beginner, and I doubt I've solved your problem, but maybe I can get you started. Some advice. Your query is hard to read because you are doing your joins "the hard way" (the way shown in the Access 2003 documentation). People will be able to help you more if you make your queries more readable by using the easier join syntax which is more like Sql Server 2000. I am so excited about having just learned the easier syntax that today I wrote an article on it even though beginners like me should not be writing articles. I will post the article shortly, but here's what the easy syntax looks like:

SELECT Research.resID, Research.resSelect, Advisor.adID, Contact.coID
FROM
(((ResAdList INNER JOIN Advisor ON Advisor.adID = ResAdList.adID)
INNER JOIN Research ON Research.resID = ResAdList.resID)
INNER JOIN ResCoList ON Research.resID = ResCoList.resID)
INNER JOIN Contact ON Contact.coID = ResCoList.coID
WHERE Advisor.adID Like
IIf
(
IsNull([Forms]![ResearchManage]![selectadvisor])
,'*'
,[Forms]![ResearchManage]![selectadvisor]
)
AND Contact.coID Like
IIf
(
IsNull([Forms]![ResearchManage]![selectcontact])
,'*'
,[Forms]![ResearchManage]![selectcontact]
);

Now you said that you are not getting all the Research records back in some situations. Typically this means that you need to convert one or more of your joins to a LEFT or RIGHT Join. I'm not bright enough to figure out where (it's hard when I can't see the tables), but maybe I can give you some clues. To preserve all the Research records using the above start, you might need to RIGHT join Research, that is, replace the above line:

INNER JOIN Research ON Research.resID = ResAdList.resID)

with this line

RIGHT JOIN Research ON Research.resID = ResAdList.resID)

However, a more likely possibility, I suppose, is that if you are, once again, suffering from an insufficient number of advisors, you might try left joining advisors by replacing this line:

(((ResAdList INNER JOIN Advisor ON Advisor.adID = ResAdList.adID)

with this line:

(((ResAdList LEFT JOIN Advisor ON Advisor.adID = ResAdList.adID)

(well, unfortunately I forgot that the first join in a series must be an INNER JOIN so you may have to flip flop a table or two here).
And try the same ideas where you are suffering from insufficient contacts. That is, try replacing this line

INNER JOIN Contact ON Contact.coID = ResCoList.coID

with this line

LEFT JOIN Contact ON Contact.coID = ResCoList.coID

And now I'll post that article on the easy syntax, please everyone share it with others. Good luck to you.

 

[jal]

Easier Join Syntax

The documentation for Access 2003 unfortunately advises us, unnecessarily, to use the following confusing syntax for joining multiple tables:

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 = tablex.fieldx)]
ON table2.field2 = table3.field3)
ON table1.field1 = table2.field2;

Note that after every instance of the INNER JOIN keyword comes a left parentheses, except for the last INNER JOIN. After I finally figured out how to use this difficult syntax, I then learned the easy way from examples found on the Net. Before we discuss the easy way, keep in mind that you can test the syntax used in the examples below even without creating any tables - just try to save them in the Access query editor because Access throws syntax errors at the moment of saving the query. Here's a join of two tables, and note that the Access syntax, for two tables, is precisely the same simple, easy-to-use syntax used in SQL Server 2000:

SELECT * FROM
t1
INNER JOIN t2 ON t1.id = t2.id

You simply have to understand how Access "thinks" regarding the "easy way." To begin with, no parentheses are needed if you are only joining two tables, as shown above. The problem is that Access ALWAYS "thinks" only in terms of two tables. It cannot, therefore, join three tables at once. To join the third table, it first joins t1 and t2 into one table, and then it joins that one table to t3. As a result, it is really only joining two tables. Oddly enough, you have to use parentheses to TELL Access to treat the t1-t2 join as one table (whereas Sql Server 2000 figures this out on its own). To do this, simply place the entire t1-t2 join-code within parentheses as to form the "first table," and then join t3, in the usual way, as the "second table," because Access always thinks in terms of only two tables. Note that t3 doesn't need any parentheses because parentheses are never needed for two tables (t3 is the second of two tables). Parentheses are needed only to form t1 and t2 into one table, and now that we really have only two tables (t1+t2 and t3), no more parentheses are needed, because no parentheses are needed when we only have two tables.

SELECT * FROM
(t1 INNER JOIN t2 on t1.ID = t2.ID) <-- t1+t2 is first table, the virtual "t1"
INNER JOIN t3 on t3.id = t2.ID <--- virtual "t1" joins to t3 as "second" table

Aside from the set of parentheses, the syntax above is exactly the same as in Sql Server 2000. And so it remains. No matter how many tables you add, the syntax remains the same easy-to-use syntax found in Sql Server 2000 except for the added parentheses. If you are joining 10 tables, tables 1 through 9 must be wrapped to form the "first" table, which is then joined to t10 as the "second" table, because Access always thinks in terms of two tables. But we are getting ahead of ourselves, because each time you add a table, you have to add another set of parentheses (you can't just use one set of parentheses to wrap tables 1 through 9). So let's add the fourth table (t4) to the example above. Again, t4 will be the "second" table and therefore does not need any parentheses, but we must add a set that wraps the entire t1 to t3 join code as to form the "first" table. To do this, simply add a left parenthesis to t1 and close it with a right parenthesis after the t3 join like this:

SELECT * FROM
((t1 INNER JOIN t2 on t1.ID = t2.ID)
INNER JOIN t3 on t3.id = t2.ID) <------ t1 thru t3 form virtual table "t1"
INNER JOIN t4 on t4.id = t3.ID <----- virtual "t1" joins to virtual "t2" (i.e. to t4).

Notice that t1 has two parentheses to the left. It should always have two less than the total number of tables, in this case there are 4 tables in all so it has 2 parentheses. If there were ten tables, it would have 8 parentheses to the left. Now let's add table 5 (t5). Simply place the entire t1 to t4 join code within parentheses. To do this, simply add another lefthand parenthesis to t1 and then close it with a righthand parenthesis after the t4 join. Table t5 doesn't need any parentheses because it is the "second" table:

SELECT * FROM
(((t1 INNER JOIN t2 on t1.ID = t2.ID)
INNER JOIN t3 on t3.id = t2.ID)
INNER JOIN t4 on t4.id = t3.id) <----- t1 thru t4 form virtual table "t1"
INNER JOIN t5 on t5.id = t4.id

Notice how simple this pattern is. If you look at the righthand side, you'll see that every line ends with a righthand parentheses except for the last line - this is very easy to remember. Then you just place the correct number of lefthand parentheses in front of t1 (the correct amount is two less than the total number of tables or, stated differently, 1 less than the number of INNER JOIN keywords). This too is very easy to remember. Following this pattern, we can now easily reach our original goal of ten tables:

SELECT * FROM
((((((((t1 INNER JOIN t2 on t1.ID = t2.ID)
INNER JOIN t3 on t3.id = t2.ID)
INNER JOIN t4 on t4.id = t3.id)
INNER JOIN t5 on t5.id = t4.id)
INNER JOIN t6 on t6.id = t5.id)
INNER JOIN t7 on t7.id = t6.id)
INNER JOIN t8 on t8.id = t7.id)
INNER JOIN t9 on t9.id = t8.id)
INNER JOIN t10 on t10.id = t9.id

You can now see that the Access syntax is very easy to read just like in Sql Server. Probably the only other major difference between Access and Sql Server 2000, regarding join syntax, is that with Access your ON clauses cannot include WHERE-clause criteria. You have to put these criteria within a WHERE clause, whereas Sql Server lets you put them directly into the ON-clauses. In Access, ON-clauses are strictly for specifying join criteria, that is to say, each ON clause must mention two tables and must relate them to each other:

ON t1.id = t2.ID

and then add a WHERE clause whenever you have to limit one of the tables:

WHERE t1.id > 5000

whereas sql Server 2000 let's you omit the WHERE clause like this:

ON t1.id = t2.ID AND t1.id > 5000

And there is also one minor difference: when using aliases, you have to use the "AS" keyword.

SELECT * FROM
CustomerTable AS c
INNER JOIN OrdersTable as t ON t.id = c.ID

Of course in many cases you can omit the INNNER JOIN keyword altogether and just use WHERE clauses. You simply have to name all your tables in the FROM clause. (All databases are designed to do a join whenever there is more than one table named in the FROM clause) Example:

SELECT *
FROM t1, t2, t3, t4
WHERE t1.id = t2.id AND t2.id = t3.id AND t4.id = t3.ID

But I like the JOIN keyword better because it comes in handy when I need to do a left join, or a right join, or a full join. It is hard to use WHERE clauses for doing left, right, and full joins.

 

[neileg]

You can't pass text variables into SQL like that. You'll have to build your query in VBA using concatenated text and then run the resulting SQL.

 

[MatMac]

Jal - thanks for the reply. When I get time!! I will have a good read through your article on the alternative syntax. Unfortunately, time constraints for me mean that "getting it working" has to be the priority.

Anyway, I actually worked out yesterday how to solve the problem. I used an If statement to run one of two queries based on whether one of my parameters was Null or not.

The code below is run when the SELECTADVISOR combo is changed, and gives the idea:

Note, these queries below are actually update queries, which set a YES/NO field to YES where records are returned by the query. I then display RESEARCH records where SELECT=YES. (This get round the problem of the query returning multiple copies of each record - an issue I raised in an earlier thread.)

'Sets select field in research record according to criteria selected in form.
If IsNull(selectadvisor) Then
'If no advisor selected, looks for contacts only.
DoCmd.RunSQL "UPDATE Research INNER JOIN (Contact INNER JOIN ResCoList ON Contact.coID = ResCoList.coID) ON Research.resID = ResCoList.resID SET Research.resSelect = -1 WHERE (((Contact.coID) Like IIf(IsNull([Forms]![ResearchManage]![selectcontact]),'*',[Forms]![ResearchManage]![selectcontact])))"
Else
'If both contact and advisor selected, looks for both advisors and contacts.
DoCmd.RunSQL "UPDATE Contact INNER JOIN ((Research INNER JOIN (Advisor INNER JOIN ResAdList ON Advisor.adID = ResAdList.adID) ON Research.resID = ResAdList.resID) INNER JOIN ResCoList ON Research.resID = ResCoList.resID) ON Contact.coID = ResCoList.coID SET Research.resSelect = -1 WHERE (((Advisor.adID) Like IIf(IsNull([Forms]![ResearchManage]![selectadvisor]),'*',[Forms]![ResearchManage]![selectadvisor])) AND ((Contact.coID) Like IIf(IsNull([Forms]![ResearchManage]![selectcontact]),'*',[Forms]![ResearchManage]![selectcontact])))"
End If