checking if month is missing

[Bob]

Hi,
I receive information about monthly payments made in to several accounts.
I need to run a query to check that information to see if any accounts
did not receive the payment for that month.
A very simplified example.
Account 1
January, February, March, April, May, June
Account 2
January, February, April, May, June

Clearly, Account 2 has missed out on the payment for March
All necessary information is stored in 2 tables and is imported monthly
from a .csv file.

Any help greatly appreciated.

Bob Wickham

 

[Duane Hookom]

Do you have table and field names you could share? Is there an actual date
field somewhere? How about a year? Can you show us some sample records?

 

[Bob]

Do you have table and field names you could share? Is there an actual date
field somewhere? How about a year? Can you show us some sample records?

Sorry, I tried not to bore you with the detail.
The information I have concerns commission payments made by banks to a
company that has brokered a loan from that bank.
I have 6 tables, tblClient, tblAdvisor, tblLender, tblCommissionType,
tblLoan and tblCommission.

tblLoan has these fields

LoanID


LoanNumber
LoanAmount
LoanStartDate
Period
PaymentFrequency
ClientID (looks up tblClient)(detail not shown below)
AdvisorID (looks up tblAdvisor)(detail not shown below)
LenderID (looks up tblLender)(detail not shown below)


1 1 Commercial 1/01/2005 60 Monthly
2 2 Equipment 25/01/2005 120 Monthly
3 3 Home Loan 3/02/2005 180 Monthly
4 4 Insurance 14/03/2005 84 Monthly

tblCommission has these fields

CommissionID
CommissionType
LoanNumber (looks up tblLoan)
PaymentDate
Payment
ClientID (looks up tblClient)(detail not shown below)
AdvisorID (looks up tblAdvisor)(detail not shown below)
LenderID (looks up tblLender)(detail not shown below)


68 Trail 1 22/09/2005 $4,654.04
69 Trail 2 22/09/2005 $43.40
71 Trail 4 22/09/2005 $9.19

Each loan should have received a commission payment for September
(Australian date format) but LoanNumber 3 did not.
I need to run a query to check that.

Hope this helps.

 

[Duane Hookom]

Consider the orders table in Northwind. Assume you want to find the months
in 1997 where EmployeeID 5 did not have any orders. I created a table with a
date for each month in the field "TheDate".

SELECT tblMonths.TheDate
FROM tblMonths
WHERE Format([TheDate],"yyyymm")
Not In (
SELECT Format([OrderDate],"yyyymm")
FROM ORDERS
WHERE EmployeeID =5);

 

[Bob]

Consider the orders table in Northwind. Assume you want to find the months
in 1997 where EmployeeID 5 did not have any orders. I created a table with a
date for each month in the field "TheDate".

SELECT tblMonths.TheDate
FROM tblMonths
WHERE Format([TheDate],"yyyymm")
Not In (
SELECT Format([OrderDate],"yyyymm")
FROM ORDERS
WHERE EmployeeID =5);

Thankyou Duane
Thats started me down the right track and I can adapt it to my own
situation.
I'll have to figure out how to loop through so the user doesn't have to
select just one ID but I guess thats a question for another forum.

Bob.