Crosstab Column headings with firstdayofweek

L

lynda

My boss asked me to make a crosstab query to count how
many sales note were written in a week by each employee.
He does not want to see column heading looks like w01Jan,
w02Jan. If we use format like w01Jan, w02Jan, sometimes
one week cross two months, the same week will be shown
like w05Jan, w05Feb. It is confusing. He would like to see
the columns heading is first day of the week, for example,
Jul 6, Jul 13, Jul 20, Jul 27 instead of week - month
format. My tables are: tblSalesNotes(noteID, employeeID,
dateNote, Notes), tblemployeeInfo(employeeID,
employeelastname, employeefirstname)
Someone helped me make the crosstab with column heading
format like w01Jan, w02Jan..., can anyone help me to look
it again and change it to column heaidngs is like Jan 6,
Jan 13, Jan 20 format? Note Jan 6, Jan 13 Jan 20 are
Sundays. Thanks!
Best Regards
Lynda


RANSFORM Count(tblsalesnotes.dateNote) AS CountOfdateNote
SELECT [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname] AS WrittenBy, Count
(tblsalesnotes.dateNote) AS [Total Notes]
FROM tblemployeeinfo INNER JOIN tblsalesnotes ON
tblemployeeinfo.employeeID = tblsalesnotes.employeeID
WHERE (((Year([dateNote]))=Year(Date()))
GROUP BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
ORDER BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
PIVOT "w" & Format(DatePart("ww",[dateNote]),"00") & Format
([dateNote],"mmm");
 
L

lynda

Hi, Duane,
Thank you! It is a very good idea to create a form to set
date period for the query. But how to solve set
thefirstdayofweek as column headings? At first, I set W1,
W2, W3... as column headings. My boss didn't like it
because he didn't know which week is in which month. He
suggested to put month in the column headings, like w1Jan,
w2Jan... Since sometimes one week crosses two months, it
causes problem like one month has five weeks, and repeat
week in next month, he didn't like the format W1Jan,
W2jan.., now he wants to see the first day of the week as
the column headings, any idea for that? Thanks!
Best Regards
Lynda
-----Original Message-----
I would create a form with a text box for ending date (form!frmA!txtEnd).
Then create a column heading expression of
"W" & DateDiff("ww", [DateNote], forms!frmA!txtEnd)
You would need to set the Query | Parameters
forms!frmA!txtEnd Date/Time
Then determine how many weeks you want to display in your report. Set the
Column Headings property to:
"W0","W1","W2","W3"...
W0 will be all the data from same week as forms!frmA! txtEnd. W1 will be the
prior week, etc.

Then build your report from this query. For column labels in the report, use
text boxes with control sources of:
=DateAdd("ww",0,forms!frmA!txtEnd)
=DateAdd("ww",-1,forms!frmA!txtEnd)
=DateAdd("ww",-2,forms!frmA!txtEnd)
etc


--
Duane Hookom
MS Access MVP


lynda said:
My boss asked me to make a crosstab query to count how
many sales note were written in a week by each employee.
He does not want to see column heading looks like w01Jan,
w02Jan. If we use format like w01Jan, w02Jan, sometimes
one week cross two months, the same week will be shown
like w05Jan, w05Feb. It is confusing. He would like to see
the columns heading is first day of the week, for example,
Jul 6, Jul 13, Jul 20, Jul 27 instead of week - month
format. My tables are: tblSalesNotes(noteID, employeeID,
dateNote, Notes), tblemployeeInfo(employeeID,
employeelastname, employeefirstname)
Someone helped me make the crosstab with column heading
format like w01Jan, w02Jan..., can anyone help me to look
it again and change it to column heaidngs is like Jan 6,
Jan 13, Jan 20 format? Note Jan 6, Jan 13 Jan 20 are
Sundays. Thanks!
Best Regards
Lynda


RANSFORM Count(tblsalesnotes.dateNote) AS CountOfdateNote
SELECT [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname] AS WrittenBy, Count
(tblsalesnotes.dateNote) AS [Total Notes]
FROM tblemployeeinfo INNER JOIN tblsalesnotes ON
tblemployeeinfo.employeeID = tblsalesnotes.employeeID
WHERE (((Year([dateNote]))=Year(Date()))
GROUP BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
ORDER BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
PIVOT "w" & Format(DatePart("ww",[dateNote]),"00") & Format
([dateNote],"mmm");
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Did you create a report to display the results?

--
Duane Hookom
MS Access MVP


lynda said:
Hi, Duane,
Thank you! It is a very good idea to create a form to set
date period for the query. But how to solve set
thefirstdayofweek as column headings? At first, I set W1,
W2, W3... as column headings. My boss didn't like it
because he didn't know which week is in which month. He
suggested to put month in the column headings, like w1Jan,
w2Jan... Since sometimes one week crosses two months, it
causes problem like one month has five weeks, and repeat
week in next month, he didn't like the format W1Jan,
W2jan.., now he wants to see the first day of the week as
the column headings, any idea for that? Thanks!
Best Regards
Lynda
-----Original Message-----
I would create a form with a text box for ending date (form!frmA!txtEnd).
Then create a column heading expression of
"W" & DateDiff("ww", [DateNote], forms!frmA!txtEnd)
You would need to set the Query | Parameters
forms!frmA!txtEnd Date/Time
Then determine how many weeks you want to display in your report. Set the
Column Headings property to:
"W0","W1","W2","W3"...
W0 will be all the data from same week as forms!frmA! txtEnd. W1 will be the
prior week, etc.

Then build your report from this query. For column labels in the report, use
text boxes with control sources of:
=DateAdd("ww",0,forms!frmA!txtEnd)
=DateAdd("ww",-1,forms!frmA!txtEnd)
=DateAdd("ww",-2,forms!frmA!txtEnd)
etc


--
Duane Hookom
MS Access MVP


lynda said:
My boss asked me to make a crosstab query to count how
many sales note were written in a week by each employee.
He does not want to see column heading looks like w01Jan,
w02Jan. If we use format like w01Jan, w02Jan, sometimes
one week cross two months, the same week will be shown
like w05Jan, w05Feb. It is confusing. He would like to see
the columns heading is first day of the week, for example,
Jul 6, Jul 13, Jul 20, Jul 27 instead of week - month
format. My tables are: tblSalesNotes(noteID, employeeID,
dateNote, Notes), tblemployeeInfo(employeeID,
employeelastname, employeefirstname)
Someone helped me make the crosstab with column heading
format like w01Jan, w02Jan..., can anyone help me to look
it again and change it to column heaidngs is like Jan 6,
Jan 13, Jan 20 format? Note Jan 6, Jan 13 Jan 20 are
Sundays. Thanks!
Best Regards
Lynda


RANSFORM Count(tblsalesnotes.dateNote) AS CountOfdateNote
SELECT [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname] AS WrittenBy, Count
(tblsalesnotes.dateNote) AS [Total Notes]
FROM tblemployeeinfo INNER JOIN tblsalesnotes ON
tblemployeeinfo.employeeID = tblsalesnotes.employeeID
WHERE (((Year([dateNote]))=Year(Date()))
GROUP BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
ORDER BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
PIVOT "w" & Format(DatePart("ww",[dateNote]),"00") & Format
([dateNote],"mmm");
Click to expand...


.
Click to expand...
Click to expand...
 
D

Duane Hookom

OK, so if you have a report then there is no reason to display W1 or any
other field name. Look further down in this reply to my suggestion for
creating column titles in your report.

--
Duane Hookom
MS Access MVP


lynda said:
Yes
Lynda
-----Original Message-----
Did you create a report to display the results?

--
Duane Hookom
MS Access MVP


lynda said:
Hi, Duane,
Thank you! It is a very good idea to create a form to set
date period for the query. But how to solve set
thefirstdayofweek as column headings? At first, I set W1,
W2, W3... as column headings. My boss didn't like it
because he didn't know which week is in which month. He
suggested to put month in the column headings, like w1Jan,
w2Jan... Since sometimes one week crosses two months, it
causes problem like one month has five weeks, and repeat
week in next month, he didn't like the format W1Jan,
W2jan.., now he wants to see the first day of the week as
the column headings, any idea for that? Thanks!
Best Regards
Lynda
-----Original Message-----
I would create a form with a text box for ending date
(form!frmA!txtEnd).
Then create a column heading expression of
"W" & DateDiff("ww", [DateNote], forms!frmA!txtEnd)
You would need to set the Query | Parameters
forms!frmA!txtEnd Date/Time
Then determine how many weeks you want to display in your
report. Set the
Column Headings property to:
"W0","W1","W2","W3"...
W0 will be all the data from same week as forms!frmA!
txtEnd. W1 will be the
prior week, etc.

Then build your report from this query. For column labels
in the report, use
text boxes with control sources of:
=DateAdd("ww",0,forms!frmA!txtEnd)
=DateAdd("ww",-1,forms!frmA!txtEnd)
=DateAdd("ww",-2,forms!frmA!txtEnd)
etc


--
Duane Hookom
MS Access MVP


My boss asked me to make a crosstab query to count how
many sales note were written in a week by each employee.
He does not want to see column heading looks like
w01Jan,
w02Jan. If we use format like w01Jan, w02Jan, sometimes
one week cross two months, the same week will be shown
like w05Jan, w05Feb. It is confusing. He would like to
see
the columns heading is first day of the week, for
example,
Jul 6, Jul 13, Jul 20, Jul 27 instead of week - month
format. My tables are: tblSalesNotes(noteID, employeeID,
dateNote, Notes), tblemployeeInfo(employeeID,
employeelastname, employeefirstname)
Someone helped me make the crosstab with column heading
format like w01Jan, w02Jan..., can anyone help me to
look
it again and change it to column heaidngs is like Jan 6,
Jan 13, Jan 20 format? Note Jan 6, Jan 13 Jan 20 are
Sundays. Thanks!
Best Regards
Lynda


RANSFORM Count(tblsalesnotes.dateNote) AS
CountOfdateNote
SELECT [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname] AS WrittenBy, Count
(tblsalesnotes.dateNote) AS [Total Notes]
FROM tblemployeeinfo INNER JOIN tblsalesnotes ON
tblemployeeinfo.employeeID = tblsalesnotes.employeeID
WHERE (((Year([dateNote]))=Year(Date()))
GROUP BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
ORDER BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
PIVOT "w" & Format(DatePart("ww",[dateNote]),"00") &
Format
([dateNote],"mmm");



.
Click to expand...


.
Click to expand...
Click to expand...
 
L

lynda

Hi, Dunnes,
Thank you very much! I appoligize fro previous post that I
didn't really unbderstand what you suggested. I tested it,
it shows nicely the firstoftheweek. Thank you.
Best Regards
Lynda
-----Original Message-----
I would create a form with a text box for ending date (form!frmA!txtEnd).
Then create a column heading expression of
"W" & DateDiff("ww", [DateNote], forms!frmA!txtEnd)
You would need to set the Query | Parameters
forms!frmA!txtEnd Date/Time
Then determine how many weeks you want to display in your report. Set the
Column Headings property to:
"W0","W1","W2","W3"...
W0 will be all the data from same week as forms!frmA! txtEnd. W1 will be the
prior week, etc.

Then build your report from this query. For column labels in the report, use
text boxes with control sources of:
=DateAdd("ww",0,forms!frmA!txtEnd)
=DateAdd("ww",-1,forms!frmA!txtEnd)
=DateAdd("ww",-2,forms!frmA!txtEnd)
etc


--
Duane Hookom
MS Access MVP


lynda said:
My boss asked me to make a crosstab query to count how
many sales note were written in a week by each employee.
He does not want to see column heading looks like w01Jan,
w02Jan. If we use format like w01Jan, w02Jan, sometimes
one week cross two months, the same week will be shown
like w05Jan, w05Feb. It is confusing. He would like to see
the columns heading is first day of the week, for example,
Jul 6, Jul 13, Jul 20, Jul 27 instead of week - month
format. My tables are: tblSalesNotes(noteID, employeeID,
dateNote, Notes), tblemployeeInfo(employeeID,
employeelastname, employeefirstname)
Someone helped me make the crosstab with column heading
format like w01Jan, w02Jan..., can anyone help me to look
it again and change it to column heaidngs is like Jan 6,
Jan 13, Jan 20 format? Note Jan 6, Jan 13 Jan 20 are
Sundays. Thanks!
Best Regards
Lynda


RANSFORM Count(tblsalesnotes.dateNote) AS CountOfdateNote
SELECT [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname] AS WrittenBy, Count
(tblsalesnotes.dateNote) AS [Total Notes]
FROM tblemployeeinfo INNER JOIN tblsalesnotes ON
tblemployeeinfo.employeeID = tblsalesnotes.employeeID
WHERE (((Year([dateNote]))=Year(Date()))
GROUP BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
ORDER BY [tblemployeeinfo].[employeefirstname] & " " &
[tblemployeeinfo].[employeelastname]
PIVOT "w" & Format(DatePart("ww",[dateNote]),"00") & Format
([dateNote],"mmm");
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query to identify the column headings 3
Crosstab By Month with only last 6 months data 6
Crosstab query column headings 1
Creating Static Column Headings in Crosstab Queries 5
how to group column headings in crosstab query 1
Create column headings in crosstab 1
Crosstab Query based on Ordinal Values 6
Crosstab Query Omitting Some Items 1

Top