DSum

[sargentrs]

Hate to beat a dead horse but I've searched every thread and can't figure out one which fits my application. I'm wanting to use a DSum function in a query to calculate a running sum field. Purpose is to consume inventory until it's exhausted and starts going negative. i.e.

Fields:
COMP_ID DUE_DATE REQD STOCK BAL SHORT
PartA 8/26/08 4 6 2 0
PartA 8/27/08 4 6 -2 2
PartA 8/28/08 6 6 -8 6

The BAL field will be the DSum function. Easy enough to do on a report but I need it in the query and don't know anything about VBA. Can you help?

 

[sargentrs]

Hate to beat a dead horse but I've searched every thread and can't figure out one which fits my application. I'm wanting to use a DSum function in a query to calculate a running sum field. Purpose is to consume inventory until it's exhausted and starts going negative. i.e.

Fields:
COMP_ID DUE_DATE REQD STOCK BAL SHORT
PartA 8/26/08 4 6 2 0
PartA 8/27/08 4 6 -2 2
PartA 8/28/08 6 6 -8 6

The BAL field will be the DSum function. Easy enough to do on a report but I need it in the query and don't know anything about VBA. Can you help?

That post doesn't look at all like I typed it. Hope you can understand what I'm trying to ask.

 

[sargentrs]

Any takers?

 

[georgedwilkinson]

Sorry, you need to re-ask the question and give some details. Not sure why anyone would use DSum in a query.

Post your query. Tell us what you're trying to accomplish with the tables/fields involved. Tell us specifically what you're having problems with/what doesn't work.

 

[sargentrs]

I'm trying to duplicate the calculations in the attached spreadsheet. The database is a scheduling tool mimicking an MRP scenario. Input is a sellable item that uses other parts to make it. Process is as follows:

The input is an Excel spreasheet open order report with quantity and due date.
Explode Bill of Material and show the components with those requirements and dates.
Check inventory and see if stock is available to cover requirements.
If so, show a zero balance, if not, show true requirement by quantity and due date.

Currently, I'm doing the 1st two steps in Access, exporting to Excel to calculate the last two steps and then linking the spreadsheet back into the database for multiple passes (for multilevel bills of material) and reporting purposes. I'm trying to eliminate the export to Excel to link back to Access steps. I'm assuming the DSum function will do that. Am I going about it the hard way? I have zero knowledge of VBA so writing a function is not feasible at this time. The database is over 1Gb and contains hundreds of ODBC links to our manufacturing system, hundreds of queries, tables, forms, reports, etc. It'd be pretty difficult and time consuming to make a duplicate version with much smaller subsets. Take a look at the spreadsheet and see if you can make sense of it. I'll start working on a condensed version of the database. I appreciate your patience.

 

[georgedwilkinson]

Oooo, this is right up my alley. But I'm a little strapped for time and this issue is gonna take some.

If you don't get an answer by tonight (10 hours from now), PM me and I'll take a more detailed look.

 

[sargentrs]

Great! and thanks. No rush. I've been developing this database for 6 years so another day or two won't hurt.

 

[sargentrs]

Get anywhere with this?