Crosstab query to generate a Report

[Never Hide]

Hello everyone,

I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.

At this point I can generate a report for any given day.

This could be an example of what I want to achieve

------------ Date | Date+1 | Date+2 | Date+3
New Sheets   2       1       0       2
New Towels   1       3       0       1
Full Clean   0       1       2       0

"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)

I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.

Any suggestions/thoughts on the subjuect are greatly appreciated

P.S. : I wasn't sure in which sub-forum I should post this, so I chose the general feel free to move the thread to the appropriate one if you want

 

[Trevor G]

Can you upload a sample of your database to indicate how you have things laid out, you will get a better response and possible solution.

 

[Never Hide]

Hello Trevor, thnx for your reply

There's a little problem though.
Let me explain the situation.
The customer that we're developing this Reports for is using an ERP program to run his Hotel. Everything works ok with that prgramm exept for the "Cleaning Reports" (don't know how else to say it. The reports that let him know which rooms need to be cleaned)
So we are using Access to create those reports for him,based on the SQL database that the ERP programm uses. Now the tables name and fields, out of contex, make absolutely no sense and it would take for ever to make a copy with understandable names.

 

[Trevor G]

Ok I understand, how about pushing the data into a CSV file and perhaps use Excel to load the data and use the Transpose Command in Paste Special to see if that provides and acceptable report solution, you could use code to generate it and then place it back into a temp table in access and use that as the reporting source. Or leave it in Excel and some nice formatting.

 

[Never Hide]

Thank you very much, I'll give it a try