Can I use a Report expression for queries?

  • Thread starter Thread starter Robbie Doo
  • Start date Start date
R

Robbie Doo

Is it possible to use the following expression that is used in reports on
queries?

=[Sales]/Sum([Sales])*100

Or some expression to get the result.
Thank you.
 
It is possible to do something like this in a query. It would require a
sub-query to do so.

SELECT Sales/
(SELECT Sum(Sales)
FROM YourTable as Tmp) *100 as PercentCalc
FROM YourTable

Of course that works over the entire table and you are probably more
interested in doing this for specific sets of data. For instance for the
year 2007 and by store

SELECT YourTable.StoreId
, Sales/
(SELECT Sum(Sales)
FROM YourTable as Tmp
WHERE Tmp.SalesDate Between #1/1/2007# and #12/31/2007#
AND Tmp.StoreID = YourTable.StoreID) *100 as PercentCalc
FROM YourTable
WHERE SalesDate Between #1/1/2007# and #12/31/2007#

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you guys both for your responses. The reason why I was asking this was
to apply the result to a Chart that I would like to create. Unless you have
another suggestion for charts because I can't get no correct results anything
I try.

John Spencer said:
It is possible to do something like this in a query. It would require a
sub-query to do so.

SELECT Sales/
(SELECT Sum(Sales)
FROM YourTable as Tmp) *100 as PercentCalc
FROM YourTable

Of course that works over the entire table and you are probably more
interested in doing this for specific sets of data. For instance for the
year 2007 and by store

SELECT YourTable.StoreId
, Sales/
(SELECT Sum(Sales)
FROM YourTable as Tmp
WHERE Tmp.SalesDate Between #1/1/2007# and #12/31/2007#
AND Tmp.StoreID = YourTable.StoreID) *100 as PercentCalc
FROM YourTable
WHERE SalesDate Between #1/1/2007# and #12/31/2007#

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

Robbie Doo said:
Is it possible to use the following expression that is used in reports on
queries?

=[Sales]/Sum([Sales])*100

Or some expression to get the result.
Thank you.
Click to expand...
Click to expand...
 
Back
Top