grouping field to calcuate percentage

[ngweixiong]

Hi,

i want to create a Ms Access query such that it will auto calculate its percentage out.

For example,
i have Field: (lead time between order date and invoice date). i want to calculate how many of them fall within 0-7 days, 7-14 days and >14 days. in percentage.

please help.

[NeoPa]

Assuming fields in your table of [OrderDate] & [InvoiceDate] you can firstly have a query that includes fields that show which group they fall under :

SELECT [OrderDate],
       [InvoiceDate],
       IIf(DateAdd('d',7,[OrderDate])>[InvoiceDate],
           '1 Week',
           IIf(DateAdd('d',14,[OrderDate])>[InvoiceDate]),
               '2 Weeks',
               '>2 Weeks')) AS [DaysTaken]
FROM [YourTable]

[ngweixiong]

Assuming fields in your table of [OrderDate] & [InvoiceDate] you can firstly have a query that includes fields that show which group they fall under :

SELECT [OrderDate],
       [InvoiceDate],
       IIf(DateAdd('d',7,[OrderDate])>[InvoiceDate],
           '1 Week',
           IIf(DateAdd('d',14,[OrderDate])>[InvoiceDate]),
               '2 Weeks',
               '>2 Weeks')) AS [DaysTaken]
FROM [YourTable]

Hi,

thx for your reply,
i already have a query with field that calulate the lead time.
So, how do i have another query which show me the percentage which shows lead time
1) less than 7 days
2) 7-14 days
3) more than 7 days?

[NeoPa]

How about you post the SQL of the query that you would like me to work from.