Comparing 2 tables of data

[Rogue Programme]

Hi guys, i'll try and explain this as best i can....

I have 2 tables:

CurrentMonth
PreviousMonth

Both tables hold financial information by policy number. What i am doing is working out financial movement on a month on month basis. For example, if in previous month the financial position was £100 but in current month the finances had moved to £150 then the movement is £50 for the month. This i can do no problem by linking the tables by policy number and extracting the financial position and subtracting one from the other to give me a movement. However, in some instances there will be some policies that appeared in PreviousMonth but have since been cancelled back to policy inception so is not appearing in CurrentMonth.

Problem: I need to be able to bring in the policies on the previous month that are not appearing on the current month (due to cancellation).

Can anyone help me?

Cheers

 

[macca the hacke]

Write a query that shows all records in previous month that are not in current month, joining by policy number.
You can then write a union query that will join this new query to your existing query.