How do I prevent values from been multiplied by two or three?

  • Thread starter Thread starter legacy
  • Start date Start date
L

legacy

Hi,
I created a query to add values from two different tables. However, some of
the results are been multiplied by two and some of them by three. Is there
anything I need to do on the query itself to solve this calculation issue?

Thanks
 
legacy said:
Hi,
I created a query to add values from two different tables. However,
some of the results are been multiplied by two and some of them by
three. Is there anything I need to do on the query itself to solve
this calculation issue?
Click to expand...

Probably
Show us the sql statement (switch your query to SQL View) and we might
be able to offer a solution.
 
You have a Cartesian effect due to not joining the two tables. It multiplies
each record in table1 by the number of records in table2.
If you join the tables you won't get the effect.
If you do not know how to join the table post the structure indicating table
and field names with datatype. Post sample data also. What field is common?
 
Bob,

I have included the SQL statement for your review.

Thanks

SELECT ProjectInformaiton.ProjectNumber, PanelHardware.PieceMark, Sum(((([SteelCostPerFt]*([SteelLength_Inches]/12))*([SteelQuantity]))*[QtyNeeded])) AS CostPerSteel, Sum(([MiscCost]*[MiscQuantity])*[QtyNeeded]) AS CostPerMisc, Sum((([RebarCostPerFt]*([RebarLength_Inches]/12))*([RebarQuantity]))*[QtyNeeded]) AS CostPerRebar, Nz([CostPerSteel])+Nz([CostPerMisc])+Nz([CostPerRebar]) AS TotalCost
FROM Stud_Misc_Hardware RIGHT JOIN ((SteelInventory LEFT JOIN SteelInventoryQuery ON SteelInventory.SteelID = SteelInventoryQuery.SteelID) RIGHT JOIN ((Rebar LEFT JOIN RebarQuery ON Rebar.RebarID = RebarQuery.RebarID) RIGHT JOIN (ProjectInformaiton INNER JOIN (((PanelHardware LEFT JOIN RebarBreakdown ON PanelHardware.PieceMark = RebarBreakdown.PieceMarkID) LEFT JOIN SteelHardwareBreakdown ON PanelHardware.PieceMark = SteelHardwareBreakdown.PieceMarkID) LEFT JOIN Stud_Misc_HardwareBreakdown ON PanelHardware.PieceMark = Stud_Misc_HardwareBreakdown.PieceMarkID) ON ProjectInformaiton.ProjectNumber = PanelHardware.ProjectNumber) ON Rebar.RebarID = RebarBreakdown.RebarID) ON SteelInventory.SteelID = SteelHardwareBreakdown.SteelID) ON Stud_Misc_Hardware.MiscID = Stud_Misc_HardwareBreakdown.MiscID
GROUP BY ProjectInformaiton.ProjectNumber, PanelHardware.PieceMark
HAVING (((ProjectInformaiton.ProjectNumber)=[Enter Project Number]));




Bob Barrows wrote:

legacy wrote:ProbablyShow us the sql statement (switch your query to SQL View)
22-Apr-10

legacy wrote

Probabl
Show us the sql statement (switch your query to SQL View) and we migh
be able to offer a solution
-
HTH
Bob Barrows

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Crypto Obfuscator for .NET - Product Review
http://www.eggheadcafe.com/tutorial...f8-f5fd987fafb1/crypto-obfuscator-for-ne.aspx
 
Sorry, I totally forgot about watching this thread. Have you solved this
on your own yet? Didn't your first post mention a problem with adding
values from two tables? There are many more than two tables in this
query. Without access to your actual data and requirements, there is no
way I can even start telling you what's wrong.

In a broad sense, Karl is correct, at least one of the tables in this
join is causing more than one record to be returned for a particular
ProjectNumber and PieceMark combination. Based on your knowledge of the
data, can you deduce which table that might be? It's a table that likely
contains multiple records for each ProjectNumber/Piecemark combination,
when you expect there to be only a single record for that combination.
If you cannot deduce it, all I can suggest is eliminating tables from
the join one-at-a-time until the problem goes away. That will give you
the culprit. The solution is to create a saved query that returns only a
single record for each combination from that table, either a grouping
query or a "select distinct", and then substitute that saved query in
your sql below for the culprit table. (saved queries are identical to
tables as far as the database engine is concerned, so you can use one in
place of a table).


Alex said:
Bob,

I have included the SQL statement for your review.

Thanks

SELECT ProjectInformaiton.ProjectNumber, PanelHardware.PieceMark,
Sum(((([SteelCostPerFt]*([SteelLength_Inches]/12))*([SteelQuantity]))*[Q
tyNeeded]))
AS CostPerSteel, Sum(([MiscCost]*[MiscQuantity])*[QtyNeeded]) AS
CostPerMisc,
Sum((([RebarCostPerFt]*([RebarLength_Inches]/12))*([RebarQuantity]))*[Qt
yNeeded])
AS CostPerRebar,
Nz([CostPerSteel])+Nz([CostPerMisc])+Nz([CostPerRebar]) AS TotalCost
FROM Stud_Misc_Hardware RIGHT JOIN ((SteelInventory LEFT JOIN
SteelInventoryQuery ON SteelInventory.SteelID =
SteelInventoryQuery.SteelID) RIGHT JOIN ((Rebar LEFT JOIN RebarQuery
ON Rebar.RebarID = RebarQuery.RebarID) RIGHT JOIN (ProjectInformaiton
INNER JOIN (((PanelHardware LEFT JOIN RebarBreakdown ON
PanelHardware.PieceMark = RebarBreakdown.PieceMarkID) LEFT JOIN
SteelHardwareBreakdown ON PanelHardware.PieceMark =
SteelHardwareBreakdown.PieceMarkID) LEFT JOIN
Stud_Misc_HardwareBreakdown ON PanelHardware.PieceMark =
Stud_Misc_HardwareBreakdown.PieceMarkID) ON
ProjectInformaiton.ProjectNumber = PanelHardware.ProjectNumber) ON
Rebar.RebarID = RebarBreakdown.RebarID) ON SteelInventory.SteelID =
SteelHardwareBreakdown.SteelID) ON Stud_Misc_Hardware.MiscID =
Stud_Misc_HardwareBreakdown.MiscID GROUP BY
ProjectInformaiton.ProjectNumber, PanelHardware.PieceMark
HAVING (((ProjectInformaiton.ProjectNumber)=[Enter Project Number]));




Bob Barrows wrote:

legacy wrote:ProbablyShow us the sql statement (switch your query to
SQL View) 22-Apr-10

legacy wrote:

Probably
Show us the sql statement (switch your query to SQL View) and we might
be able to offer a solution.
Click to expand...
 
Back
Top