Percentage Yes per Day Query

G

Guest

Hi,

Does anyone have any ideas regarding the above.

I have 2 fields in my table that I need to run a query against.
The first is a Date field and the second is a Processed field.
The Processed field is a Yes/No field which will contain Y or N.

What I would like the query to do is to give me the percentage of Y
for each day in the Date field.

I tried using a crosstab query to no avail, but if anyone knows how
to do this, I'd be really grateful

Ta.

Neil
 
G

GPO

Hi,

I mocked up some raw data that looks like this (02 = Feb):
MyDate IsProcessed
1/02/2005 Yes
2/02/2005 Yes
3/02/2005 Yes
4/02/2005 No
5/02/2005 No
6/02/2005 No
7/02/2005 No
8/02/2005 No
9/02/2005 No
10/02/2005 Yes
11/02/2005 Yes
....so that you can test the assumptions I've made about your data.
You might want to do it with two queries. I think you were on the right
track with the crosstab.

Your first query (saved as Q1 originally enough) might then look like:
TRANSFORM nz(Count(*),0) AS [Processed=Yes]
SELECT Weekday([MyDate],1) AS [Weekday(Sun=1)]
FROM MyTable
GROUP BY Weekday([MyDate],1)
PIVOT IIf([IsProcessed]=True,"Processed","Not Processed");

Your second will look at Q1 and might look like:
SELECT Weekday(Sun=1)], (CInt([Processed])/(CInt([Processed])+CInt([Not
Processed]))) AS [Percentage Processed]
FROM Q1;

Final output:

Weekday(Sun=1) Percentage Processed
1 0.00%
2 0.00%
3 50.00%
4 50.00%
5 100.00%
6 50.00%
7 0.00%

The field properties for [Percentage Processed] shout be set to "percent"

GPO
 
G

Guest

Ah ha,

Thanks for the reply.

That's close, but I do apologise, I had a slight wording discrepancy
there. When I said Number Of Processed per Day, I think what is more
appropriate is Number Of Processed per Date. Which I think may make
it slightly easier as it doesn't involve the date conversion parts.

So a mock up of my data would look something like.....

MyDate IsProcessed
01/02/2005 Yes
01/02/2005 Yes
01/02/2005 No
02/02/2005 Yes
02/02/2005 No
02/02/2005 No
02/02/2005 No
03/02/2005 Yes
03/02/2005 Yes
03/02/2005 No
04/02/2005 No
04/02/2005 Yes
04/02/2005 Yes
05/02/2005 No
05/02/2005 Yes
06/02/2005 No
06/02/2005 No
06/02/2005 Yes
07/02/2005 No
07/02/2005 Yes
08/02/2005 No
08/02/2005 Yes
08/02/2005 Yes
09/02/2005 No
09/02/2005 Yes
10/02/2005 Yes
10/02/2005 Yes
10/02/2005 No
11/02/2005 Yes
11/02/2005 No
11/02/2005 No

Does this change it at all?

Thanks again.

Neil


GPO said:
Hi,

I mocked up some raw data that looks like this (02 = Feb):
MyDate IsProcessed
1/02/2005 Yes
2/02/2005 Yes
3/02/2005 Yes
4/02/2005 No
5/02/2005 No
6/02/2005 No
7/02/2005 No
8/02/2005 No
9/02/2005 No
10/02/2005 Yes
11/02/2005 Yes
....so that you can test the assumptions I've made about your data.
You might want to do it with two queries. I think you were on the right
track with the crosstab.

Your first query (saved as Q1 originally enough) might then look like:
TRANSFORM nz(Count(*),0) AS [Processed=Yes]
SELECT Weekday([MyDate],1) AS [Weekday(Sun=1)]
FROM MyTable
GROUP BY Weekday([MyDate],1)
PIVOT IIf([IsProcessed]=True,"Processed","Not Processed");

Your second will look at Q1 and might look like:
SELECT Weekday(Sun=1)], (CInt([Processed])/(CInt([Processed])+CInt([Not
Processed]))) AS [Percentage Processed]
FROM Q1;

Final output:

Weekday(Sun=1) Percentage Processed
1 0.00%
2 0.00%
3 50.00%
4 50.00%
5 100.00%
6 50.00%
7 0.00%

The field properties for [Percentage Processed] shout be set to "percent"

GPO


Neily said:
Hi,

Does anyone have any ideas regarding the above.

I have 2 fields in my table that I need to run a query against.
The first is a Date field and the second is a Processed field.
The Processed field is a Yes/No field which will contain Y or N.

What I would like the query to do is to give me the percentage of Y
for each day in the Date field.

I tried using a crosstab query to no avail, but if anyone knows how
to do this, I'd be really grateful

Ta.

Neil
Click to expand...
Click to expand...
 
G

Guest

Okey Dokey,

Managed it. It just didn't occur to me to use 2 queries.

Query1 named qryCrossTab

TRANSFORM Count(*) AS Expr1
SELECT MyTable.MyDate
FROM MyTable
GROUP BY MyTable.MyDate
PIVOT MyTable.IsProcessed;

Query 2

SELECT qryCrossTab.MyDate, ([Yes]/([Yes]+[No])) AS [%Yes]
FROM qryCrossTab;

What I'm trying to do with this is bring these results back into Excel
I was trying to do it all as one query, but what I can do is leave the
1st query on the Database and then Excel runs a query against that
query in the database.

Anyway, cheers for the help.

Neil


Neily said:
Ah ha,

Thanks for the reply.

That's close, but I do apologise, I had a slight wording discrepancy
there. When I said Number Of Processed per Day, I think what is more
appropriate is Number Of Processed per Date. Which I think may make
it slightly easier as it doesn't involve the date conversion parts.

So a mock up of my data would look something like.....

MyDate IsProcessed
01/02/2005 Yes
01/02/2005 Yes
01/02/2005 No
02/02/2005 Yes
02/02/2005 No
02/02/2005 No
02/02/2005 No
03/02/2005 Yes
03/02/2005 Yes
03/02/2005 No
04/02/2005 No
04/02/2005 Yes
04/02/2005 Yes
05/02/2005 No
05/02/2005 Yes
06/02/2005 No
06/02/2005 No
06/02/2005 Yes
07/02/2005 No
07/02/2005 Yes
08/02/2005 No
08/02/2005 Yes
08/02/2005 Yes
09/02/2005 No
09/02/2005 Yes
10/02/2005 Yes
10/02/2005 Yes
10/02/2005 No
11/02/2005 Yes
11/02/2005 No
11/02/2005 No

Does this change it at all?

Thanks again.

Neil


GPO said:
Hi,

I mocked up some raw data that looks like this (02 = Feb):
MyDate IsProcessed
1/02/2005 Yes
2/02/2005 Yes
3/02/2005 Yes
4/02/2005 No
5/02/2005 No
6/02/2005 No
7/02/2005 No
8/02/2005 No
9/02/2005 No
10/02/2005 Yes
11/02/2005 Yes
....so that you can test the assumptions I've made about your data.
You might want to do it with two queries. I think you were on the right
track with the crosstab.

Your first query (saved as Q1 originally enough) might then look like:
TRANSFORM nz(Count(*),0) AS [Processed=Yes]
SELECT Weekday([MyDate],1) AS [Weekday(Sun=1)]
FROM MyTable
GROUP BY Weekday([MyDate],1)
PIVOT IIf([IsProcessed]=True,"Processed","Not Processed");

Your second will look at Q1 and might look like:
SELECT Weekday(Sun=1)], (CInt([Processed])/(CInt([Processed])+CInt([Not
Processed]))) AS [Percentage Processed]
FROM Q1;

Final output:

Weekday(Sun=1) Percentage Processed
1 0.00%
2 0.00%
3 50.00%
4 50.00%
5 100.00%
6 50.00%
7 0.00%

The field properties for [Percentage Processed] shout be set to "percent"

GPO


Neily said:
Hi,

Does anyone have any ideas regarding the above.

I have 2 fields in my table that I need to run a query against.
The first is a Date field and the second is a Processed field.
The Processed field is a Yes/No field which will contain Y or N.

What I would like the query to do is to give me the percentage of Y
for each day in the Date field.

I tried using a crosstab query to no avail, but if anyone knows how
to do this, I'd be really grateful

Ta.

Neil
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculating a percentage 1
Not "Y"??? 1
Finding the Percentage of 'Yes' 2
DCount function for filtered data in table 7
Cross tab query solution 4
Need help with count query with criteria - Stumped! 2
Help needed with IIF in my query 14
Overflow error on calculate field in query - Help Please 1

Top