All Dates in a crosstab

[Guest]

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.

 

[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

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.

 

[Guest]

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

I am creating a crosstab query to display a count of how many jobs eac

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

 

[Duane Hookom]

I missed your description of which were rows and columns ;-)
You could probably create a single table with a single numeric column [Num]
and values 1 - 30. If you then create a query based on this table and just
add [Num] to Date(), you will have a table of the next 30 days. You can join
this query with your crosstab and use a join that selects all records from
your new query.

--
Duane Hookom
MS Access MVP

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 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

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. there any
way I can do this? build up
a table of dates each time the query is displayed.