Query with multiple tables and multiple sum columns

[monkeyman77]

Hello, I am attempting to create a query that will allow me to sum all of the labor costs and material cost in the same query. Labor and material costs are stored in separate tables.

However, when I do this I am running in to an issue where the sum values are wrong. I think this is because sometimes there are multiple rows in the corresponding tables and the other table doesn't have the same number of rows.

I have created the attached sample database that has the problem I am having and a picture of what the values should be. I am hoping someone can help me to correct this error. Maybe I can't do what I am trying to do.

Thank you in advance!
Steven

 

[plog]

This should be simple and your method should work, however; you haven't set up your tables properly.

You shouldn't have tables with the exact same structure. When you do that you are essentially using the table name to store a pertinent piece of data (Type). That means Labor and Material should not be seperate tables, instead you should have just one table called Costs:

Costs
cost_ID, autonumber, primary key
JobNumber, text, foreign key to JobNumbers
cost_Amount, currency, this will hold the dollar amount currently in MCost and LCost
cost_Type, text, this field will differentiate if a cost is for Material or Labor

Then, with that structure the query is trivial:

SELECT JobNumber, cost_Type, SUM(cost_Amount) AS TotalCost FROM Costs GROUP BY JobNumber, cost_Type

Restructure your data correctly and you make things so much simpler.

 

[monkeyman77]

I agree that would make things simpler but the database uploaded is just a simple example of what I am trying to do.
My labor is going to be entered in by one user and the material is going to be enter in by another user at two different time frames.

Is there a way to do what I am trying to do?

 

[monkeyman77]

I was able to figure out away to do what I was looking for by create a few querys to make it work. However, I can't imagine this is the best way to do this.

The attached database shows:
Query1 from the original post that provides the wrong information
QueryM for the material cost
QueryL for the labor cost
Query2 that combines QueryM and QueryL

Like I said this can't be the best way to do this. If any one has a better solution, I be very interested in learning.

Thank you!

 

[plog]

My labor is going to be entered in by one user and the material is going to be enter in by another user at two different time frames.

I don't understand that reasoning. I mean, all cost recordss will be entered at different time frames--they all don't go in there exactly at the same millisecond. So why is a time difference for a different types of cost an issue that makes normalization not practical?

 

[monkeyman77]

If I did it the way you are suggesting then I'd have a table with incomplete rows. It would end up looking something like the attached table. That would work.

 

[plog]

That's not how I said. You are still storing the type in the field name. You will not have a field for MaterialCost nor LaborCost. You will have a field simply called [Cost] and then another field called [CostType] where you will store if it is for Material or Labor.

 

[mike60smart]

Hi
Plog has suggested the correct way to do this.

See the attached example

 

[Pat Hartman]

I agree with the others. The incorrect table design is causing the problem. There is a way to get past it though, you can normalize on the fly with a Union query. Create a union to get the material and labor costs into ONE recordset. Then join to the union query using a left join.

 

[monkeyman77]

Hi
Plog has suggested the correct way to do this.

See the attached example

Hi Mike, I see what you did here but the original example I uploaded was a simple example. I have added some columns to the database attached to so a more complicated example of the tables.

I need some help with table design, because I am unable to take this attached example and turn it in to the example you uploaded.

 

[mike60smart]

Hi Mike, I see what you did here but the original example I uploaded was a simple example. I have added some columns to the database attached to so a more complicated example of the tables.

I need some help with table design, because I am unable to take this attached example and turn it in to the example you uploaded.

Hi

I cannot open the attachment. Did you upload the database while it was still open?

Best to close the database and then upload again.

 

[monkeyman77]

Hi

I cannot open the attachment. Did you upload the database while it was still open?

Best to close the database and then upload again.

Try that one.

 

[mike60smart]

Try that one.

Hi
That is the same database that I uploaded previously. Just 3 tables a few queries and no Forms ??

 

[monkeyman77]

Yes, but I added a few fields to the tables to make the database a bit more complex. I'm still trying to get to the same end goal which is a query that shows me the results in the attached image. I am unable to accomplish this.

 

[mike60smart]

Yes, but I added a few fields to the tables to make the database a bit more complex. I'm still trying to get to the same end goal which is a query that shows me the results in the attached image. I am unable to accomplish this.

Not understanding because your Query2 gives you exactly that result:-

Query2 Query2

JobNumber	JobName	SumOfMCost	SumOfLCost
Job1	House	£30.00​	£100.00​
Job2	Street	£40.00​	£450.00​
Job3	Car	£5.00​	£650.00​
Job4	Tree	£100.00​	£1,000.00​