Running Calculation on Query

[jmak_1214]

Thank you for reading this thread. I have a problem with creating a query with calculation, please help:
RecordID | PartCount | RowCount
1 | 1 | 9<Default as "9" on Query
2 | 2 | 11 <=2+9, which is PartCount on 2nd record adding RowCount on 1st Record
3 | 5 | 16 <=5+11, which is PartCount on 3rd record adding RowCount on 2nd Record
etc.

Is there any way to make a query like this?

 

[Galaxiom]

Running Sum queries are very intensive because they basically have to run a subquery (usually achieved via a DSum function) for each record.

Put this in a query designer column

RowCount:DSum("[PartCount]","tableorqueryname", "RecordID <=" & [RecordID])

 

[jmak_1214]

Running Sum queries are very intensive because they basically have to run a subquery (usually achieved via a DSum function) for each record.

Put this in a query designer column

RowCount:DSum("[PartCount]","tableorqueryname", "RecordID <=" & [RecordID])

Thank you for you reply, but how about making the first RowCount be default "9", and then add PartCount?

 

[isladogs]

There is a tip on Allen Browne's website which shows how to use values from the previous record to determine a value in the current record.
Although not identical to your situation, you should be able to adapt it.
See http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[jmak_1214]

There is a tip on Allen Browne's website which shows how to use values from the previous record to determine a value in the current record.
Although not identical to your situation, you should be able to adapt it....

Hello, I have tried to use "Get the value in another record" but i could not get the value from the record above. This is the code i have got right now.

SELECT SPQuoteTool.QuoteToolID, SPQuoteTool.SemiProductID, ToolingPhoto.ERPToolID, ToolingPhoto.ToolName, ToolingPhoto.NetWeight, ToolingPhoto.GrossWeight, ToolingPhoto.Up, SPQuoteTool.Usage, SPQuoteTool.Material, SPQuoteTool.Pantone, SPQuoteTool.Color, ToolingPhoto.Machine, ToolingPhoto.Cavity, ToolingPhoto.PartCount, ToolingPhoto.Cycle, ToolingPhoto.ToolID, DCount("QuoteToolID","SPQuoteTool","QuoteToolID<" & [QuoteToolID]) AS [Counter], (SELECT TOP 1 Dupe.PartCount FROM ToolingPhoto AS Dupe WHERE Dupe.ToolID = ToolingPhoto.ToolID) AS PriorValue FROM SPQuoteTool INNER JOIN ToolingPhoto ON SPQuoteTool.ToolingPhotoID = ToolingPhoto.ToolingPhotoID WHERE (((SPQuoteTool.SemiProductID)=42)) ORDER BY SPQuoteTool.QuoteToolID;

 

[Galaxiom]

how about making the first RowCount be default "9", and then add PartCount?

You could add 9 to the expression but it is better to put in a starting record that will be picked up by the DSum. For example a record ID 0.

This way the start value is actually stored in a table rather than being hard coded. Good design avoids hard coding arbitrary values.

However you would also need to take steps to ensure that record is never deleted or inadvertently updated.

 

[JHB]

Post some sample data with the field names you use, (either in MS-Access database or Excel Sheet.)
Zip it then you haven't post 10 post yet.

 

[jmak_1214]

You could add 9 to the expression but it is better to put in a starting record that will be picked up by the DSum. For example a record ID 0.

This way the start value is actually stored in a table rather than being hard coded. Good design avoids hard coding arbitrary values.

However you would also need to take steps to ensure that record is never deleted or inadvertently updated.

Thanks, but in this case, I will always need the first record to be 9 and then sum below two columns.

 

[jmak_1214]

Post some sample data with the field names you use, (either in MS-Access database or Excel Sheet.)
Zip it then you haven't post 10 post yet.

View attachment SampleData.zip

Hi, here you go. Thanks in advanced.

I want to add a column with RowCount.

 

[isladogs]

You haven't really supplied enough data in 'myQuery' to test this properly

Try pasting this SQL into a query and see if it works when you have additional data:

SELECT myQuery.QuoteToolID AS RecordID, myQuery.SemiProductID, myQuery.PartCount, 9+Nz((SELECT TOP 1 Dupe.PartCount             
   FROM myQuery AS Dupe                     
   WHERE Dupe.SemiProductID = myQuery.SemiProductID
     AND Dupe.QuoteToolID < myQuery.QuoteToolID
   ORDER BY Dupe.QuoteToolID),0) AS RowCount
FROM myQuery;

With the data you supplied I get:

NOTE: I've hard coded the first value as 9 as instructed but if you change that later you'll need to modify the query

 

[Mark_]

Much more important, do you need this IN a query or as a running total in a report?

 

[jmak_1214]

You haven't really supplied enough data in 'myQuery' to test this properly

Try pasting this SQL into a query and see if it works when you have additional data:

SELECT myQuery.QuoteToolID AS RecordID, myQuery.SemiProductID, myQuery.PartCount, 9+Nz((SELECT TOP 1 Dupe.PartCount             
   FROM myQuery AS Dupe                     
   WHERE Dupe.SemiProductID = myQuery.SemiProductID
     AND Dupe.QuoteToolID < myQuery.QuoteToolID
   ORDER BY Dupe.QuoteToolID),0) AS RowCount
FROM myQuery;

With the data you supplied I get:

NOTE: I've hard coded the first value as 9 as instructed but if you change that later you'll need to modify the query

Thank you.

I have implied the code into my project, but it does not calculate the running sum.

RecordID | PartCount | RowCount
1 | 1 | 9 <Default as "9" on Query
2 | 2 | 11 <=2+9, which is PartCount on 2nd record adding RowCount on 1st Record
3 | 5 | 16 <=5+11, which is PartCount on 3rd record adding RowCount on 2nd Record

Please see attached for the current query with the updated code.View attachment SampleData1.zip

 

[jmak_1214]

Much more important, do you need this IN a query or as a running total in a report?

I need this in query in order to set the layout of a exported excel file. The excel file has to exported in a certain way (which is kind of annoying).

 

[Mark_]

Ah, it is for export purposes. I am guessing having excel do this is out of the question? Also, how are you exporting to excel?

If you do the export as a DoCmd, then it isn't easy to get in to make changes. If you have a loop through your record source that is updating individual cells then you can do the total yourself fairly easily.

 

[isladogs]

Apologies.
I misunderstood your requirements and the previous approach I suggested was incorrect.

Attached is an updated version of your latest sample data with a proper running sum query (myQuery):

SELECT SPQuoteTool.QuoteToolID, SPQuoteTool.SemiProductID, ToolingPhoto.ERPToolID, ToolingPhoto.ToolName, ToolingPhoto.NetWeight, ToolingPhoto.GrossWeight, ToolingPhoto.Up, SPQuoteTool.Usage, SPQuoteTool.Material, SPQuoteTool.Pantone, SPQuoteTool.Color, ToolingPhoto.Machine, ToolingPhoto.Cavity, SPQuoteTool.PartCount, ToolingPhoto.Cycle, ToolingPhoto.ToolID, 9+Nz(DSum("PartCount","SPQuoteTool","QuoteToolID <= " & [QuoteToolID]),0) AS RowCount
FROM SPQuoteTool INNER JOIN ToolingPhoto ON SPQuoteTool.ToolingPhotoID = ToolingPhoto.ToolingPhotoID
WHERE (((SPQuoteTool.SemiProductID)=42))
ORDER BY SPQuoteTool.QuoteToolID;

Here is a shorter version (Query2A) showing the important fields for this purpose:

There is an alternative version (Query2B) using an aggregate query which will be better if you need to group values together ... but that query is read only

Note:
Both are based on a DSum function which WILL get slow if you have a lot of records

 

[JHB]

Here is another solution without DSum.

 

[jmak_1214]

Apologies.
I misunderstood your requirements and the previous approach I suggested was incorrect.

Attached is an updated version of your latest sample data with a proper running sum query (myQuery):

SELECT SPQuoteTool.QuoteToolID, SPQuoteTool.SemiProductID, ToolingPhoto.ERPToolID, ToolingPhoto.ToolName, ToolingPhoto.NetWeight, ToolingPhoto.GrossWeight, ToolingPhoto.Up, SPQuoteTool.Usage, SPQuoteTool.Material, SPQuoteTool.Pantone, SPQuoteTool.Color, ToolingPhoto.Machine, ToolingPhoto.Cavity, SPQuoteTool.PartCount, ToolingPhoto.Cycle, ToolingPhoto.ToolID, 9+Nz(DSum("PartCount","SPQuoteTool","QuoteToolID <= " & [QuoteToolID]),0) AS RowCount
FROM SPQuoteTool INNER JOIN ToolingPhoto ON SPQuoteTool.ToolingPhotoID = ToolingPhoto.ToolingPhotoID
WHERE (((SPQuoteTool.SemiProductID)=42))
ORDER BY SPQuoteTool.QuoteToolID;

Here is a shorter version (Query2A) showing the important fields for this purpose:


There is an alternative version (Query2B) using an aggregate query which will be better if you need to group values together ... but that query is read only

Note:
Both are based on a DSum function which WILL get slow if you have a lot of records

Thank you very much for your help. After I have put in the calculations, I figured out that my original request is not quite accuracy neither, sorry about that. But I have managed to work out the ACTUAL count that I want with 2 queries, please see attached. The actual result that I needed is on myToolQuery.RC

View attachment SampleData2.zip

Is there any other way to save some loading time?

 

[jmak_1214]

Here is another solution without DSum.

Thank you very much for your help. It is beautiful. But I actually need to add 1 row for each part count and then do the running sum. Please see the SampleData2.zip as attached in the above reply.

Is there anyway to do this without using 2 queries?

 

[jmak_1214]

Updated the SampleData with JHB's calculation:
View attachment SampleData3.zip

 

[JHB]

Thank you very much for your help. It is beautiful. But I actually need to add 1 row for each part count and then do the running sum. Please see the SampleData2.zip as attached in the above reply.

Is there anyway to do this without using 2 queries?

It is not quiet clear for me what you want, can you show it in a printscreen or Excel sheet the result you want, (using the data you've provide)?