Results 1 to 8 of 8
  1. #1
    Rackrunner is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Posts
    4

    Cannot total fields when using Date Search

    Help...I am trying to run and query/report using a Date input for between 2 dates (Between #2023-05-01# And #2023-05-19#)and have the query total the appropriate field however the query will only return values for individual dates?How do I get the query to return a total for each product in a given date range?



    Here is the SQL view
    SELECT tblOrdersSub.Model, Sum(tblOrdersSub.Qty) AS SumOfQty, tblOrders.[Ship Date]
    FROM tblOrders INNER JOIN tblOrdersSub ON tblOrders.[Sales Number Id] = tblOrdersSub.[Sales Number]
    GROUP BY tblOrdersSub.Model, tblOrders.[Ship Date]
    HAVING (((tblOrders.[Ship Date]) Between #5/1/2023# And #5/19/2023#));


    Model SumOfQty Ship Date
    Stinger-XL 112 3 2023-05-10
    Stinger-XL 112 1 2023-05-11
    Stinger-XL 112 2 2023-05-15
    Stinger-XL 112 1 2023-05-16
    Stinger-XL 112 3 2023-05-17
    Stinger-XL 112 10 2023-05-19
    Stinger-Trike 2 2023-05-12
    Stinger-Trike 1 2023-05-15
    Stinger-Trike 5 2023-05-19
    Custom 1 2023-05-04
    Stinger-Strap Kit -Single 1 2023-05-04
    Stinger-Strap Kit -Single 1 2023-05-10
    Stinger-Strap Kit -Single 1 2023-05-11
    Stinger-Strap Kit -Single 1 2023-05-12
    Stinger-Strap Kit -Single 4 2023-05-15
    Stinger-Strap Kit -Single 2 2023-05-16
    Stinger-Strap Kit -Single 3 2023-05-17
    Stinger-Tires 12" & 13" Radial 1 2023-05-04
    Stinger-Tires 12" & 13" Radial 2 2023-05-15
    Stinger- Tongue Jack 2 2023-05-15
    Stinger-Can-Am Spyder RT/F3/Ryker 1 2023-05-15
    Stinger-Can-Am Spyder RT/F3/Ryker 1 2023-05-16
    Stinger-Can-Am Spyder RT/F3/Ryker 1 2023-05-17
    Lock Kit 1 2023-05-15
    Grip Tape 1 2023-05-10

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Don't include the date field in the Group By.

    Cheers
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You would create a Report based on your query and Group By Model

    The Report would look like this:-


    Attached Thumbnails Attached Thumbnails Total of Group.jpg  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi As Vlad has pointed out it can be achieved in a query

    SQL would be on these lines:-

    SELECT tblData.Model, Sum(tblData.SumOfQty) AS SumOfSumOfQty
    FROM tblData
    GROUP BY tblData.Model;
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    Rackrunner is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Posts
    4
    Quote Originally Posted by Gicu View Post
    Don't include the date field in the Group By.

    Cheers
    The only concern I have with that Vlad is I am trying to run Monthly/Weekly reports and need to input selected dates. Any suggestions on how I could achieve that?

    Thanks Paul

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Leave the Ship Date and the criteria in the query but change the Group By in the Totals row to Where.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Rackrunner is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Posts
    4
    Quote Originally Posted by Gicu View Post
    Leave the Ship Date and the criteria in the query but change the Group By in the Totals row to Where.

    Cheers,


    Wow!That is the exact solution I was looking for... Thanks so much

    Model SumOfQty
    Stinger-XL 112 20
    Stinger-Strap Kit -Single 13
    Tire Rim Upgrade- 2 Tires 11
    Stinger-Trike 8
    Stinger-Twin Cruiser 7
    Spare Tire 12" 7
    Spare Tire 13" 5
    Shipping 5
    Grip Tape 3
    Stinger-Can-Am Combo 3
    Stinger-Can-Am Spyder RT/F3/Ryker 3
    Stinger-Tires 12" & 13" Radial 3
    Stinger-Triple 2
    Stinger- Tongue Jack 2
    Custom 1
    Lock Kit 1
    Powder Coat Rim- Black 1
    Stinger-S X S 1
    Torque Check 0

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to total fields with no date data
    By mick2000 in forum Reports
    Replies: 2
    Last Post: 08-14-2020, 04:57 AM
  2. Replies: 15
    Last Post: 07-22-2017, 05:15 PM
  3. Replies: 4
    Last Post: 04-21-2015, 07:12 PM
  4. Replies: 9
    Last Post: 11-25-2013, 04:33 PM
  5. Total query with date/time fields
    By Ruegen in forum Queries
    Replies: 8
    Last Post: 09-03-2013, 05:32 PM

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