All Dates in a crosstab

N

Nik

I am creating a crosstab query to display a count of how many jobs each staff member has outstanding against a given date. The crosstab works fine, but doesn't show me the dates where nobody has any outstanding jobs

I need to be able to show all dates within the next 30 days. I there any way I can do this

I would prefer to be able to do it without having to run code to build up a table of dates each time the query is displayed.
 
D

Duane Hookom

Are the dates used as column headings? If so, consider this answer I just on
another crosstab question that grouped by weeks rather than days. You can
easily modify the solution for days.
===========================
This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate. Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the query or
report.
===========================

--
Duane Hookom
MS Access MVP


Nik said:
I am creating a crosstab query to display a count of how many jobs each
Click to expand...
staff member has outstanding against a given date. The crosstab works fine,
but doesn't show me the dates where nobody has any outstanding jobs.
I need to be able to show all dates within the next 30 days. I there any way I can do this?

I would prefer to be able to do it without having to run code to build up
Click to expand...
a table of dates each time the query is displayed.
 
N

Nik

The dates are to be used as Row headings

I need to be able to show how many jobs each staff member has allocated to them per day for the next 30 days (including 'blank' dates e.g. weekends & bank holidays

----- Duane Hookom wrote: ----

Are the dates used as column headings? If so, consider this answer I just o
another crosstab question that grouped by weeks rather than days. You ca
easily modify the solution for days
==========================
This is where I would use "relative weeks" rather than "absolute weeks"
Relative weeks would compare your date field to a date entered on a form t
create the columns. For instance, you could use an expression like
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate]
This would create column headings lik
"Wk0", "Wk1", "Wk2", "Wk3",..
Wk0 would contain the values for DivDate in the same week as txtEndDate. Wk
would be 6 weeks earlier. You would need to set the Query|Parameter
[Forms]![frmA]![txtEndDate] Date/Tim
and set the Column Headings property t
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks
This method allows you to create a report that will ALWAYS have the sam
column/fields. It requires no code and no future maintenance of the query o
report
==========================

--
Duane Hooko
MS Access MV


Nik said:
I am creating a crosstab query to display a count of how many jobs eac
Click to expand...
staff member has outstanding against a given date. The crosstab works fine
but doesn't show me the dates where nobody has any outstanding jobsa table of dates each time the query is displayed
 
Top