Query tables using date criteria

Posted on 2012-08-27
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
    LVL 39

    Expert Comment

    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

    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 39

    Accepted Solution

    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

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now