Merging Data Columns into Rows

[tfaiers]

Hi everyone,

I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:

When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid )

The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:

Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06

The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:

Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'

*Where the blanks are simply left empty, rather than putting in the word 'blank'

Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record

Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.

Regards
Tony

 

[Jon K]

It's much easier to demonstrate with a sample database. In the database I used a form, which, I think, is more user-friendly.

You can open the form, type 16/1/2006 in the text box and click on the command button to run the crosstab query. The code is in the On Click event of the command button.


If you don't like to use a form, you can change all instances of [Forms]![Form1]![txtMonday] in the query "qryA" to [Week Start Date?] and directly run the crosstab query.


Note
For the crosstab query to run without popping up an error, you have to declare the data type of the parameter. See the SQL statement of "qryA".
.

 

[tfaiers]

Absolutely fabulous, I tried playing with the crosstabs queries, and reading the access programmers book but not 100% understanding how to use them. Your example is spot on and has resolved the problem.

Many many thanks.