Crosstab Queries with criteria

[aromano789]

New to this site. very impressing

I am trying to create a crosstab query, but can get it right.

Table1 = input Table
_____________________________________________________________
name StartDate date Present
A 1201 1201 P
B 1202 1205 P
C 1203 1206 P
D 1204 1207 P
E 1206 1209 P


Can get this result with Crosstab Query;

TRANSFORM NZ(First([Table1].[Present]),"a") AS FirstOfPresent
SELECT Table1.name, Table1.StartDate
FROM Table1
GROUP BY Table1.name, Table1.StartDate
PIVOT Table1.date;

______________________________________________________________
name StartDate 1201 1205 1206 1207 1209
A 1201 P a a a a
B 1202 a P a a a
C 1203 a a P a a
D 1204 a a a P a
E 1206 a a a a P


But am looking to add NA for StartDate > Header Date(1201,1205,1206,1207,1209). See below. Does anyone have a solution or an alternate method


Looking to get this type of results
______________________________________________________________
name StartDate 1201 1205 1206 1207 1209
A 1201 P a a a a
B 1202 NA P a a a
C 1203 NA a P a a
D 1204 NA a a P a
E 1206 NA NA a a P

A=Absent
P=Present
NA=Non Applicable

Thanks Tony

 

[stopher]

Hi Tony

Is your input table ever going to contain anything other than P for present? What I mean is, if there is no entry for a person on a date, can one assume they were absent?

Anyway, i've attached a d/b taking a different approach. It uses a cartesian join to generate the full list of people v calendar dates. Then it's easy to determine the P/A/NA with a formula and then apply the transform. I've done it as two queries for clarity but you could do it as one.

A couple of other points...

• You should really store dates as data type "date" and use Format to display them how you want.
• Don't use Date as the name of a field. Date is a reserved word. It will cause you problems at some stage.

hth
Chris

 

[aromano789]

Thanks Chris

That was very helpful