Percentage Yes per Day Query

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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...
 
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

Not "Y"??? 1
calculating a percentage 1
Help needed with IIF in my query 14
DCount function for filtered data in table 7
Counting survey responses, 4 queries or 1 query?? 1
Finding the Percentage of 'Yes' 2
Need help with count query with criteria - Stumped! 2
Cross tab query solution 4

Back
Top