Crosstab Query

[arrice]

In Access 2007, I need to create a Crosstab Query to show all of the dates (by day)for an extended period as columns (to be filtered by user), the tasks to be performed as rows (from table), and the people performing the tasks (from table) in the row/columns. Where the tasks require more than one day to perform, the names should be repeated as long as the task requires.

The grid should look like:

DATE1 DATE2 DATE3 DATE4 DATE5
PREP Joe
CLEAN Bill Bill
FILL Joe Pete Pete

Am familiar with VBA and most aspects of Access, but have never had the need for crosstabs before. This can't be as hard as I'm making it.

Thanks in advance.

 

[lameid]

First start by showing all values to be used in a crosstab. I recommend a table that has all days that you OUTER Join from to your second table. Use the criteria to limit the days on the days table.

Then crosstab...

Just like there is the drop down in the query designer to change to action query there is one for Crosstab queries... This adds a line like the grouping does.

Make the day form the days table the column heading, the field showing Prep, clean etc. a row heading and the person name as value. You can add as many row headings as you want but only one column heading and value. Also value MUST use an aggregate funtion which is fine if you have exactly one person per task otherwise you would have to swap the task and the name. For the name just use First, last, min or max as the aggregate funtion (ideally first or last because they are faster in Access; assuming you do not intend to migrate the query to another database product later as few others <if any> support first and last).

 

[Darrylles]

Hi arrice,

You're making it even more difficult for us because you haven't even stated a problem.

It can't be that you don't know how to create a cross-tab, 'cos there's a crosstab query wizard just for this.

ATB ;-)

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.