Update from a crosstab

[xaurien]

Hi.

Im currently working on a project and need to take the total value from a crosstab query and store it in another table. This may be the completely wrong way of going about this so here is the situation.

I have a table for invoices, with 2 relevant fields. Invoice number and Total

I have another table with Invoice number, Item number, Price.(several items per invoice)

I want to update the invoice table with the total of all the prices from the same Invoice number. (i.e if inv number 1 has 4 items i want the total of those, inv number 2 has 3 i want it to sum those)

My knowledge of access is quite poor, so any help would be appreciated.

Thanks

Sean

 

[PatrickJohnson]

Hi.

Im currently working on a project and need to take the total value from a crosstab query and store it in another table. This may be the completely wrong way of going about this so here is the situation.

I have a table for invoices, with 2 relevant fields. Invoice number and Total

I have another table with Invoice number, Item number, Price.(several items per invoice)

I want to update the invoice table with the total of all the prices from the same Invoice number. (i.e if inv number 1 has 4 items i want the total of those, inv number 2 has 3 i want it to sum those)

My knowledge of access is quite poor, so any help would be appreciated.

Thanks

Sean

What you are describing isn't really a crosstab. You would want to do a maketable query. open a new query in design view. add the table with the item breakdown to your query. then add the invoice number field and the price field from that table to the query. then click the little sigma button up top to "view totals" (or simply click View -> Totals). you will see an extra box pop up under each field name labeled "Total". Under the invoice number, set this setting to "Group By," and under the price set it to "Sum." Run the query. You should get a total of all the prices by invoice number. Once you have verified some of the data, go back to design view and click Query -> Make-Table Query. It will ask you what table you want to make. select the name of the second table (the one with only invoice numbers and totals) and choose current database. Save the query. Then, each time you run it, it will recreate the totals table with totals from your source table.

Having said all that, i think this would lean toward inefficient database design, as you really should have things like totals stored in a report or form only, not as a separate table.

 

[xaurien]

Thank you very much, its not a huge database, and i now dont have time to alter the way im doing it. Thx for the work around!

Sean