Query wrong sum

[Mikey1987]

Hello,

I got perhaps a silly question, but I can't seem to figure it out.

I want to do a query on invoices.
There are 2 tables: a table with the invoiceID, the NumberofHoursWorked and the SalaryPerHour
The other table contains Material (with a price and the number of items) for each invoice.

Obviously, an invoice can contain several items, and an item can appear on many invoices.

The query I want to do is as simple as this: Calculate the total amount of the invoice
Total Labor cost = NumberOfHoursWorked * SalaryPerHour
Total Material cost = sum of the cost of all items (item price * number of items)
Tax: (Total Labor cost + Total Material Cost) * Tax percentage



That all works fine, but when I do the query, I got the Total Labor Cost for each of the material items
so InvoiceID 1 with 3 items, would count the Total Labor Cost 3 times

It probably has something to do with group by and stuff, but it's not working...

any ideas?

[June7]

That would be expected in a query that joins these two tables.

Consider a report/subreport arrangement.

Or if you don't care about the details of materials, do an aggregate query that summarizes the material costs by InvoiceID and then join that query to the Invoices table.

[Mikey1987]

Hello june7

I indeed don't care about the details. The query will fuel a subform, for which I simply need the total amount.

How would you build that aggregate query exactly? Do I need a subquery perhaps? All I want is the SUM of all the items * their price, and the hours worked * salary (and the sum of these multiplied with tax %)

Any advice would be greatly appreciated

[June7]

What fields are in the materials table?

[Mikey1987]

It's actually a junction table between the material table and the invoice table

There are 3 fields in the junction table: invoiceID, materialID and "Material_Amount".

The materialID of course relates to items in the materialntable, with among others the price of the item

[June7]

In query builder: join the Materials table to the junction table linking on MaterialID fields. Then click the Totals button on ribbon.

Group by the invoiceID, do Sums on Material_Amount and Price or Sum on Material_Amount * Price.

Save that query.

Now build another query that joins the aggregate query to the Invoices table by linking on InvoiceID fields. Do other calcs in this query.

The two queries can be a single query if you want to do nested statements in SQL View.

[Mikey1987]

In query builder: join the Materials table to the junction table linking on MaterialID fields. Then click the Totals button on ribbon.

Group by the invoiceID, do Sums on Material_Amount and Price or Sum on Material_Amount * Price.

Save that query.

Now build another query that joins the aggregate query to the Invoices table by linking on InvoiceID fields. Do other calcs in this query.

The two queries can be a single query if you want to do nested statements in SQL View.

That worked like a charm... awesome, thx mate!