crosstab help

[d00ku]

I have a table that holds review information on staff:

tblReviews:
StaffID (Number)
ReviewDate (Date/Time)
Completed (Yes/No)

Everytime a review is carried out it is added as a new record, however, I need the information to be presented in a crosstab so it looks something like this:

StaffID          Review1          Review2           Review3
17             01/01/2006       04/04/2006         05/05/06
20             01/01/2006       04/04/2006         05/05/06
25             01/01/2006       04/04/2006         05/05/06

Other then creating a table and playing with a bit of vba does anyone have a solution?
Thanks.

 

[gromit]

Hi -

See attached for one approach.

I first created a query to duplicate the review date column. Then I ran this through the cross-tab query wizard to produce a query close to what you are looking for. The headers are dates, not "Review 1", "Review 2", etc. But with some more experimentation you can probably generate those labels as well.

There is probably some way to do this all in one query, but I'm not smart enough today!

Note: You can rank the dates of the reviews in a query as well. It took me a little time to recall how to do this, but I answered my own post at
http://www.access-programmers.co.uk/forums/showthread.php?t=104559

- g

 

[d00ku]

Thanks for the reply gromit, however, thats not quite what I am after.
There are thousands of review dates in my real table and for each unique date, the crosstab query generates a column header for it and therefore leaving me with thousands of blank "cells".
I have written a vba function that will take the table and transpose the data into a new table, however, I am sure there must be another way to do this.

Thanks for the reply anyway.

 

[JoeCruse]

How many reviews will you have? If there are just 3, as in your example, or a set number, you can make your column heading in the query, in the Properties section.

 

[d00ku]

There will be a maximum of 18 reviews for each person, however, some people may just have 1 review.

I'll try the column heading feature and get back to you.

Thanks for the reply.