one to many records

[Pells]

I have 2 tables.

1 table with a list of employee details including called EMPDET.
1 table with a list of transaction details called EMPTRANS.

The primary key for both tables is the EMPID.

EMPTRANS has many records relating to a week number, which has many week numbers in it.



What I am trying to create is an exception query so that I can find where an employee hasn't got an entry against a specific week nr. I can do it the other way around and find where an employee has an entry in the table for a specific week nr, but really need to reverse it which I am having great difficulty with!

I have tried the unmatched query wizard which is fine, but this finds unmatched records for the whole table and not just for unmatched records for a specific WEEKNR.

Many thanks for taking the time to read my post, and any assistance would be greatly received.

[RuralGuy]

The EMPTRANS table should have its own PrimaryKey and have the EMPID field from the EMPDET table as a ForeignKey.

[Pells]

Sorry, I should have explained better in my earlier post. EMPTRANS has its own primarykey and EMPID field from the EMPDET table is foreign.

Do you have any other suggestions?

[RuralGuy]

So you want to find "missing" records in the EMPTRANS table? Have you tried using the "Find Unmatched" query wizard?

[Pells]

Yes, I have tried this and detailed this in my original post. This seems to work ok for finding records in the whole table, but I need to find unmatched records against a specific WEEKNR in the EMPTRANS table.

Hope this makes sense and is possible?

[RuralGuy]

You're absolutely right. You did say that in the first post, sorry. All you need to do is go into the design mode of that query and put in a Criteria for the WEEKNR. Is that what you were looking for?

[Pells]

When I do that, I get records for that particular WEEKNR, but Im looking for an exception query where there are no records for that week number - its like I want a reverse of this query.

Hope that makes sense?

[RuralGuy]

Boy, I'm really dense eh? Are you modifying the original "Find Unmatched" query? You should be only getting records that have Null values in the 2nd table.

[Pells]

Yes I am and get returned results for where records are unmatched for the whole table, but what I am looking to get it only records for a specific week nr. So if my criteria is WEEKNR = 41 and there are no entries in the EMPTRANS for WEEKNR 41, I should get a list of EMPID from EMPDET but I dont, I get blanks which is what you would expect as there are no details in EMPDET, but what I need is a list of EMPID's.

[RuralGuy]

How about posting the SQL View for the query you are using?

[Pells]

No problem:

SELECT EMPDET.PERSONNEL_NO, EMPDET.SURNAME, EMPDET.CENTRE, EMPTRANS.WEEK_NO
FROM (EMPDET.PERSONNEL_NO = EMPTRANS.PERSONNEL_NO) AND (EMPDET.COMPANY = EMPTRANS.COMPANY)
GROUP BY EMPDET.PERSONNEL_NO, EMPDET.SURNAME, EMPDET.CENTRE, EMPTRANS.WEEK_NO, EMPDET.COMPANY, EMPDET.DATE_LEFT, EMPDET.USE_IN_TB, EMPTRANS.ORIG_ADJUST
HAVING (((EMPDET.CENTRE)="144") AND ((EMPTRANS.WEEK_NO)="201041") AND ((EMPDET.COMPANY)="PFC1") AND ((EMPDET.DATE_LEFT) Is Null) AND ((EMPDET.USE_IN_TB)=1) AND ((EMPTRANS.ORIG_ADJUST)="Original"))
ORDER BY EMPDET.PERSONNEL_NO;

Many thanks for all you help, much appreciated!

[RuralGuy]

I don't see the join for the two tables. Am I missing something?

[Pells]

Apologies:

FROM EMPDET INNER JOIN EMPTRANS ON (EMPDET.PERSONNEL_NO = EMPTRANS.PERSONNEL_NO) AND (EMPDET.COMPANY = EMPTRANS.COMPANY)

Thanks.

[RuralGuy]

What did you do before, type the SQL instead of Copy and Paste or just open the wrong query?

[Pells]

Unsure what I did! You have all the code now, thankfully.