Summing data with a query 1

[mtastad]

I have a table that I want to do some summary calculations on. It is a detail table with multiple records for each Master Part# and the corresponding quantities and costs for the items that make up the master part. I want to create a table that has one record for each Master Part and contains the summary information (total cost) from the group of records in the detail table.

Previously I have been using TCursors to do this type of thing, but I would like to utilize the power of queries. I have been reading through manuals and searching online, but am still stumped.

 

[Langley]

Do a linked query and use 'calc sum as fieldName' - unchecked - (change fieldName to the actual name of the field) in the fields of the detail table you want to get totals for.

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[mtastad]

Thanks Langley - I tried this method and it worked, but...I am trying to calculate the sum of qty * cost (sum of the products) - doesn't this method provide the summary of qty and the summary of cost?

 

[Langley]

Yes, you are right. I misunderstood. You may need to do a second query using example element labels and a changeto query.

answer.db | qty    | cost                       |
          | _qty   | _cost, changeto _qty*_cost |

The result is that the value in the Cost field will be the sum of qty*cost after the query is run. This is a quick and dirty, you could also use another numeric field (something you don't need) to stick the result in, leaving the cost field values unchanged. You could run this first to get the line item totals, but make sure you are using an answer table and not the original.

Haven't done this lately, but I think the syntax is right. Remember, use F5 (not underscores) for example element labels in interactive mode.

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[Langley]

Notes about changeTo queries if you are not familiar with them. You can't check any fields. The table that is displayed immediately after the query (in interactive mode) is called Changed.db and contains your data in its ORIGINAL form (nice for undos). The data in the table upon which the query is based will be changed but not displayed on the screen - you'll have to open it to see the results.

Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us