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:
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
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:
Code:
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