Additional Join in Query Results in Miscalculation

[tweeterbot]

Hi, I am a chemical engineer trying to design a database and I am running into some trouble.

My database is going to be 'processing' raw data to get the figures we need to prepare the monthly reports. I am given data broken down by blends, plant that they are produced in, and amount, Adj Good Kgs. Each Blend belongs to a Product Group which in turn belongs to a Business Unit (the Business Units don't really come into my question though). My object is to get the sums of the data by Product Group, and then the cost by multiplying them by a price.

I have a query with the data already manipulated a little bit called All Calculated Values. That is where my Plant and Adj Good Kgs fields are from. The Blends and Prod Group table allows me to assign a Product Group and sum them that way.

My problem comes up with the totals query that I am trying to run. When I just have the query All Calculated Values and the table Blends and Prod Groups, it works fine (as long as there are no further relationships defined). It's when I bring in the Pricing table, which allows me to assign a price based on the plant and product group (both are necessary, and the Pricing table uses both of those fields) that I get HUGE values for my adj good kgs, like its adding each record up many times. I don't really understand it myself.

Is it appropriate to try to do calculated fields (ie a cost field that is Adj Good Kgs x Price) that aren't sums in a totals query? Should I just do that elsewhere? Why would it give me such big numbers when all I've done is add one more join?

Hopefully one of you guys can make sense of this. Let me post the code of the query when it's working (w/o the Pricing table and it's join) and when it's not (with Pricing).

(without)

SELECT [All Calculated Values Jan].Plant, [Blends and Prod Groups].[Product Group], Sum([All Calculated Values Jan].[Adj Good Kgs]) AS [SumOfAdj Good Kgs]
FROM [Blends and Prod Groups] INNER JOIN [All Calculated Values Jan] ON [Blends and Prod Groups].Blend = [All Calculated Values Jan].Blend
GROUP BY [All Calculated Values Jan].Plant, [Blends and Prod Groups].[Product Group];
 

(with - additional table and join are bolded)

SELECT [All Calculated Values Jan].Plant, [Blends and Prod Groups].[Product Group], Sum([All Calculated Values Jan].[Adj Good Kgs]) AS [SumOfAdj Good Kgs]
FROM ([Blends and Prod Groups] INNER JOIN [All Calculated Values Jan] ON [Blends and Prod Groups].Blend = [All Calculated Values Jan].Blend) INNER JOIN Pricing ON [Blends and Prod Groups].[Product Group] = Pricing.[Product Group]
GROUP BY [All Calculated Values Jan].Plant, [Blends and Prod Groups].[Product Group];
 

Thanks in advance, for anyone who attempts this one!

[nico5038]

Without going into detail, I'll explain what the cuase is of the big numbers.
When JOINing two tables a so-called "cartesian product" is created. This is a "large" table containing for each row of one table all rows of the other table.
You can try this by creating a table with one field Year and enter 2006 and 2007 as rows and another table with one field Month and the values 1 till 12. When you place both tables in a select like:
select * from tblYear, tblMonth
You'll get 24rows.

In your case the JOIN is limited by a comparison in the ON clause, but when you use non-unique fields, the Cartesian product will cause you trouble.

In general I start with creating a query to calculate the detail data (like the amount) and add additional descriptions and that query I use in a GoupBy query to get the aggregation values.

Nic;o)

[tweeterbot]

Without going into detail, I'll explain what the cuase is of the big numbers.
When JOINing two tables a so-called "cartesian product" is created. This is a "large" table containing for each row of one table all rows of the other table.
You can try this by creating a table with one field Year and enter 2006 and 2007 as rows and another table with one field Month and the values 1 till 12. When you place both tables in a select like:
select * from tblYear, tblMonth
You'll get 24rows.

In your case the JOIN is limited by a comparison in the ON clause, but when you use non-unique fields, the Cartesian product will cause you trouble.

In general I start with creating a query to calculate the detail data (like the amount) and add additional descriptions and that query I use in a GoupBy query to get the aggregation values.

Nic;o)

That makes a lot more sense now.

So are you recommending that I just go ahead and break it up into different queries: one totals query for aggregating and one regular query to calculate price?

[nico5038]

Affirmative, first the "detail" query with the basic calculations (like Price*Quantity) and than the GroupBy to get on the needed field(s) the Sum(), Average(), etc. of the calculated value.

Success !

Nic;o)

[tweeterbot]

Thank you so much! This has been a great help for me in understanding!