Append Query

[jlgray0127]

I have a query (BoM_ItemAnalysis_RunTot) with a calculation in it which does a running total on demand for material. I also have a calculation which subtracts the running total from the On Hand inventory to identify which builds I may run out of parts on.
I need to create a temp table to due further analysis and generate reports from.
The query runs in less than 1 second.

I created an append query, using (BoM_ItemAnalysis_RunTot) as the source to append the results to a temporary table. I first did a make table and only ran it against 1 record to create the table. I then removed the criteria to append all records and all it will do now is run.

My SQL is below for the append. Thoughts on how I can make this work?


INSERT INTO BoM_ItemAnalysis_RunTot_T ( ID, Component, BuildPriority, RECOV_DATE, PO_RTS, Comp_Descript, Comp_Type, Comp_Stat, TotalQty, RunningTotal, OH, Balance, [S&OP_CATEGORY], PART, CAT_Part, PO_RTS_DATE, STATUS, PRODUCT_DESCRIPTION, PRODUCT_NAME )
SELECT BoM_ItemAnalysis_RunTot.ID, BoM_ItemAnalysis_RunTot.Component, BoM_ItemAnalysis_RunTot.BuildPriority, BoM_ItemAnalysis_RunTot.RECOV_DATE, BoM_ItemAnalysis_RunTot.PO_RTS, BoM_ItemAnalysis_RunTot.Comp_Descript, BoM_ItemAnalysis_RunTot.Comp_Type, BoM_ItemAnalysis_RunTot.Comp_Stat, BoM_ItemAnalysis_RunTot.TotalQty, BoM_ItemAnalysis_RunTot.RunningTotal, BoM_ItemAnalysis_RunTot.OH, BoM_ItemAnalysis_RunTot.Balance, BoM_ItemAnalysis_RunTot.[S&OP_CATEGORY], BoM_ItemAnalysis_RunTot.PART, BoM_ItemAnalysis_RunTot.CAT_Part, BoM_ItemAnalysis_RunTot.PO_RTS_DATE, BoM_ItemAnalysis_RunTot.STATUS, BoM_ItemAnalysis_RunTot.PRODUCT_DESCRIPTION, BoM_ItemAnalysis_RunTot.PRODUCT_NAME
FROM BoM_ItemAnalysis_RunTot


WHERE (((BoM_ItemAnalysis_RunTot.RECOV_DATE)<Now()+90));

[June7]

Instead of APPEND, try a MAKE TABLE for all data.

Use VBA to delete table and run MAKE TABLE.

[jlgray0127]

Thank you for the response! I am unfortunately having the same results. The query to make the table just runs and seems to get hung up. After 10 minutes, the status bar shows the query as being "JUST ABOUT DONE" and then it just stalls out.

[jlgray0127]

I even reduced the data to the 5 key columns I need the snap shot of, and am receiving the exact same performance. I am at a loss. There are approx 147,000 records from the results of the query, but I have never had this type of performance error before, in this scenario.

[June7]

You have done APPEND with running sum query successfully with other data?

Access doesn't handle this sort of calculation in queries nicely. Poor performance is not uncommon with Cartesian product SQL or domain aggregate function. SQLServer and other RDBMS platforms have functionality for this. I've never needed running total in query.

[jlgray0127]

I have not used the results to append to a table before.
Somehow I need to though, as you eluded to, Access struggles with this type of equation.
I can not do any analysis work to the data results due to this, and was hoping appending to the temp table, I'd be able to build queries to perform the analysis to the data.
Any new query to analyze the results of the running totals query does not work.

This is a real struggle!

Any thoughts on how to evaluate the running total as an absolute value vs a calculated result? If I try and query against the results, Access does not handle this well at all. Results are inconsistent and not able to process it.

[jlgray0127]

So, I did some digging on another database I have with a running total calculation in it.
I used DSum in this query, but only on one criteria. I created a make table and then updated to append query and the query worked just fine with using DSum!

I'm not the greatest with DSum and am struggling to recreate the statement in my new query with two criteria.


RunningTotal2: DSum("[TotalQty] ","[BoM_ITemAnalysis_T]","[ID]<= " & [ID] & " And [Component] <= " & [Component])
The results are #ERROR

Can someone please help me with this formula? I'm sure I'm missing something simple!

Thank you so much, in advance!!!!

[Micron]

Can you get that to not produce the error with only 1 criteria? The criteria values are actual number data type and not text?
Wondering if the space at the end here might be an issue - "[TotalQty] "
Also, how is this helpful - "[ID]<= " & [ID] & " And [Component] <= " & [Component]) ? If you substitute values the written version would be like

5<= 5 And 8 <= 8)

[June7]

Is Component a number or text field? Text field parameter needs apostrophe delimiters.

Should Component use = instead of <=?

If you want to provide db for analysis, follow instructions at bottom of my post.

[jlgray0127]

[jlgray0127]

Thank you!
Yes, it is a text box! Can you please help me? I'm not sure if I'm just brain fried or what my deal is. I can not seem to get the expression right on the text box.
Can you please help? I do not fully understand how all the quotes work in a statement like this, and am struggling to find a reference that 1, explains what the equation is doing, and two, a sample of a multiple criteria with text box.

I do think I can change it to Component = Component. I believe with my first statement below, there may have been a problem with restarting the running total on the first record where the component changed, though, which is why I went to the <=. I'll switch back to <= if it repeats.

Thank you, again!!!

[jlgray0127]

I got it!
It still is not pasting to the table like I did in the sample database I had, but I figured out the expression... my goodness!
You know the saying, "I could not see the forrest, for all the trees." ?

I think I'd been looking at this too long and trying to many different things. Sorry about that!

It did not fix my problem as I'd hoped, sadly.

[June7]

Text box is not same thing as text field.

I asked if Component is a text field - data type in table.

[jlgray0127]

Yes, it is a text field. Apologies, that is what I was attempting to state.

[June7]

So did you fix with apostrophe delimiters:

"[ID]<= " & [ID] & " And [Component] = '" & [Component]) & "'"