Columns

[D B Lawson]

This should be easy. I want my report to look like:
Mon Tue Wed Thur Fri
Client Name Name
Client Name
Client Name Name

If I put in a date range, ie Monday to Friday of current week. I want to show which operatives (name) are scheduled to see which clients. Can I get my report to look like this? All I can get at the moment is

Monday
Client 1 Operative
Tuesday
Client 1 Operative
Wednesday
Client 2 Operative
Thursday
Client 2 Operative
Client 3 Operative
Friday
Client 3 Operative

Any ideas or examples?

Thanks

 

[Rich]

Either a crosstab or multiple Iif query/report.

 

[D B Lawson]

The format example didn't post well! I've tried making a crosstab query (for the first time) but I don't want to sum anything so I can't seem to get it to work.

 

[Carol]

You need to set up 2 Crosstab queries to make this work and then use both results in a Select Query to get the format you want. In both of your Crosstab queries, set up the Date field as the Column Heading. Make sure that in the properties for the query, you specify the Column Heading Names, such as Monday, Tuesday, Wednesday, etc.
Then on one query, you will set the Client Name as the Value, with the rest of the fields being rows. On the other Crosstab Query, you will set the Name as the Value with the rest of the fields being rows. Once these two queries are made, then make an additional select Query with a join using the first selection.

I am assuming of course that your date field is set up formatted with the days of the week showing. If not, and it is truly a date/time field, then you would have to create an additional field in a blank column in your query to format out the date fields to weekday.

You should then have the layout that you require. Post back if you have trouble.

Good luck

 

[D B Lawson]

Thanks Carol. I'll let you know....

 

[D B Lawson]

Hi Carol

Despite your excellent description, I'm still having problems. Firstly, the value field is asking for an aggregate function but I'm not sure what to put in as I'm not actually wanting to either count or sum the value. Maybe that doesn't matter at this stage? Also, my date field is a date/time format and although I understand what you mean about creating a blank column in the query, I don't know how to format the date fields to weekdays. Can you help me any further?

Thanks

Dawn

 

[Carol]

Dawn:

In your value field, put in the "Where" clause. In another blank column, include the same field, put in Group By - Row Heading. This will solve your first problem.

The next problem is making your date to turn out the weekdays instead of a full date. In another blank column you are going to make a field. Put the following in:
WDate: DatePart("w",[MyDateField])
This will break down your days of the week to
Sunday = 1, Monday = 2, etc.

 

[D B Lawson]

Hi Carol

My crosstab queries and select query are now working beautifully and giving me the results I had expected. Still having trouble with converting my short date to weekdays. Tried the code in the select query and but it is creating a circular reference and the query won't run. Replaced the WDate field with the code you gave me in the crosstab queries and they give me numbers as the column headings (I take it to be the number of the day of the week). After I did that, I got strange results in the select query. Sorry to be so thick about this - your explanations are so good but I still haven't got it!

Thanks
Dawn

 

[Carol]

Dawn:

You should get numbers as the result. These numbers represent the day of the week. In your report, your labels can be labelled the actual day.

I have tried the query and my results are working out. Without going over the whole practice again, would you mind sending me a small database showing these queries so that I can see how you have it set up.