Query - How to use If Not Exist (Maybe)

Question

Hi all

I am trying to query data relating to drawings and have just posted my first forum pic.

Reading the diagram from left to right....
Tables Z01, Z02 and Z05 relate to drawing information input for the search.
Table Z07a lists all items required to construct the subject drawing.
Some of these items are drawings themselves, hence the second incidence of Z01, Z02 and Z05.
This structure works fine until either the subject drawing or the returned drawings have not yet been revised and do not appear in Z05.

So my question is...
Can I add criteria to the search (preferably within the Access design window) to ignore each incidence of Z05 whenever there is no corresponding record in it for the searched or output drawing number?

Thanks

Phil

Answer 1

Hmm, not sure I follow, but I'm thinking using a criteria might be too late. Have you tried using an OUTER JOIN instead?

Answer 2

Thanks DBGuy

I have tried altering the joins to both revision tables.
The query now returns all related drawings whether they have entries in table Z05 or not, which is great.
The problem still exists with the "input" end of things. The user is entering criteria for a field in a table where that record may not even exist.

All I can think of to resolve the problem is to create 2 queries and union them together, one which considers the revision input by the user and one which ignores it.
Some of the problems I am having are a result of poor database construction - I am trying to recover data from an old FoxPro database and output that to Excel. I am not trying to build an operating database at this stage, so the means (however dirty) are not too important.

I have a blank query column question relating to this, but will post in group to avoid monopolising too much of your time.

Phil

Answer 3

Hi Phil. Glad to hear you're making good progress. I saw your other post and responded to it. Cheers!

Answer 4

>The problem still exists with the "input" end of things. The user is entering criteria for a field in a table where that record may not even exist.<<

The solution is to OUTER JOIN the table to a subquery. The following is an example where contacts who are not employed will also be returned:

SELECT FirstName, LastName, EmployerCount
FROM Contacts LEFT JOIN
(SELECT ContactID, COUNT() AS EmployerCount
FROM ContactEmployers
GROUP BY ContactID
HAVING COUNT() > 1) AS CE2
ON Contacts.ContactID = CE2.ContactID
ORDER BY LastName, FirstName;

This is taken from DatabaseBasics.zip which you'll find in
my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169