Crosstab column does not appear if no data to display

[RobertIngles]

Hi all;



I have three queries:
1. Select query (from source table) to display 'UserName' 'Project' 'Hours Worked' and the 'Date' the hours were worked. Also linked to a table that indicates if the 'Date' falls into 'Week 1', 'Week 2', 'Week 3' or 'Week 4' of the month.

2. Crosstab of #1 displaying Username and Project as rows, Week 1, 2, 3, and 4 as the coumns and total of hours as the value.

3. Crosstab of #2 that displays a count of the users who have worked hours over the forecasted hours for that month - again I am displaying week 1, 2, 3 etc as column.

Here is my problem - I have created queries based on the month the hours occur. For months after June, because there is no data yet for the date or the number of hours worked my crosstab does not even display the week 1, 2, 3, 4 columns. I am also basing crosstab query 3 on crosstab query 2, if the week column does not show in crosstab 2 because of no date or hours data, my field select box for crosstab 3 does not show week 1, 2, 3 or 4 as fields to add to the query.



'Aug Week 1' shows in this field selection box only because an employee has logged "future" hours for the time they know they will be on vacation.

One solution I thought of was to add dates and zero values to table 1 however this is a data dump from the official DB the users enter their time to so I do not want to be manipulating that. I update it weekly.

Is there any way (other than adding a bunch of bogus data to the source table) to force Access to display the columns? I have used the Nz function in fields before however if there is no data for the date in the source table there is no cell for me to apply the Nz to.

Does any of this make sense?

Thanks in advance for your help - If I am not clear (and I am sure it is confusing) let me know what info you would need to help me out!!

[June7]

Basically, no. There must be records.

If you had a master dataset of all possible username/project/week combinations, that dataset could be included and that would force at least one record for each. Then the columns would always generate but with empty cells if no data.

[RobertIngles]

Basically, no. There must be records.

If you had a master dataset of all possible username/project/week combinations, that dataset could be included and that would force at least one record for each. Then the columns would always generate but with empty cells if no data.

That's going to work I think - Needed to alter the joins to the date/week table (as it has every day in each month to define the weeks) and use the date/week table to be the basis of the columns.

Thanks so much, I really appreciate it!!