Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing records for last two months (2003)

    To keep this as simple as possible, I have a table containing client details (Name, address etc) and a related table containing sales information for each month (date and amount) - joined on a common field (which is a unique ID in the Client Details table). Consequently, for each client, I have many related records, one for each month, going back quite a while. What I have been asked to do is to produce a query/report comparing the last two months sales figures and indicating the difference between those two numbers (whether the current month's sales are up or down when compared to the previous month's and by how much). How on earth can I do this? It's driving me crazy. I've spent ages trying to get this to work and have tried everything I can think of. If anyone out there can come up with any ideas, I would be very grateful - and I won't have to worry about it at the weekend :-) Thank you to anyone who can help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing records for last two months (2003)

    This is possible using a series of queries, but it would be helpful to have some sample data. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing records for last two months (2003)

    OK, here you go. This is a much simplified data set but should give you the general idea. What I need is to compare the latest sales data with the previous month's data and come up with a variance between the two. Does that make sense?
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing records for last two months (2003)

    In the attached version I have created two queries.

    qryMonths is a totals query that simply calculates the most recent month. It also calculates the previous month by subtracting 1 month from the most recent one.

    qryCompare is also a totals query. It is based on qryMonths and two copies of tblPerformance. One copy is joined to the query on the current month, the other on the previous month.
    The query groups by the client name field, sums the SalesAmount fields and calculates the difference.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing records for last two months (2003)

    Hans, you are a genius! Thank you so much. The annoying thing is that I was so nearly there. What I had done wrong was the expression in qryMonths. I can now relax over the weekend instead of worrying about it. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •