SubQuery in Select who need value of MainQuery

V

Vic

Hello,
I use Access 2000 and have a table Products (tProd), with the fields Code,
Name, Category and Turnover. For this table i have the following query.

Select tP.Code, tP.Name, tP.TurnOver, ROUND(([tP].[TurnOver] / (Select
SUM(tPa.TurnOver) From tProd tPa) * 100),2) AS Perc_Total From tProd tP

This query works fine and give me the percent TurnOver to Total TurnOver
(for all products) for each product.
Now i want to go to a next step. I need also the percent TurnOver to total /
category. For that i try the following

Select tP.Code, tP.Name, tP.TurnOver, tP.Category, ROUND(([tP].[TurnOver] /
(Select SUM(tPa.TurnOver) From tProd tPa) * 100),2) AS Perc_Total,
ROUND(([tP].[TurnOver] / (Select SUM(tPb.TurnOver) From tProd tPb Where
tPb.Category = tP.Categorie) * 100),2) AS Perc_Category
From tProd tP

This query isn't work, when i try to run it a popup window ask me the value
for tP.Categorie. How to change it?

Beste Regards, Vic
 
J

Jason Lepack

Select tP.Code, tP.Name, tP.TurnOver, tP.Category, ROUND(([tP].[TurnOver] /
(Select SUM(tPa.TurnOver) From tProd tPa) * 100),2) AS Perc_Total,
ROUND(([tP].[TurnOver] / (Select SUM(tPb.TurnOver) From tProd tPb Where
tPb.Category = tP.Categorie) * 100),2) AS Perc_Category
From tProd tP
Click to expand...

In the 4th line change "Categorie" to "Category" and you should be
fine.


Hello,
I use Access 2000 and have a table Products (tProd), with the fields Code,
Name, Category and Turnover. For this table i have the following query.

Select tP.Code, tP.Name, tP.TurnOver, ROUND(([tP].[TurnOver] / (Select
SUM(tPa.TurnOver) From tProd tPa) * 100),2) AS Perc_Total From tProd tP

This query works fine and give me the percent TurnOver to Total TurnOver
(for all products) for each product.
Now i want to go to a next step. I need also the percent TurnOver to total /
category. For that i try the following

Select tP.Code, tP.Name, tP.TurnOver, tP.Category, ROUND(([tP].[TurnOver] /
(Select SUM(tPa.TurnOver) From tProd tPa) * 100),2) AS Perc_Total,
ROUND(([tP].[TurnOver] / (Select SUM(tPb.TurnOver) From tProd tPb Where
tPb.Category = tP.Categorie) * 100),2) AS Perc_Category
From tProd tP

This query isn't work, when i try to run it a popup window ask me the value
for tP.Categorie. How to change it?

Beste Regards, Vic
Click to expand...
 
J

John Spencer

You refer to Category in one place and Categorie in another. So, fix the
spelling

Select tP.Code
, tP.Name
, tP.TurnOver
, tP.Category
, ROUND(([tP].[TurnOver] /
(Select SUM(tPa.TurnOver) From tProd tPa) * 100),2) AS Perc_Total
, ROUND(([tP].[TurnOver] /
(Select SUM(tPb.TurnOver) From tProd tPb
Where tPb.Category = tP.Category) * 100),2) AS Perc_Category
From tProd tP

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top