Query not returning all expected results

[amenitytrust]

Hi folks,



hoping someone might be able to point me in the right direction. I'm no expert (as you'll soon be able to tell) and welcome any advice.

I've created a database to log one of my companies annual events. In this event volunteer groups go out and clean up a roadside or beach. Each group registers and then submits a survey.

I have 3 tables:



I use the following query to pull information from all three tables:

SELECT tbl_groups.sal, tbl_groups.firstName, tbl_groups.surname, tbl_groups.address1, tbl_groups.address2, tbl_groups.address3, tbl_groups.address4, tbl_groups.address5, tbl_surveys.areaCleaned, tbl_surveys.commCounArea, tbl_surveys.bags, tbl_surveys.funding, tbl_surveys.confirmed, tbl_registrations.regID, tbl_surveys.surveyID
FROM (tbl_surveys INNER JOIN tbl_groups ON tbl_surveys.[groupID] = tbl_groups.[groupID]) INNER JOIN tbl_registrations ON (tbl_groups.[groupID] = tbl_registrations.[groupID]) AND (tbl_surveys.[regID] = tbl_registrations.[regID])
WHERE (((Year([tbl_registrations.plannedDate]))=Year(Date())));

Since this is the first year that the database is in operation the number of records generated by this query should equal the number of records in tbl_surveys. However, tbl_surveys has 139 records at present and the query returns only 130. I can see which 9 records have been omitted (by using the find unmatched query wizard), but cannot see a reason why.

I thought that I maybe needed to use a LEFT JOIN in the above query, but anytime I do (in either JOIN expression or both) I am told "JOIN expression not supported".

Any help would be greatly appreciated.


Regards,

John

[JoeM]

Try making all your joins Left Joins from the "tbl_surveys" table to the other two tables.

Note: Without seeing or understanding the nature of your data, you may also need (or want) to include "group_id" in your relationship between the "tbl_surveys" and "tbl_registrations" tables.

[amenitytrust]

Hi JoeM,

thank you for your response.

As I said in my original post, if I make both joins LEFT JOIN then I receive an error message saying "JOIN expression not supported".

As per your other suggestion, I've updated the statement to include groupID in the relationship between tbl_surveys and tbl_registrations:

SELECT tbl_groups.sal, tbl_groups.firstName, tbl_groups.surname, tbl_groups.address1, tbl_groups.address2, tbl_groups.address3, tbl_groups.address4, tbl_groups.address5, tbl_surveys.areaCleaned, tbl_surveys.commCounArea, tbl_surveys.bags, tbl_surveys.funding, tbl_surveys.confirmed, tbl_registrations.regID, tbl_surveys.surveyID
FROM ((tbl_surveys INNER JOIN tbl_groups ON tbl_surveys.[groupID] = tbl_groups.[groupID]) INNER JOIN tbl_registrations ON (tbl_groups.[groupID] = tbl_registrations.[groupID]) AND (tbl_surveys.[groupID] = tbl_registrations.[groupID]) AND (tbl_surveys.[regID] = tbl_registrations.[regID]))
WHERE (((Year([tbl_registrations.plannedDate]))=Year(Date())));

This gives the same number of records, but I agree that it probably helps reinforce the integrity of the query.

However, this "JOIN expression not supported" seems to be my undoing. I can't understand why it will not allow me to use LEFT JOIN.

### I'll try to explain the database a little more ###

The data is being collected for two reasons:

1) - To collect statistics on how much rubbish/garbage has been collected in which areas. What kind of rubbish it is, how many man hours have been accumulated etc.
2) - To help facilitate mail merge operations so that registration packs, welcome/thank you letters can be sent out with ease.

Any one group may participate in the event for x number of years. For each year every participating group should have 1 registration and 1 survey (hence the WHERE clause in SQL - this query returns all of this years participants).

Any further info I can post, please let me know.

[JoeM]

I think you may have an extra join in there that you don't need (between tbl_registrations and tbl_groups).

If you need a LEFT JOIN, I think it should look like this:

FROM (tbl_surveys
LEFT JOIN tbl_groups
ON tbl_surveys.[groupID] = tbl_groups.[groupID])
LEFT JOIN tbl_registrations
ON (tbl_surveys.[regID] = tbl_registrations.[regID]) AND (tbl_surveys.[groupID] = tbl_registrations.[groupID])

Regarding your record count, also note that your criteria may be dropping some records (based on plannedDate). If you want to ensure that your LEFT JOIN is done right, try it first without the criteria and see what your record count is.

[amenitytrust]

Thank you very much for your input, JoeM! I shall give that a try and get back to you.

[amenitytrust]

Hi again JoeM,

your last post got me thinking and ultimately has lead me to my answer. I had made the mistake of relying upon that PlannedDate field, but not making it a required field, so Null values had been my downfall.

Sorry for my sillyness and thank you again for your help.


Regards,

John

[JoeM]

Glad to hear that you figured it out!