Identifying Missing Records 1

[hijinx]

I have seen some threads similar to this, but can't get any of them to work.

I work for a financial company and all of our clients are supposed to make monthly deposits. At times, however, the client chooses to skip a deposit. I need to determine a list of clients and the months for which they made no deposits.

I use a field called YYYYMM (eg, 200509) to identify the month of each deposit. I also created a "master lookup table" of months and years (in the same YYYYMM format) to compare it to.

I have tried the Access unmatched wizard, but that doesn't give me the client name and the missing month(s), since the result fields are drawn from the "master" table.

Any help is appreciated.

 

[PHV]

Say you have 3 tables: tblClient, tblDeposit and tblMonth:
SELECT B.ClientID, B.YYYYMM
FROM tblDeposit AS A RIGHT JOIN (
SELECT ClientID, YYYYMM FROM tblClient, tblMonth
) AS B ON A.ClientID=B.ClientID AND A.YYYYMM=B.YYYYMM
WHERE A.ClientID Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

To better understand why/how this works I use a simple example to teach people.
1)Make tblPersonnel which has People's names in it.
2)Make tblChildren which has the Children names in it and relate the tables by a Person ID.
3)Put some names in tblPersonnel.
4)Put some children names in the tblChildren.
5)Relate the children to the parents. Have some personnel without children and some orphans (children without a proper Person ID).
6)Make a query joining the two tables.
Acess will automatically join the tables so that it shows only those records where the join is equal. You will only see a record for each child and their parent. You will not see Persons without children or orphans. Look at the SQL statement
7)Click on the arrow joining the tables in the query designer and it should allow you to change the join type
8) Change the join to Show all records from tblPersonnel and those from tblChildren that are equal
You will now see all the Persons and their children. Persons without children will appear, but orphans won't
9) Change the join to show all tblChildren and only tblPersonnel that are equal.
Now you can see all the children including the orphans, but you will not see Person's without children.
10)If you set your criteria to look for Person Name is null then you will get only your orphans.
11) If you do this look at the SQL each time. You will then understand right, left, and inner joins.

I know your question was specific, but understanding this concept comes in handy often.

 

[PHV]

MajP, the key here to solve the problem is a cartesian product.

 

[hijinx]

Thanks for the guidance. Worked like a champ!! Gold Star for you!