Track Travel over time

[m stroup]

tblStaff
StaffID (PK) / StaffName / etc
tblTravel
TripID (PK) / StaffID (FK) / Destination / Depart Dt / Return Dt / etc

I would like to be able to create a query - to go to excel like the following:

1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8 1/9 1/10
StaffID T T T T T T

I want to import the data into excel and have excel color the cells with a T
indicating the person was on travel those dates.

I can get the data into a query, but each trip is on a different line, which
is wonderful for some reports, but not for this query type.

Any suggestions for the query or revamping the table structure?

 

[KARL DEWEY]

Use a crosstab query.

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum trip lenght.
Travel_Days ---
SELECT tblTravel.StaffID, tblTravel.TripID, DateAdd("d",[CountNUM],[Depart
Dt]) AS Traveling
FROM tblTravel, CountNumber
WHERE (((DateAdd("d",[CountNUM],[Depart Dt]))<=[Return Dt]))
ORDER BY tblTravel.StaffID, tblTravel.TripID, DateAdd("d",[CountNUM],[Depart
Dt]);

TRANSFORM First(IIf([Traveling] Is Not Null,"T","")) AS Expr1
SELECT tblStaff.Staffname
FROM tblStaff INNER JOIN Travel_Days ON tblStaff.StaffID = Travel_Days.StaffID
GROUP BY tblStaff.Staffname
PIVOT Format([Traveling],"Short Date");