Separing the yes and no to separate columns 2

[dinster]

Hi all,

I have query which counts the number of records on monday, tuesday, wednesday etc depending on whether it is true or false.

e.g

Monday 18 yes
Monday 3 no
Tuesday 9 yes
Tuesday 8 no
etc...

the code for this is

SELECT Format([dateadded],"dddd") AS Days, Count(Sheet4.ReferralAppropriate) AS CountOfReferralAppropriate, Sheet4.ReferralAppropriate
FROM Sheet4
GROUP BY Format([dateadded],"dddd"), Sheet4.ReferralAppropriate;

My question is how do i show the records seperatley on different columns?

e.g

Weekday Yes no

Monday 18 3
Tuesday 9 8
etc

This ones quite difficult to suss out :s

many thanks

 

[HarleyQuinn]

You will be able to achieve the desired result in a crosstab query.

If you want to do this in SQL (outside of the query designer), have a search for TRANSFORM and PIVOT.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

Perhaps:

TRANSFORM Count(Sheet4.ReferralAppropriate) AS CountOfReferralAppropriate
SELECT Format([dateadded],"dddd") AS Days
FROM Sheet4
GROUP BY Format([dateadded],"dddd")
PIVOT Format([ReferralAppropriate],"Yes/No");

 

[HarleyQuinn]

That would do it

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

Sorry HarleyQuinn, I should have refreshed

 

[HarleyQuinn]

No worries mate

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dinster]

I've done it like this eventually....
I never heard of transform....something to read on ...

But thanks guys...

SELECT Format([dateadded],"dddd") AS Days, Sum(IIf([ReferralAppropriate]=Yes,1,0)) AS YesA, Sum(IIf([ReferralAppropriate]=No,1,0)) AS NoA
FROM Sheet4
GROUP BY Format([dateadded],"dddd");

 

[Remou]

HarleyQuinn
That should have been your purple, so ...

 

[HarleyQuinn]

Cheers Remou, worked out well for me in the end...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.