Problem in Query

[Dave177]

Hi there,
I have got a problem with something had thought would be relatively straight forward.

I have a list of 100 Establishments in [tblEstablishmentCodes] that need to submit data on a weekly basis that goes into [tblRegMon].

However, I am trying to create a query identifying which establishments have or have not submitted data on each of the 52 weeks.

i.e
Week 1 Week 2 Week 3 Week 4 etc
Establishment 1 Yes Yes No Yes
Establishment 2 No Yes Yes Yes
Establishment 3 Yes Yes Yes No
Establishment 4 No No No No

I had thought the following would work:

SELECT DISTINCT tblEstablishmentCodes.Establishment, IIf(([WeekNo])=1,"Y","N") AS 1, IIf(([WeekNo])=2,"Y","N") AS 2, IIf(([WeekNo])=3,"Y","N") AS 3
FROM tblRegMon RIGHT JOIN tblEstablishmentCodes ON tblRegMon.Code = tblEstablishmentCodes.EstabCode
GROUP BY tblEstablishmentCodes.Establishment, IIf(([WeekNo])=1,"Y","N"), IIf(([WeekNo])=2,"Y","N"), IIf(([WeekNo])=3,"Y","N");

But this produces something like:

Week 1 Week 2 Week 3 Week 4 etc
Establishment 1 Yes Yes No Yes
Establishment 1 No Yes No No
Establishment 1 No No No Yes
Establishment 2 No Yes No No
Establishment 2 No No Yes No
Establishment 2 No No No Yes

Really I'm after all of Establishment 1's returns on one line, followed by all of Establishment 2's etc.

Any ideas?

Dave

 

[lespaul]

so you can take your query and convert it to a cross tab query to get the final results that you are looking for. In order to determine if you don't have a specific week you need to have a table that lists all the weeks.

So tblRegMon has something like:
[tt]
EstablishmentID Week {maybe other fields?)
1 1
1 3
1 4
2 1
2 2
2 3
3 1
3 2
3 3
3 4[/tt]
So:
1 is missing 2
2 is missing 4
3 is missing NONE

So if you create a table with a single field:
[tt]
Weekno
1
2
3
4
..
52
[/tt]

then a query (typed not tested, pretty sure Is null syntax is wrong, but this will get you started!):

SELECT r.EstablishmentID, w.Weekno, iif(r.Week is null, "No", "Yes") As Reported
FROM tblRegMon r
Left Join Weeks w on r.week = w.weekno

This should give you something like:
[tt]
Establishmentid WeekNo Reported
1 1 yes
1 2 no
1 3 yes
1 4 yes
2 1 yes
2 2 yes
2 3 yes
2 4 no
3 1 yes
3 2 yes
3 3 yes
3 4 yes[/tt]

now you can convert this to a cross tab query to get the results you want.
HTH





Leslie

Have you met Hardy Heron?

 

[PHV]

As for your initial query:

SELECT tblEstablishmentCodes.Establishment
, Max(IIf(WeekNo=1,"Y","N")) AS 1
, Max(IIf(WeekNo=2,"Y","N")) AS 2
, Max(IIf(WeekNo=3,"Y","N")) AS 3
FROM tblRegMon RIGHT JOIN tblEstablishmentCodes ON tblRegMon.Code = tblEstablishmentCodes.EstabCode
GROUP BY tblEstablishmentCodes.Establishment;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

PHV, the OP says:

However, I am trying to create a query identifying which establishments have or have not submitted data on each of the 52 weeks.

and i'm pretty sure you're solution would require an IIF statement for each of the 52 weeks right? which would probably be too long of a statement....

Leslie