How to write this inter-table update/append query?

[Hyder]

Hi, I'm using access 2003 or 2007 and have some programming background but a
minimum knowledge about SQL, anyhow i'm making a database for my a small
company where the Invoice table has a one-tp-many relation with InvoiceLine
table. InvoiceLine represents individual items, quantity, price etc on the
Invoice.
now I've writen a an Update Query that base on the Quantity * Price works
out the SubTotal for every InvoiceLine but then I have problem with writing
the correct query on how to Sum UP all the SubTotals with the similar
InvoiceNumber and Placing this in the Total cell of Invoice Table.
I use DSum([InvoiceLine]![SubTotal],InvoiceLine])
and Sum([InvoiceLine]) but all return empty sets,
All have currency Type and by default set to Null
looking forward to your reply.
Thanks : Hyder

 

[Jason]

I’m by no means an MVP, so there may be a more elegant solution but you could
try this:
1. Either turn your update query into a totals query or make a copy of it
and turn the copy into a totals query. (in design view: View > Totals).
2. Turn it back into a select query long enough to play with it and make
sure you are getting the results you want.
3. Keep the invoice # field at the default “Group By†setting
4. Change the setting for the line item total field to “Sumâ€
5. Depending on the other fields you have in the query, change them to
“First Ofâ€, “Last Ofâ€, “Expressionâ€, or just get rid or them. (The goal is to
get a total sum based on the individual invoice #. Everything else is just
details.)
6. Use whatever method you like to filter the results down to the Invoice #
you are working with. If you do it right, you will have just one record in
the resulting dataset, and it will contain the invoice total in it.
7. When you are ready, turn it back into an append query.
8. No muss, no fuss, no coding.

Hope this works for you,
Jason