Query Efficacy

[Mina Garas]

Hi Everybody


I'm not expert in sql or vb so that am trying to do all my database by using queries
my database about stores and inventory management

but i face a problem with any query am trying to do the query doesn't do more than one issue.

example :

i create a query that sum all purchased items by quantity and price like that :

ItemCode groupby
ItemDesc groupby
Qty Sum
Price Sum

now i got the sum of items by qty and price
when i try to creat new column called total to calculate qty * price
and sum this column also

when i run the query i got this message " subqueries cannot be used in the exppression (sum([qty]*[price]))

and to do this issue i create new query based on last query to calculate Total : qty * price

how can i merge both of them in query and that query do sum and multiply in one query

thanks alot.

[ssanfu]

Try:

The last column would be Extended:[qty]*[price]

In the totals row, the first two columns would be "groupby", the last 3 columns would be "Sum"


Column ........TOTALS row
-----------------------
ItemCode ...groupby
ItemDesc ...groupby
Qty ............Sum
Price.......... Sum
Extended:[qty]*[price] .......SUM

[Mina Garas]

ssanfu thank you

i did what u said but nothing change and i got the same message

[ssanfu]

Would you post the SQL of the query?

[Mina Garas]

SELECT [1-0-InQuery].MoYrID, [1-0-InQuery].MoYrNa, [1-0-InQuery].Code, [1-0-InQuery].Origin, [1-0-InQuery].[Drug Name], [1-0-InQuery].Substance, [1-0-InQuery].Cate, [1-0-InQuery].Company, [1-0-InQuery].Unit, [1-0-InQuery].Size, [1-0-InQuery].Conc, Sum([1-0-InQuery].InQty) AS InQty, Sum([1-0-InQuery].price) AS price
FROM [1-0-InQuery]
GROUP BY [1-0-InQuery].MoYrID, [1-0-InQuery].MoYrNa, [1-0-InQuery].Code, [1-0-InQuery].Origin, [1-0-InQuery].[Drug Name], [1-0-InQuery].Substance, [1-0-InQuery].Cate, [1-0-InQuery].Company, [1-0-InQuery].Unit, [1-0-InQuery].Size, [1-0-InQuery].Conc;

[Mina Garas]

that's it ssanfu

[ssanfu]

"Size" is a reserved word in Access and shouldn't be used for object names. Plus it is not very descriptive. "Size" of what?? Shoe, hat, shirt, ...

Really, really a bad ides to start an object name with a number. Access sometimes chokes when the first char is a number.



This query works for me:

SELECT [1-0-InQuery].MoYrID, [1-0-InQuery].MoYrNa, [1-0-InQuery].Code, [1-0-InQuery].Origin, [1-0-InQuery].[Drug Name], [1-0-InQuery].Substance, [1-0-InQuery].Cate, [1-0-InQuery].Company, [1-0-InQuery].Unit, [1-0-InQuery].[Size], [1-0-InQuery].Conc, Sum([1-0-InQuery].InQty) AS SumOfInQty, Sum([price]*[inqty]) AS Extended
FROM [1-0-InQuery]
GROUP BY [1-0-InQuery].MoYrID, [1-0-InQuery].MoYrNa, [1-0-InQuery].Code, [1-0-InQuery].Origin, [1-0-InQuery].[Drug Name], [1-0-InQuery].Substance, [1-0-InQuery].Cate, [1-0-InQuery].Company, [1-0-InQuery].Unit, [1-0-InQuery].[Size], [1-0-InQuery].Conc;

But you can't display the sum of "price".

Lets say you have two rows:
row 1 : qty = 10 and price = $5 -> extended price = 10 * $5 = $50
row 2 : qty = 5 and price = $15 -> extended price = 5 * $15 = $75
--------------------------------------------------------------------
Sum : qty = 15 and price = $20 -> extended price = $125


The problem is here: if you then try multiplying the sum of the qty (15) and the sum of the price ($20), the total is $300; which is wrong.
So I did not display the price in the query.


Does this help?

[Mina Garas]

yes it does help
you are genius
because u analysis the problem right

and it does mean its impossible make one query to do both of those functions sum and multiply and i am on the right way when make 2 queries

thanks a lot ssanfu

[InsuranceGuy]

you are summing a [price] column and naming it [price]. same thing with a source field called [inQty] being summed and named [inQty] again. that is bad form and causes the database engine to not know which column you're referencing when you try to define [extended price] as [price]*[inQty].

it may work to add table references instead of referring only to the column names.

sum([1-0-InQuery].[price]*[1-0-InQuiry].[inQty]) as [Extended Price]

If not, try not naming your summed columns based on their source field name.

Jeff