how to determine which record is missing...

[ThriftyFinanceGirl]

I need to

1. test to see if all records have a child record for the matching month/year
2. if there are records that don't exist, create the ones that don't exist.

Parent Table = LocationExceptions
Child table = ExceptionData (contains an amount, month, year for each
exception)

Issue is that if there is a NEW exception, I can run a query and easily find
that there is a "null" exception ID, and create the record. However, I can't
figure out how to determine if there is and exception ID that has a missing
MONTH/YEAR record.

Example: new exception created in May, exception data has already been keyed
for May, need to create a new ExceptionData record for May/2009. This I
accomplished by finding the Null Exception Id in a Left Join.

But, tried to do the APRIL set for the same thing and it won't work because
now there is no "null" value for that exception (ie. there is now a record
for that exception although it is for MAY).

 

[Graham Mandeno]

Hi ThriftyFinanceGirl

I'm not entirely certain I understand your problem, but if I'm right then
you can use a subquery. For example:

SELECT * from LocalExceptions where ExceptionID not in
(Select ExceptionID from ExceptionData
where MonthField=4 and yearField=2009)

 

[ThriftyFinanceGirl]

Thank you! perfect... I just couldn't figure it out in my blonde head
yesterday!

Hi ThriftyFinanceGirl

I'm not entirely certain I understand your problem, but if I'm right then
you can use a subquery. For example:

SELECT * from LocalExceptions where ExceptionID not in
(Select ExceptionID from ExceptionData
where MonthField=4 and yearField=2009)

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I need to

1. test to see if all records have a child record for the matching
month/year
2. if there are records that don't exist, create the ones that don't
exist.

Parent Table = LocationExceptions
Child table = ExceptionData (contains an amount, month, year for each
exception)

Issue is that if there is a NEW exception, I can run a query and easily
find
that there is a "null" exception ID, and create the record. However, I
can't
figure out how to determine if there is and exception ID that has a
missing
MONTH/YEAR record.

Example: new exception created in May, exception data has already been
keyed
for May, need to create a new ExceptionData record for May/2009. This I
accomplished by finding the Null Exception Id in a Left Join.

But, tried to do the APRIL set for the same thing and it won't work
because
now there is no "null" value for that exception (ie. there is now a record
for that exception although it is for MAY).