SubQuery in Select who need value of MainQuery

  • Thread starter Thread starter Vic
  • Start date Start date
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
 
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...
 
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
..
 
Back
Top