Crosstab Issue (Access 2002)

[RNCIENG]

I am currently running a report that provides me the the details from a time table. (I have attached a spreadsheet to show the format I need).

How can I accomplish this in Access?

I've explored the crosstab query, but it doesn't seem to help. I can only get three columns of data and it the options that are listed isn't what I'm looking for.

Thanks.

[WendellB]

You may be able to get a crosstab query to come close to your format, but it wants to do totals in its own fashion, and missing data will give you a fit. We usually solve this kind of problem by creating a temporary table that looks like your desired format. In your case you will probably need code to deal with situations where you don't have a record for a day, or you have only partial records - i.e. somebody starts at 7:00AM and forgets to check out at lunch, or they leave for an appointment and don't return till the next day. Some challenges to think about.

[wemeier]

You CAN accomplish this with a crosstab table if you are willing to have four records per person -- in, lunch out, lunch in, out. You can later group these by person to create the report. To accomplish this, do the following:

First, create a query against the time table that includes the following fields:
<UL><LI>person's name
<LI>day of week ("Monday", "Tuesday", etc.)
<LI>time element ("In", "Lunch Out", "Lunch In", "Out")
<LI>time element order (1=In, 2=Lunch Out, 3=Lunch In, 4=Out)
<LI>time[/list]Next, create a crosstab query based on the above query. Group by Person, Time Element Order and Day of Week. Make Person, Time Element Order and Time Element the Row Headings, Day of Week the Column Heading and First(time) the Value.

Once you have created the query, change the Column Headings property of the query to use the following headings:
"Monday";"Tuesday";"Wednesday";"Thursday";"Fri day"

This not only ensures that the columns will be in that order, but also ensures that the columns will always appear, even if there is no time for a given day (i.e. a day off).

You can then use this query as the RecordSource of a report. In Sorting and Grouping, group by person and sort by time element number. Don't print the time element number, just the time element text.