Calculate Percentage

[ngweixiong]

Hi,

I have a Ms Access query which i used to calculate how many times the leadtime is
a) less than 7 days
b) 7-14 days
c) more than 14 days

With the query results, i will like to convert my results to percentage.
E.G.
how many percent is less than 7 days, 7-14 days and more than 14 days.

Thanks.

[MMcCarthy]

Can you post the full SQL of the query you are currently using.

[ngweixiong]

Can you post the full SQL of the query you are currently using.

Hi,

below are my codes.

Thanks alot!!

SELECT [lead Time between PO date and Invoice date].[PO date], [lead Time between PO date and Invoice date].PO, Avg([lead Time between PO date and Invoice date].[TL PO and Inv]) AS [AvgOfTL PO and Inv]
FROM [lead Time between PO date and Invoice date]
GROUP BY [lead Time between PO date and Invoice date].[PO date], [lead Time between PO date and Invoice date].PO;

[MMcCarthy]

Try something like this ...

SELECT "Less than 7 days" As Period, DCount("[TL PO and Inv]","lead Time between PO date and Invoice date") AS TotalCount, Count([TL PO and Inv]) As CountTL, CountTL/TotalCount As PercentTL
FROM [lead Time between PO date and Invoice date]
WHERE [PO date] BETWEEN Date() AND Date()-7
UNION
SELECT "7 - 14 days" As Period, DCount("[TL PO and Inv]","lead Time between PO date and Invoice date") AS TotalCount, Count([TL PO and Inv]) As CountTL, CountTL/TotalCount As PercentTL
FROM [lead Time between PO date and Invoice date]
WHERE [PO date] BETWEEN Date()-7 AND Date()-14
UNION
SELECT "Greater than 14 days" As Period, DCount("[TL PO and Inv]","lead Time between PO date and Invoice date") AS TotalCount, Count([TL PO and Inv]) As CountTL, CountTL/TotalCount As PercentTL
FROM [lead Time between PO date and Invoice date]
WHERE [PO date] < Date()-14;
 

[ngweixiong]

Try something like this ...

SELECT "Less than 7 days" As Period, DCount("[TL PO and Inv]","lead Time between PO date and Invoice date") AS TotalCount, Count([TL PO and Inv]) As CountTL, CountTL/TotalCount As PercentTL
FROM [lead Time between PO date and Invoice date]
WHERE [PO date] BETWEEN Date() AND Date()-7
UNION
SELECT "7 - 14 days" As Period, DCount("[TL PO and Inv]","lead Time between PO date and Invoice date") AS TotalCount, Count([TL PO and Inv]) As CountTL, CountTL/TotalCount As PercentTL
FROM [lead Time between PO date and Invoice date]
WHERE [PO date] BETWEEN Date()-7 AND Date()-14
UNION
SELECT "Greater than 14 days" As Period, DCount("[TL PO and Inv]","lead Time between PO date and Invoice date") AS TotalCount, Count([TL PO and Inv]) As CountTL, CountTL/TotalCount As PercentTL
FROM [lead Time between PO date and Invoice date]
WHERE [PO date] < Date()-14;
 

Hi,

Can kindly explain the code? thanks alot

[MMcCarthy]

This is a union query. Essentially it is three queries which gets the records based on different criteria but returns them as one set of records.

The first column returned is just a string to describe the record being returned. The second is just a way of retrieving the total count of records for use in calculating percentage. The third column is the count of records in this criteria and the fourth calculates the percentage. Then the where statement on each SELECT statement sets the criteria e.g. In the last 7 days would be the first one.

[ngweixiong]

This is a union query. Essentially it is three queries which gets the records based on different criteria but returns them as one set of records.

The first column returned is just a string to describe the record being returned. The second is just a way of retrieving the total count of records for use in calculating percentage. The third column is the count of records in this criteria and the fourth calculates the percentage. Then the where statement on each SELECT statement sets the criteria e.g. In the last 7 days would be the first one.

Hi,

thanks for the code.

I have modify the code and it still does not calaculate the percentage.

my query (Average time lead between PO date and Inv Date) only have the following fields,
PO date
PO
AvgOfTL PO and Inv

SELECT "Less than 7 days" AS Period, DCount("[PO]","Average time lead between PO date and Inv Date") AS TotalCount, Count([AvgOfTL PO and Inv]) AS CountTL, CountTL/TotalCount AS PercentTL
FROM [Average time lead between PO date and Inv Date]
WHERE [PO date] BETWEEN Date() AND Date()-7
UNION
SELECT "7 - 14 days" AS Period, DCount("[PO]","Average time lead between PO date and Inv Date") AS TotalCount, Count([PO]) AS CountTL, CountTL/TotalCount AS PercentTL
FROM [Average time lead between PO date and Inv Date]
WHERE [PO date] BETWEEN Date()-7 AND Date()-14
UNION
SELECT "Greater than 14 days" AS Period, DCount("[PO]","Average time lead between PO date and Inv Date") AS TotalCount, Count([PO]) AS CountTL, CountTL/TotalCount AS PercentTL
FROM [Average time lead between PO date and Inv Date]
WHERE [PO date] < Date()-14;

please help

[ngweixiong]

any one can help???

thanks alot...