Summing in query

[stephendeloach via AccessMonster.com]

I have re-created a db and cant get the queries to work right.. My tables are
Main Ven and Description. In the before db I had a query that has this
sql statement..

SELECT Main.Rig, Main.LeaseName, Descriptions.[1Category], Sum(Descriptions.
[1Quantity]*Descriptions.[1UnitPrice]) AS total_value
FROM Main INNER JOIN Descriptions ON Main.Invoice = Descriptions.[1Invoice]
GROUP BY Main.Rig, Main.LeaseName, Descriptions.[1Category];

this works great. now that i have made the new db i cant get it to work so
great. This is my new sql statement with out the total_value

SELECT Main.Rig, Main.LN, Description.Categ
FROM (Main INNER JOIN Ven ON Main.MainId = Ven.MainID) INNER JOIN Description
ON Ven.VenID = Description.VenID;

i need to add a total_value that is Description.[Qty]*Description.[UP]
when I add Total_Value: Sum(Description.[Qty]*Description[UP]) into the query
and run it i get "You tried to execute a query that does not include the
specified expression "Rig" as part of an aggregate function"??

 

[Guest]

Try this --
SELECT Main.Rig, Main.LN, Description.Categ,
Sum([Description].[Qty]*([Description].[UP])) AS Total_Value
FROM (Main INNER JOIN Ven ON Main.MainId = Ven.MainID) INNER JOIN
Description ON Ven.VenID = Description.VenID
GROUP BY Main.Rig, Main.LN, Description.Categ;

 

[stephendeloach via AccessMonster.com]

Your the smartest person alive... It works. Thanks

Try this --
SELECT Main.Rig, Main.LN, Description.Categ,
Sum([Description].[Qty]*([Description].[UP])) AS Total_Value
FROM (Main INNER JOIN Ven ON Main.MainId = Ven.MainID) INNER JOIN
Description ON Ven.VenID = Description.VenID
GROUP BY Main.Rig, Main.LN, Description.Categ;

I have re-created a db and cant get the queries to work right.. My tables are
Main Ven and Description. In the before db I had a query that has this

[quoted text clipped - 16 lines]

and run it i get "You tried to execute a query that does not include the
specified expression "Rig" as part of an aggregate function"??

 

[Guest]

There are a few folks that would disagree with your opinion.
--
KARL DEWEY
Build a little - Test a little

Your the smartest person alive... It works. Thanks

Try this --
SELECT Main.Rig, Main.LN, Description.Categ,
Sum([Description].[Qty]*([Description].[UP])) AS Total_Value
FROM (Main INNER JOIN Ven ON Main.MainId = Ven.MainID) INNER JOIN
Description ON Ven.VenID = Description.VenID
GROUP BY Main.Rig, Main.LN, Description.Categ;

I have re-created a db and cant get the queries to work right.. My tables are
Main Ven and Description. In the before db I had a query that has this

[quoted text clipped - 16 lines]

and run it i get "You tried to execute a query that does not include the
specified expression "Rig" as part of an aggregate function"??