Comparing two tables across months

[zeeforel]

Dear all,

As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.

I would like to compare two similar tables for different months. I am looking for:
+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both

How do I solve this in the easiest way?

Many thanks in advance.

Marcel

 

[pr2-eugin]

Questions to Ponder over:

1. Why is the same Data present in Multiple tables?
2. What is the Relationship between t and t-1
3. What are you going to loose by giving some proper Table names instead of t and t-1.
4. Have you considered JOIN on the loanID?

 

[zeeforel]

Dear pr2-eugin,

1. These are just monthly snapshots I need to analyse. There is nothing "double" in the DB.
2. t and t-1 mean like September and August.
3. Names are just for illustration purposes
4. ??? I really am a newbie on trying to solve this, although I have a small clue about joins.

Any solution or tutorial you could point to? Thanks!

 

[pr2-eugin]

Still not clear TBH.

Could you upload a Stripped DB. Please follow the instructions religiously.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[zeeforel]

Please find attached a fictious database to simulate the real one.

The file contains two tables for the months July and August, each having 4 fields:
+ ID
+ Loan ID
+ LoanPart ID
+ Balance

Beware: the loan id's sometime occur multiple times because actually a loan could consist of more than one parts.

What I am looking for the create is a report stating:
+ count of loan id's / sum of balance that are new in table(aug), i.e. not exist in table(july)
+ count of loan id's / sum of balance loan id's that exited: that is exist in table(july) and not in table(aug)
+ count of loan id's / sum of balance loan id's that stayed in both


Many thanks in advance.