Query Table for records with no 'sub records'

[LukeMills2010]

Hi,

Access 2003

I have two different tables which contain assets with the same primary key ('Serial Number') and these fields are linked via a one-to-one relationship (No Ref Integrity).

I am trying to figure out if their is a way to query one of these tables for records that don't have a sub record i.e. records that don't have a linked relationship to a record in the other table. How is this done?

Any help is much appreciated, thanks.

 

[CJ_London]

use a left join and a criteria

select *
from table1 left join table2 on table1.id=table2.id
where table2.id is null

will display all records in table1 where there are no corresponding records in table2

I really don't want to ask why you have set things up this way, you ought to read up on normalisation

 

[vbaInet]

Welcome to AWF!

If they both hold similar data then perhaps you want to rethink your one-to-one relationship and consider merging both tables.

1. Create a UNION query that merges both tables. Save and close.
2. Create a new query and include the union query from (1) and the child table linked via Serial Number
3. Click the Totals button and count the records per Serial Number in the child table - only include two fields, the Serial Number and the Count(). The count will be redundant, this is just to condense the data.
4. Now use a DLookup() to see if it returns any data, if it does, a child record exists. If it doesn't a child record doesn't exist.

 

[LukeMills2010]

use a left join and a criteria

select *
from table1 left join table2 on table1.id=table2.id
where table2.id is null

will display all records in table1 where there are no corresponding records in table2

I really don't want to ask why you have set things up this way, you ought to read up on normalisation

The reason I have it setup like this is because asset data is imported from two sources. The secondary source is considered to contain assets that are not registered in the primary asset source and I wan't to distinquish these. Is that a valid reason not to follow the rules of normalisation.

 

[CJ_London]

Is that a valid reason not to follow the rules of normalisation.

Not really - I'm not aware of any situation that says 'it is OK to not follow normalisation rules'.

I would have the one table but have an extra field, perhaps called 'status' which indicates whether the record is 'primary','secondary'.