Creating a Inventory Line Of Balance Adjustment to Scheduled Production

[leopold2015]

Below I have a query that outputs data into an unusable manor, due to the size and manipulation required in excel to make it worth while.

Current Code:
[SELECT U.partno, U.op, Format(u.esh,"Fixed") AS esh, U.consumptiondate, U.inv
FROM (SELECT partno, operation_ as op,ExtendedESHCalc as ESH, format(routingconsumptiondate,"mm/dd/yyyy") as consumptiondate, '' as inv
FROM RoutingConsumptionWithSpanQ
order by partno, operation_, routingconsumptiondate


union all
SELECT partno, operation_ as op, sum((InvExtendedESHCalc*-1)) as ESH, '' as consumptiondate, 'Inventory' as inv
FROM RoutingInventoryValueQ
group by partno,operation_) AS U
ORDER BY U.partno, U.op, U.inv, U.consumptiondate;]

Example Of Adjusted Consumption Based on Current Inventory.zip

I have attached an excel file that shows the current output with grey headings and a yellow headed column of what I would like to see. I am a new user to the forum and fairly new to access, so I am in over my head. All help appreciated.

[June7]

You want to accomplish in Access what you currently do in Excel?

This will likely involve several queries.

The calculation involves a progressive sum, this is not simple in query. I think this is like a 'year to date' query. Review: http://allenbrowne.com/subquery-01.html#YTD

This should give you an idea of the complexity of what you want. Get that query working first then can deal with the IIf().

[leopold2015]

No, I am not currently doing this in Excel. I was simply trying to display what I want done in Excel.

I have done some DSum type functions to try and slowly build it out like I would in Excel but the Query is very slow and crashes when I do. I am simply (maybe not so simply) trying to figure out how I can get a production plan with consumption dates to recognize that inventory has already been created for the lots being calculated.

[June7]

Yes, domain aggregates can be poor performers. However, nested queries can also be slow and crash depending on the size of dataset and if based on complex subqueries although usually run better than equivalent domain aggregate.

[ssanfu]

due to the size and manipulation

How many records are in the dataset?

[leopold2015]

49,000 Rows and 4 columns in this query output, that if done in excel would all need the sample sum if type logic I put in the attachment.

[ssanfu]

I don't know how fast this will be with 49k records. But here is an example of the calcs using VBA based on the data in the spreadsheet.