Creating the "inverse" of a table

[RobF]

Sorry - couldn't think of a succinct title for this question, but hopefully I can explain the challenge simply enough.

Imagine a very simple database with three tables. The first lists observers, each with an autokey ID number. The second lists potentially observable objects, again with an autokey ID number. The third, linked to the first two, lists observations with rows comprising observer ID, object ID and observation date.

What I want to do now is create a query that finds all the items NOT seen by each observer, i.e. multiple rows for observer 1 listed against objects x, y, z that he has not seen (can also think of it as a row for every possible observer/object combination, EXCEPT combinations appearing in the original Observations table)

Can anyone advise?

Thanks in anticipation

Rob

[ConneXionLost]

Hi Rob,

What you need to do is create a query with an "Outer Join" between your observation table and your objects table.

When the result indicates a null value in the observation table, you will have the objects you seek.

Cheers,

[RobF]

Thanks, but that's not quite what I'm looking for. Maybe I should have explained that ultimately I want to produce reports for each observer of items they haven't seen. This means I need a table with multiple rows for each observer containing

observer 1, object not seen a
observer 1, object not seen b
observer 1, object not seen p
...
observer 2, object not seen b
observer 2, object not seen c
observer 2, object not seen q
...

etc

does that make sense?

Rob

[ConneXionLost]

Start with what Observer1 has seen:

qselObserver1Seen

SELECT tblObserver.ObserverID, tblObservation.ObjectID
FROM tblObserver INNER JOIN tblObservation ON tblObserver.ObserverID = tblObservation.ObserverID
WHERE ((tblObserver.ObserverID)=1);

Then use an outer join to list what he hasn't seen:

SELECT tblObject.ObjectID
FROM tblObject LEFT JOIN qselObserver1Seen ON tblObject.ObjectID = qselObserver1Seen.ObjectID
WHERE ((qselObserver1Seen.ObjectID) Is Null);

Cheers,

[RobF]

Thanks - this works for me though obviously is slightly less that what I'd hoped for in that you have to manually change the query to search for one observer at a time. I'd hoped to get the non-observed objects for all observers assembled in a single query? Is this not possible?

TIA

Rob

[TheShabz]

You dont have to do it manually. The SQL can be put into VBA replacing the "1" in the WHERE clause with a reference to an object on a form.

[RobF]

It's pretty obvious I think that I'm an Access novice - is it acceptable to ask for some help with the VBA code? Thanks very much indeed in advance...

[TheShabz]

Scratch that, dont use VBA. Create a form (called myForm in this example) and a combobox holding the observerIDs (called cmbObsID in this example) Just replace the "1" from the WHERE clause with "Forms!myForm!cmbObsID". then run the second query as stated above. That's probably the easiest way to do it and you dont even need VBA for it.