Re: [MS_AccessPros] Sum of inventory by date question

 

Jim-

The easiest way is to build yourself a "driver" or "tally" table that contains one row with a date in it, and the rows span the full amount of time - one day at a time - that covers your events.  It's easy to load a table like this with a bit of VBA code:

Sub LoadDates()

    Dim db As DAO.Database, rst As DAO.Recordset, datDate As Date, intI As Integer

    datDate = #1/1/2017#   ' Start date

    

    Set db = CurrentDb

    Set rst = db.OpenRecordset("SELECT * FROM ztblDates", dbOpenDynaset, dbAppendOnly)

    

    For intI = 1 To 365 * 10   ' Loading about 10 years

        rst.AddNew

        rst!DateField = datDate

        rst.Update

    Next intI

    rst.Close

    Set rst = Nothing

    Set db = Nothing

End Sub

Let's say your query is named qryEvents.  I assume you want totals out for each day from the delivery date to the end date.  To get daily totals, do this:

SELECT ztblDates.DateField, Sum(Quantity) As TotalItem23Out

FROM ztblDates, qryEvents

WHERE DateField BETWEEN DeliveryDt And EventEndDt

GROUP BY DateField;

Note that there is NO join between the two tables - this "explodes" your result to one row per day of interest.

If you get rid of the filter (WHERE ItemID = 23) and include ItemID in your query, you can get a total by item by date by grouping on ItemID and DateField.  (I notice you have TWO copies of EventEnd in your query - you need to remove one of those.)

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Jun 23, 2017, at 00:33, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

We have a database that tracks inventory being used sort of like a rental database. the database is for events that users can order chairs, tables etc for the moving and event department to deliver. I need to get a query to show the sum of inventory for chairs for every date being delivered. I have a table such as below.

the main sql is below

SELECT Events.EventID, tblEventDetails.Quantity, Events.DeliveryDt, Events.EventEndDt, Events.EventStartDt, Events.EventEndDt, Events.PickupDt
FROM Events INNER JOIN tblEventDetails ON Events.EventID = tblEventDetails.EventID
WHERE (((tblEventDetails.ItemID)=23))
ORDER BY Events.EventID, Events.DeliveryDt;

Event Id 18624

Qty    14

DeliveryDt 6/29/2017

EventEndDt    8/1/2017

another record

Event id 19214

Qty    200

DeliveryDt    6/29/2017

EventEndDt    6/30/2017

so I am trying to break it down so that each date has a sum for the inventory type

how could I get a query (probably a crosstab) to show each date with the sum like below

6/29/2017 214

6/30/2017 214

7/1/2017 14

7/2/2014 14

etc

Thank you for any help.

Jim Wagner

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___