Report totals by day of week - from crosstab report

[Barbara Waller]

Using Access97 (with Windows XP)

I am trying to create weekly activity report that shows how many calls were
made to each client by day and then give a total of calls for the week. It
would look like this:

Customer
Mon
Tue
Wed
Thur
Fri
Total

Jones
1


3

4

Peach

1
1
1
1
4

ZipIt
1
1
3
2
1
8



My report is based a crosstab query. The crosstab pulls information from a
query that counts the number of visits by date by calendar date i.e.:
1/3/05, 1/4/05, etc.

My problems are:
1. The crosstab won't work if the underlying query has a parameter (I ask
what the date range should be for the report.

2. The crosstab query returns the dates 1/3/05 and not the date of the week
"Monday" even though the underlying query is formatted to show just the day
of the week.

3. The report returns an error if there is no value for one day of the
week.

I tried putting in Column Headers in the Properties dialog box of the
crosstab query ("Monday","Tuesday","Wednesday","Thursday","Friday") but that
returns the error: "Data type mismatch in criteria expression."

I know how to create queries and have some experience with modules in
Access, but I am not an advanced user so please explain carefully.

Thank you.

BW

 

[Fons Ponsioen]

Hi Barbara. The way you could approach this is use your current query but
convert it to a make table query, than use the newly created table as the
source for your crosstab query.
Hope this helps.
Fons

 

[Barbara Waller]

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I want a
report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a select query
set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

 

[PC Datasheet]

Put this expression in a blank field in your query:
DayOfWeek:Format([NameOfYourDateField],"mmm")
This will give you Mon to Fri instead of the dates.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I want a
report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a select query
set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

Hi Barbara. The way you could approach this is use your current query but
convert it to a make table query, than use the newly created table as the
source for your crosstab query.
Hope this helps.
Fons

week.

It

the

week

the

day

but

that

 

[Fons Ponsioen]

Babrbara.
You can use the following expression for the column header:
Format([YourDates],"dddd")
This would give you the day.
The way I would approach this is to create a macro which would run a make
table query, selecting the dates by filtering for week number, you could use:
Week#: Format([YourDates],"ww")
This will return a week number, this way you would set the filter to the
specific week you want.
Hope this gives you some idea.
Fons

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I want a
report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a select query
set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

Hi Barbara. The way you could approach this is use your current query but
convert it to a make table query, than use the newly created table as the
source for your crosstab query.
Hope this helps.
Fons

 

[Duane Hookom]

I would use a solution like the following only "re-purposed" for days rather
than months.
=== solution ================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

 

[Barbara Waller]

This is great! That was the next step after getting daily reports I need to
be able to show the month by week then a report for each month of the year
as you've outlined.

Thanks for the suggestion.

BW

I would use a solution like the following only "re-purposed" for days rather
than months.
=== solution ================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP

Using Access97 (with Windows XP)

I am trying to create weekly activity report that shows how many calls
were
made to each client by day and then give a total of calls for the week.
It
would look like this:

Customer
Mon
Tue
Wed
Thur
Fri
Total

Jones
1


3

4

Peach

1
1
1
1
4

ZipIt
1
1
3
2
1
8



My report is based a crosstab query. The crosstab pulls information from
a
query that counts the number of visits by date by calendar date i.e.:
1/3/05, 1/4/05, etc.

My problems are:
1. The crosstab won't work if the underlying query has a parameter (I ask
what the date range should be for the report.

2. The crosstab query returns the dates 1/3/05 and not the date of the
week
"Monday" even though the underlying query is formatted to show just the
day
of the week.

3. The report returns an error if there is no value for one day of the
week.

I tried putting in Column Headers in the Properties dialog box of the
crosstab query ("Monday","Tuesday","Wednesday","Thursday","Friday") but
that
returns the error: "Data type mismatch in criteria expression."

I know how to create queries and have some experience with modules in
Access, but I am not an advanced user so please explain carefully.

Thank you.

BW

 

[Barbara Waller]

This should work for the weekly report I need to create and Duane gave me a
good idea for the monthly report.

Is there a way to get around the error the report gives you when one day of
the week has not information?

BW

Babrbara.
You can use the following expression for the column header:
Format([YourDates],"dddd")
This would give you the day.
The way I would approach this is to create a macro which would run a make
table query, selecting the dates by filtering for week number, you could use:
Week#: Format([YourDates],"ww")
This will return a week number, this way you would set the filter to the
specific week you want.
Hope this gives you some idea.
Fons

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I want a
report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a select query
set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

Hi Barbara. The way you could approach this is use your current query but
convert it to a make table query, than use the newly created table as the
source for your crosstab query.
Hope this helps.
Fons

:

Using Access97 (with Windows XP)

I am trying to create weekly activity report that shows how many

calls
were

made to each client by day and then give a total of calls for the

week.
It

would look like this:

Customer
Mon
Tue
Wed
Thur
Fri
Total

Jones
1


3

4

Peach

1
1
1
1
4

ZipIt
1
1
3
2
1
8



My report is based a crosstab query. The crosstab pulls information from a
query that counts the number of visits by date by calendar date i.e.:
1/3/05, 1/4/05, etc.

My problems are:
1. The crosstab won't work if the underlying query has a parameter

(I
ask

what the date range should be for the report.

2. The crosstab query returns the dates 1/3/05 and not the date of

the
week

"Monday" even though the underlying query is formatted to show just

the
day

of the week.

3. The report returns an error if there is no value for one day of the
week.

I tried putting in Column Headers in the Properties dialog box of the
crosstab query ("Monday","Tuesday","Wednesday","Thursday","Friday")

but
that

returns the error: "Data type mismatch in criteria expression."

I know how to create queries and have some experience with modules in
Access, but I am not an advanced user so please explain carefully.

Thank you.

BW

 

[Barbara Waller]

I need to show the week of the current month not the year. I created an
expression in my query called "Week2" that returns the right information as
long as there is a value in the [Visit-Date] field. There are two problems
with it:

1 It gives me an error if there was no visit for a customer.
2 Crosstab query \says there is a data mismatch if I try to use "Week2."

This is my expression:

Week2:
IIf([Month]<>1,Format([tbl_Job_Visits]![Visit-Date],"ww")-(([month]-1)*4)-1,
Format([tbl_Job_Visits]![Visit-Date],"ww"))

I'm saying that if the date is not in January subtract the total weeks that
have already gone by from the week of the year to give me the current week
of the month. Like I said above it works OK except for customers that don't
have a visit.

What do I need to change to get a zero for a non-visit, and make the
crosstab use the field? Please, keep in mind this is Access 97.

BW

Babrbara.
You can use the following expression for the column header:
Format([YourDates],"dddd")
This would give you the day.
The way I would approach this is to create a macro which would run a make
table query, selecting the dates by filtering for week number, you could use:
Week#: Format([YourDates],"ww")
This will return a week number, this way you would set the filter to the
specific week you want.
Hope this gives you some idea.
Fons

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I want a
report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a select query
set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

Hi Barbara. The way you could approach this is use your current query but
convert it to a make table query, than use the newly created table as the
source for your crosstab query.
Hope this helps.
Fons

:

Using Access97 (with Windows XP)

I am trying to create weekly activity report that shows how many

calls
were

made to each client by day and then give a total of calls for the

week.
It

would look like this:

Customer
Mon
Tue
Wed
Thur
Fri
Total

Jones
1


3

4

Peach

1
1
1
1
4

ZipIt
1
1
3
2
1
8



My report is based a crosstab query. The crosstab pulls information from a
query that counts the number of visits by date by calendar date i.e.:
1/3/05, 1/4/05, etc.

My problems are:
1. The crosstab won't work if the underlying query has a parameter

(I
ask

what the date range should be for the report.

2. The crosstab query returns the dates 1/3/05 and not the date of

the
week

"Monday" even though the underlying query is formatted to show just

the
day

of the week.

3. The report returns an error if there is no value for one day of the
week.

I tried putting in Column Headers in the Properties dialog box of the
crosstab query ("Monday","Tuesday","Wednesday","Thursday","Friday")

but
that

returns the error: "Data type mismatch in criteria expression."

I know how to create queries and have some experience with modules in
Access, but I am not an advanced user so please explain carefully.

Thank you.

BW

 

[Duane Hookom]

I would do the exact same report solution as the monthly relative
query/report. If you are missing a column, make sure you place all possible
column heading values into the Column Headings property of the query.

--
Duane Hookom
MS Access MVP

This should work for the weekly report I need to create and Duane gave me
a
good idea for the monthly report.

Is there a way to get around the error the report gives you when one day
of
the week has not information?

BW

Babrbara.
You can use the following expression for the column header:
Format([YourDates],"dddd")
This would give you the day.
The way I would approach this is to create a macro which would run a make
table query, selecting the dates by filtering for week number, you could use:
Week#: Format([YourDates],"ww")
This will return a week number, this way you would set the filter to the
specific week you want.
Hope this gives you some idea.
Fons

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to
return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this
may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I want a
report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for
week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a select query
set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

Hi Barbara. The way you could approach this is use your current
query but
convert it to a make table query, than use the newly created table as the
source for your crosstab query.
Hope this helps.
Fons

:

Using Access97 (with Windows XP)

I am trying to create weekly activity report that shows how many calls
were
made to each client by day and then give a total of calls for the week.
It
would look like this:

Customer
Mon
Tue
Wed
Thur
Fri
Total

Jones
1


3

4

Peach

1
1
1
1
4

ZipIt
1
1
3
2
1
8



My report is based a crosstab query. The crosstab pulls
information
from a
query that counts the number of visits by date by calendar date i.e.:
1/3/05, 1/4/05, etc.

My problems are:
1. The crosstab won't work if the underlying query has a parameter (I
ask
what the date range should be for the report.

2. The crosstab query returns the dates 1/3/05 and not the date of the
week
"Monday" even though the underlying query is formatted to show just the
day
of the week.

3. The report returns an error if there is no value for one day of the
week.

I tried putting in Column Headers in the Properties dialog box of the
crosstab query ("Monday","Tuesday","Wednesday","Thursday","Friday") but
that
returns the error: "Data type mismatch in criteria expression."

I know how to create queries and have some experience with modules in
Access, but I am not an advanced user so please explain carefully.

Thank you.

BW

 

[Barbara Waller]

Thanks to all the suggestions I have the queries and reports working
perfectly.

Thanks again.

BW

I would do the exact same report solution as the monthly relative
query/report. If you are missing a column, make sure you place all possible
column heading values into the Column Headings property of the query.

--
Duane Hookom
MS Access MVP

This should work for the weekly report I need to create and Duane gave me
a
good idea for the monthly report.

Is there a way to get around the error the report gives you when one day
of
the week has not information?

BW

Babrbara.
You can use the following expression for the column header:
Format([YourDates],"dddd")
This would give you the day.
The way I would approach this is to create a macro which would run a make
table query, selecting the dates by filtering for week number, you

could
use:

Week#: Format([YourDates],"ww")
This will return a week number, this way you would set the filter to the
specific week you want.
Hope this gives you some idea.
Fons

:

Thanks for your reply, but making a table doesn't seem to be any different
from using the query data. My problem seems to be that I want to
return the
day name of the week "Monday" for 1/3/05. I need a way to make the crosstab
to use the name not the date, so I can run this report every week and not
have to change the report field names to reflect the new date range.

My original post didn't show the "Report" the way I typed it so this
may
have been unclear. Let me explain again.

I have a salesman that visited 3 customers a total of 24 times. I

want
a

report that shows which customers he visited each day. And I want to show a
total of how many calls were made each day. What we do now in manually
Excel looks like this:

Customer Mon Tue Wed Thur Fri Total for
week
Jones 1 2 1
4
Peach 1 1 2 1 1
6
ZipIt 7 7
14
Totals 2 10 2 8 2
24

I can get this information with a crosstab query pulling from a

select
query

set to count, except I can't format the date so it will print if you change
the date range, or if there are "null" values for one of the dates.

This is the SQL statement from my crosstab report.

TRANSFORM Count([qry - count visits for week].CountOfDate) AS [The Value]
SELECT [qry - count visits for week].ID, Count([qry - count visits for
week].CountOfDate) AS [Total Of CountOfDate]
FROM [qry - count visits for week]
GROUP BY [qry - count visits for week].ID
PIVOT [qry - count visits for week].Date;

Is this any more clear?

BW

Hi Barbara. The way you could approach this is use your current
query but
convert it to a make table query, than use the newly created table

as
the

source for your crosstab query.
Hope this helps.
Fons

:

Using Access97 (with Windows XP)

I am trying to create weekly activity report that shows how many calls
were
made to each client by day and then give a total of calls for the week.
It
would look like this:

Customer
Mon
Tue
Wed
Thur
Fri
Total

Jones
1


3

4

Peach

1
1
1
1
4

ZipIt
1
1
3
2
1
8



My report is based a crosstab query. The crosstab pulls
information
from a
query that counts the number of visits by date by calendar date i.e.:
1/3/05, 1/4/05, etc.

My problems are:
1. The crosstab won't work if the underlying query has a

parameter
(I

ask
what the date range should be for the report.

2. The crosstab query returns the dates 1/3/05 and not the date

of
the

week
"Monday" even though the underlying query is formatted to show

just
the

day
of the week.

3. The report returns an error if there is no value for one day

of
the

week.

I tried putting in Column Headers in the Properties dialog box of the
crosstab query

("Monday","Tuesday","Wednesday","Thursday","Friday")
but

that
returns the error: "Data type mismatch in criteria expression."

I know how to create queries and have some experience with

modules
in

Access, but I am not an advanced user so please explain carefully.

Thank you.

BW