Using DSUM resetting upon next ID in dataset

[ellisjo3]

You can see below my very simplified example. I want RunTot to sum until a new ITEM is reached, then reset.
Ideally,




But here is my output:


Here is my code in Query Design:
RunTot: DSum("QTY","TABLE1","ITEM=[ITEM]")
I'm aware the last part is where I'm wrong.. help please?

[rpeare]

Is there a reason you're doing this in a query instead of as a running sum on a report? Using Domain functions in queries is extremely memory intensive and will bloat your database very quickly.

RunTot: DSum("QTY","TABLE1","ITEM= " & [ITEM])

if ITEM is a number (autonumber field)

RunTot: DSum("QTY","TABLE1","ITEM= '" & [ITEM] & "'")

NOTE: this will not work because there is no way to identify a differentiation between ITEM1 records, if you had a date in there and there was no possibility that the same item had the same date you could use:

RunTot: DSum("QTY","TABLE1","ITEM= '" & [ITEM] & "' AND [SaleDate] <= #" & [SaleDate] &"#")

if ITEM is a text field (description)

I'd really strongly suggest you try to do running sums in reports only, trying to do them in queries is really a bad practice. I'll explain why. Every time you use a domain function it is processing your ENTIRE data set. So let's just take this query for example. You have 8 records, what your query is actually doing is individually processing each record of those 8 records for every line in your query. So you're not actually running 1 query, you're running 9, the main query and a separate query for each domain function. You can see how this would quickly get out of hand for larger data sets. Let's say this is just a total for january, now it's december and we'll assume for the sake of simplicity you've had 8 records every month for the whole year, your domain function will now be processing 96 lines of data for each line of your december data and it will be doing that 8 times for each domain function.

[ellisjo3]

The ultimate goal is to compare available inventory vs. customer orders and to signal which order we run out by, and yes they are in date order.

Example: 6 pcs available, customer orders for 1,3,2,5. My goal is to show that we are 'covered' for the first 3 orders as 6 pcs are available for 6 needed. Then to show the final order for 5 as NOT AVAILABLE.

I've taught myself my way through Access so I am sure I have some bad practices..

Thank you for the response, I'll give your date suggestion a go and come back here with results.

[rpeare]

You can do that without using domain functions.

[ellisjo3]

Sorry, I'm not sure I follow. Can you expand on your suggested method? I'm OK using Excel for as much as possible, to simplify for other users too.

[rpeare]

How are you keeping track of your receipts vs your expenditures? Are they in separate tables?

In other words how do you know how much you have on hand. I am assuming this is some sort of inventory control database where you're recording incoming and outgoing materials and hopefully coming up with an on hand quantity and what you're trying to figure out is some sort of projection report?

[ellisjo3]

Essentially it's an order backlog table from excel I'm importing into access, including all orders with their due dates, on hand quantity, etc.

With this, sorted by oldest due date, I want to filter my list to orders that we do not have available inventory. It narrows the focus for the operations team.

I have a stagnant field that shows qty on hand when the report is run.

My thought was if I could create a running total of open qty and sort by oldest order, I can compare to on hand Qty and only show the orders that exceed available qty.

[rpeare]

I am not following your description of how your data is set up. How are you getting your 'on hand' quantity if you're not storing receipts as well as disbursements of inventory? Are you saying on every order you are also have a field that is quantity on hand after the items are removed? what do you mean by 'I have a stagnant field that shows qty on hand when the report is run' I don't follow that bit at all.

Any chance you can provide a sample of your database.

[ellisjo3]

The report is created each day from our ERP system and dumped into Excel. On Hand Qty is from the snapshot in time when the report is run. In the example below, we have 6 on hand of ITEM1, but orders that total 19 for this item. I eventually would compare the running total to see what day we are unable to support the order. In this example, We have enough (6) to support the first 3 orders, before being out of inventory.

The RunTot column is my goal so that I can use the available column to see where we are short.

[rpeare]

Which of these fields come from the ERP report other than the item and the on hand qty?

I suppose before I get on my high horse about not using domain functions I should ask, I have assumed to this point that you want to print out a report... but that may not be accurate are you trying to put this information on a form? Or are you required to leave it in a query?

Here's an example file putting the information on a report.

ellisjo3.zip

[ellisjo3]

Every field comes from the ERP report, and I would calculate 'remaining' such as what you did.

I would actually need it in a query so that I can export to Excel and add things such as comments from different people and sort different ways for different use.

That looks so simple with a report though =/

[rpeare]

Every field can't be coming from the export or we wouldn't be having this conversation

Are you saying every field except runtot and available? come from the ERP side?

Is there a reason you can't do all your 'add things such as comments from different people and sort different ways for different use' from access directly and use the access side as a general lookup database for your users? Seems like an awful waste of time to import a spreadsheet into access, maniuplate it then re-export it to access. It might just be easier to manipulate the original text field to add the two fields (runtot and available) via VBA than importing/re-exporting the whole thing.

[ellisjo3]

Yes everything except Runtot and Available.

I'm with you on the VBA part, that's what I meant to ask originally, but probably failed to explain clearly.

I get the report (minus runtot and available) in Excel. I was importing to Access to do a lot of filtering based on the next 7 days of orders, excluding orders that are on a HOLD, excluding certain types of ITEMS, etc.
The ideal step for me is to include runtot and available in another Query design, with a step that looks like what you created in the Report. With this runtot and available, I can filter out what is covered, and only display the orders that are not fulfilled.

I apologize for any confusion, but the VBA code (DSUM from what I read) is what would be best for me at this time. I struggled with how to create a 'runtot' field that reset when it reached a new ITEM.

IF we can create runtot in a query design, my problem is solved.

[rpeare]

I'm using VBA differently than you are, Domain functions are not VBA, the are part of a 'default' set of code that allow you to perform specific functions.

when I talk about VBA I mean that you process your text file without importing it at all but it doesn't seem you want to do that either.

I am trying desperately to dissuade you from using a domain function in a query because they are so hard on memory and bloat your database but you can do it using a formula like this:

(This assumes that you are sorting your data FIRST by item, SECOND by date, THIRD by order number)

RUNTOT: Dsum("[Order Qty]", "Table1", "[ITEM] = '" & [ITEM] & "' AND [DUE] <= #" & [DUE] & "# AND [ORDER] <= '" & [ORDER] "'")

this assumes order and item are both text fields.

Just remember I warned you. DSUM is going to affect both your runtot and remaining columns in effect you are going to be processing the number of records in your TABLE1 2x for every record of your report so if you have 2000 records in your table 1, even if only 10 of them are showing you are processing, in effect, 40,000 records just to create this query.

[ellisjo3]

I know I know I know. The novice description under my account name is pretty accurate of my level of experience.

You have been extremely helpful. The only missing piece now.. If the Order # for a LATER date is less, it restarts.. see below.
Instead of 2, it needs to show 5.
I am sure this tweak is minor to the code, but I am really struggling.