Query tables using date criteria

I have googled this already but couldn't find an answer that exactly fitted this so...
I want to create a query on my Access database which will tell me at a glance how my turnover matched up to the corresponding date last year, and the year before that, and so on.

I have 7 tables so far, which contain the data I am trying to look at. So let's say that the tables are 2006,2007,2008, etc and each of the tables has a field named InvoiceAmount and a field named InvoiceDate.

Obviously I can easily see a running total of my invoice amounts for this year, but I want to be able to compare this figure with the same date last year, and the year before, and the year before that.....
I don't know how to configure the criteria in the query to tell me the figure on the same date in a different year, and since this will change every day it needs to be a variable query.
I look forward to receiving your coments on this
I forgot to mention this is Access 2010, my knowledge is limited, not a beginner but by no means an expert!
Who is Participating?
als315Connect With a Mentor Commented:
Test this sample. In reality you may need some additional query if you like to see all dates, not only in main query (q2008 in example). Open query1.
There are different methods, but usually in first query (queries if you have many tables) you should extract date, month and year into different fields. In next query you can join date and month without year.
Tintoman51Author Commented:
So ok I have now worked out how to make the data appear by using the DateAdd function.
But is it possible to show the date from 7 different tables in the one query? when I try this I get results which are obviously not correct, for example the sum of my turnover might be returned as £50,000,000.00 when in fact it wouldn't be more than £125,000
Any ideas?
Tintoman51Author Commented:
I'm obliged to you for the sample, although it didn't really answer my question, it did at least make me realise that I would have to query each of the tables individually, and then merge the queries into another query.
Thanks anyway
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.