Query tables using date criteria

Posted on 2012-08-27
Medium Priority
Last Modified: 2012-08-28
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!
Question by:Tintoman51
  • 2
  • 2
LVL 40

Expert Comment

ID: 38337636
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.

Author Comment

ID: 38337637
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?
LVL 40

Accepted Solution

als315 earned 1500 total points
ID: 38337870
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.

Author Closing Comment

ID: 38341095
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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question