Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    xmattxman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    19

    Enter a date range and compare it to the same date range of the previous year


    I am writing a revenue report. Basically I need to have the user enter a date range lets say 01/01/18 - 04/01/18. I need to pull up what we sold in that range as well as what we sold in that range a year prior so 01/01/17 - 04/01/17. That needs to work with what ever date range is entered. I am not sure how to go about doing this. Anybody have any suggestions?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i have a form with 4 text boxes
    txtSTartDate, txtEndDate
    txtStartPrev, txtEndPrev

    user picks the 1st 2 dates
    the source for txtStartPrev = DateAdd("yyyy",-1,txtStartDate)
    txtEndPrev = DateAdd("yyyy",-1,txtEndDate)

    Q1 pulls data from txtStart date range
    Q2 pulls data from the txtStartPrev date range
    Q3 combines the 2 in a UNION query.

  3. #3
    xmattxman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    19
    Didn't even think about using a union query! Thanks I will give it a shot!

  4. #4
    xmattxman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    19
    SO I have the union query but I need to make a report that looks like this

    Code:
    Customer     Current Year sold     Previous Year sold   Current year units     Previous year units

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    If you want the 2 years of data side-by-side, do a JOIN on some common value such as ProductID or CustomerID. Join both queries to Customers table.

    Explore use of nested subqueries, start with http://allenbrowne.com/subquery-01.html#YTD
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in Q1, add blank fields for the PrevYrs
    in Q2 add blank fields for CurrentYr
    the union will add them

  7. #7
    xmattxman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    19
    I tried the blanks and the union but then I get this which is close to what I want but anyway to get it on the same line and not have it display as the customer twice on separate lines?
    Code:
    CU_NAME CYT CYU PYT PYU
    Company1 7719.55 3371
    Company1 11395.08 391

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Did you try suggestion in post 5?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just to confirm June's point.
    For what you want, a SELECT query joining both sets of data is needed to get a single record.
    By definition a UNION query will give two separate records for this year and last year
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by xmattxman View Post
    I tried the blanks and the union but then I get this which is close to what I want but anyway to get it on the same line and not have it display as the customer twice on separate lines?
    You need an aggregate query which sums units and quantities from union query grouped by units.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I say a UNION is not needed in this situation. Build two aggregate queries and join those to Customers table. OP indicates summation by customer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes I still agree - no UNION query is needed - just link the 2 queries by CU_NAME field
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    xmattxman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2017
    Posts
    19
    If I create two queries one that pulls the previous years dates and one that pulls the current years dates. What if we had a new customer this year or what if we didn't sell anything last year to a certain customer but we sold it to them this year? I don't believe it would show up because it would be linked as show all records from the first query and only records that equal from the second.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by xmattxman View Post
    If I create two queries one that pulls the previous years dates and one that pulls the current years dates. What if we had a new customer this year or what if we didn't sell anything last year to a certain customer but we sold it to them this year? I don't believe it would show up because it would be linked as show all records from the first query and only records that equal from the second.
    OK but you didn't mention that possibility before.
    In your final query join the original table to both your queries using outer joins so you get all customers
    You still don't need a UNION .... but if you want to do that instead try Arvil's suggestion from post #10 ... it might be slower though unless you have a lot of records the difference should be negligible
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You have a Customers table? As already suggested in several posts, join both queries to that table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  3. Replies: 1
    Last Post: 10-12-2015, 02:26 PM
  4. Replies: 14
    Last Post: 06-21-2013, 07:18 AM
  5. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums