how would i get this to work? 1

[Onslaughtdave]

Hi,

I am tring to get this query to work but have lost all hope

there is a yes/no field in one of my tables and im tring to get a count of true and a count of false so i can graph the results. However, im getting quite the mess!

I got it somewhat working use this query/sql statement:
TRANSFORM Count([ANum]) AS [CountOfANum] SELECT [year] FROM [First Aid analysis per year] GROUP BY [year] PIVOT [WSIB];

how would i label the values so the legend doesnt say -1 and 0 for the WSIB part (although i know what that means...others wont ;D)

---

on a related note! is there a way to get a count of ALL values of the yes/no but also have a count of the yes values?

ie - that would give me a TOTAL of all records (yes and no values) but also have a field beside it with a count of all the yes records only?

thanks!

 

[SuicidED]

Hi

change the table and field names to yours, this will give total and the yes/no split
SELECT Count(Table1.field) AS CountOffield, Sum(IIf([yes/no field]=-1,1,0)) AS yes, Sum(IIf([yes/no field]=0,1,0)) AS no
FROM Table1;

 

[Onslaughtdave]

SuicidED,

That works great! however, how would i get it to do that for every year?

eg)

|
|
count(anum) |
|
|__________
year

right now it only says one year.

thanks for the help!

 

[SuicidED]

Hi

add to the end

GROUP BY Table1.year;

 

[Onslaughtdave]

SuicidED,

Again, works great! however, i forgot to mention that my date field is the entire data...my mistake!

is there a way to only capture the year out of the data?

thanks for all your help!!!

 

[SuicidED]

As long as the year is in the same area of each record, this should work
GROUP BY Mid([year],4,4)
the first number being the start character of the year and the second how many characters make up the year.

 

[Onslaughtdave]

well the date is in mm/dd/year

but the data is like 1/15/2004 - would it still be 4 or in this case 6? does it count /'s

Is there a right funciton instead of mid? like maybe right([table.date], 4)?

 

[SuicidED]

Yep there's a right so that should work

 

[Onslaughtdave]

ok, i did remember something from first semester

sorry about all the questions ... but now it is displaying for ex 144...

where does it get 144 lol

1994 is 144?
1995 is 182?
...
hmmm

is it converting the date to something else? like in seconds or whatever...

thanks

 

[SuicidED]

Its no good

show some examples of your records I might be able to figure it out....this better not be homework

 

[Onslaughtdave]

no its not homework lol no need to worry

im at a coop placement(work placement) and havent used access in a bit lol

and i figued that its not displaying the year on the bottom.

so your code does work lol i wasnt understanding what my graph was telling me.

how would i get it to display the year on the bottom?

 

[PHV]

Something like this ?
SELECT Year([date field]) AS [Year], Sum(IIf([yes/no field]=-1,1,0)) AS CountOfYes, Sum(IIf([yes/no field]=0,1,0)) AS CountOfNo
FROM yourTable
GROUP BY Year([date field]);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SuicidED]

Hi sorry about that
try this as the row source of your chart
SELECT [test],Sum([CountOfpolicy]) AS [SumOfCountOfpolicy],Sum([yes]) AS [SumOfyes],Sum([no]) AS [SumOfno] FROM [Query2] GROUP BY [test];

query 2 being the earlier stuff

 

[Onslaughtdave]

Hey!

That worked like a charm! I put ur posts together and i got results LOL

I used your(SuicidED) sql from above with his(PHV) year part and it worked like clockwork!

my thanks to both of you!

who would have though that sql class and access would come in handy when i started to work heh!

 

[SuicidED]

Excellent, pubs open in London now, I'm off. Enjoy yourself.
Eddie

 

[Onslaughtdave]

Cheers