Weekly crosstab query

N

Nick

I currently have a crosstab query where I request information by date with
the prameter as "weekendingdate". The query returns data for days Saturday
then Monday, through Friday. I would like to get the same information by
requesting information by week. Also how would I display weekday names in
colum heading
where =DateAdd("d",0,[Weekendingdate])?

Thanks
 
J

John Spencer

To display the weekday names (in order)

Change the PIVOT line of the query to read

PIVOT Format(YourDateField,"ddd") IN ("Sat", "Sun", "Mon", "Tue","Wed",
"Thu","Fri")

What do you envision as inputting to request the information by week? A
week number and a year number?

Perhaps you just want to put in ANY date in the week and have the begin date
(Saturday) and end date (Friday) automatically calculated.
Where YourDateField Between
DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])
and DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])+6

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
N

Nick

Thank you, I should digress and tell you that the crosstab query colum is
"Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate] and the heading in my report
is =DateAdd("d",0,[Weekendingdate]). I do not know if this makes a
difference. If so please advise me further. You are correct, I do want to put
any date in and still receive the data for Saturday, Monday, Tuesday,
Wedendsday and so on. Are their any changes I should make based on this
information? One other thing, I am not familiar with the term PIVOT, I am
sure I use it but do not know what it is.
Thank you again.

John Spencer said:
To display the weekday names (in order)

Change the PIVOT line of the query to read

PIVOT Format(YourDateField,"ddd") IN ("Sat", "Sun", "Mon", "Tue","Wed",
"Thu","Fri")

What do you envision as inputting to request the information by week? A
week number and a year number?

Perhaps you just want to put in ANY date in the week and have the begin date
(Saturday) and end date (Friday) automatically calculated.
Where YourDateField Between
DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])
and DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])+6

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Nick said:
I currently have a crosstab query where I request information by date with
the prameter as "weekendingdate". The query returns data for days Saturday
then Monday, through Friday. I would like to get the same information by
requesting information by week. Also how would I display weekday names in
colum heading
where =DateAdd("d",0,[Weekendingdate])?

Thanks
 
J

John Spencer

If you switch the crosstab query to SQL view you will see the actual SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.

Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you, I should digress and tell you that the crosstab query colum is
"Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate] and the heading in my report
is =DateAdd("d",0,[Weekendingdate]). I do not know if this makes a
difference. If so please advise me further. You are correct, I do want to put
any date in and still receive the data for Saturday, Monday, Tuesday,
Wedendsday and so on. Are their any changes I should make based on this
information? One other thing, I am not familiar with the term PIVOT, I am
sure I use it but do not know what it is.
Thank you again.

John Spencer said:
To display the weekday names (in order)

Change the PIVOT line of the query to read

PIVOT Format(YourDateField,"ddd") IN ("Sat", "Sun", "Mon", "Tue","Wed",
"Thu","Fri")

What do you envision as inputting to request the information by week? A
week number and a year number?

Perhaps you just want to put in ANY date in the week and have the begin date
(Saturday) and end date (Friday) automatically calculated.
Where YourDateField Between
DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])
and DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])+6

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Nick said:
I currently have a crosstab query where I request information by date with
the prameter as "weekendingdate". The query returns data for days Saturday
then Monday, through Friday. I would like to get the same information by
requesting information by week. Also how would I display weekday names in
colum heading
where =DateAdd("d",0,[Weekendingdate])?

Thanks
 
N

Nick

After pasting SQL in PIVOT, I am receiving Error 3142. This query one of
sevral on a report, will have to change all to read as this one does?
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4))
GROUP BY Employees.SSI, OvertimeTracking.aUTHORIZED
PIVOT "Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate]) In
("Lea0","Lea1","Lea2","Lea3","Lea4","Lea5","Lea6");


John Spencer said:
If you switch the crosstab query to SQL view you will see the actual SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.

Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you, I should digress and tell you that the crosstab query colum is
"Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate] and the heading in my report
is =DateAdd("d",0,[Weekendingdate]). I do not know if this makes a
difference. If so please advise me further. You are correct, I do want to put
any date in and still receive the data for Saturday, Monday, Tuesday,
Wedendsday and so on. Are their any changes I should make based on this
information? One other thing, I am not familiar with the term PIVOT, I am
sure I use it but do not know what it is.
Thank you again.

John Spencer said:
To display the weekday names (in order)

Change the PIVOT line of the query to read

PIVOT Format(YourDateField,"ddd") IN ("Sat", "Sun", "Mon", "Tue","Wed",
"Thu","Fri")

What do you envision as inputting to request the information by week? A
week number and a year number?

Perhaps you just want to put in ANY date in the week and have the begin date
(Saturday) and end date (Friday) automatically calculated.
Where YourDateField Between
DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])
and DateAdd("d",1-Weekday([EnterDate?],7),[EnterDate?])+6

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I currently have a crosstab query where I request information by date with
the prameter as "weekendingdate". The query returns data for days Saturday
then Monday, through Friday. I would like to get the same information by
requesting information by week. Also how would I display weekday names in
colum heading
where =DateAdd("d",0,[Weekendingdate])?

Thanks
 
J

John Spencer

First, you will have to clarify. A report can only have one query or
one table as its source. So how would you have multiple queries as the
source of a report?

I would expect to see a query that looke like
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking
ON Employees.SSI = OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4)) and OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6

GROUP BY Employees.SSI

PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

After pasting SQL in PIVOT, I am receiving Error 3142. This query one of
sevral on a report, will have to change all to read as this one does?
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4))
GROUP BY Employees.SSI, OvertimeTracking.aUTHORIZED
PIVOT "Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate]) In
("Lea0","Lea1","Lea2","Lea3","Lea4","Lea5","Lea6");


John Spencer said:
If you switch the crosstab query to SQL view you will see the actual SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.

Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
N

Nick

Thank you John, I do not know what I did incorrectly but I could not get the
query to work after pasting what you gave me however, I pasted SQL into a
new query and it worked fine. As for your first question, I am trying to
create a query that will total figures prior to the week requsted of the
current quarter, the total for the week(which I have) and then totals for
quarter to date. I assumed I would need a different query to accomplish this.
Do I build another query with the pramameter given or can I expand on what I
have? The report will have to show amount of leave given in a quarter prior
to the week requested and then total leave after the week has been completed
I already have the week totals.
Again Thank You!.
John Spencer said:
First, you will have to clarify. A report can only have one query or
one table as its source. So how would you have multiple queries as the
source of a report?

I would expect to see a query that looke like
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking
ON Employees.SSI = OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4)) and OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6

GROUP BY Employees.SSI

PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

After pasting SQL in PIVOT, I am receiving Error 3142. This query one of
sevral on a report, will have to change all to read as this one does?
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4))
GROUP BY Employees.SSI, OvertimeTracking.aUTHORIZED
PIVOT "Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate]) In
("Lea0","Lea1","Lea2","Lea3","Lea4","Lea5","Lea6");


John Spencer said:
If you switch the crosstab query to SQL view you will see the actual SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.

Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
J

John Spencer

Sorry about the delay in responding.

I have no solution for your problem. You might look at building additional
queries for the other information and then linking the queries together by
some unique field(s). OR you might look at using sub-reports in your main
report.

It kind of depends on how you wish to display the data in the report.

If you do use queries linked together you are probably going to need to
ensure that the queries return data for the same set of employees. If you
don't you could end up dropping employees from the report. That could mean
that yo need to change your INNER JOINS to LEFT (or Right) JOINS in order to
ensure that each employee is returned. The problem here is that if you
apply criteria to the table on the right of a left join then you negate the
left join. You would need to use a subquery to return the records or nest
queries.

Query One saved as qOT
PARAMETERS Weekendingdate DateTime;
SELECT EmpID, LeaveGranted, OTDLDate
FROM OverTimeTracking
WHERE Authorized = 4 and
OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6

Query Two uses query one in place of the OvertimeTracking table

TRANSFORM Max(qOT.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees LEFT JOIN qOT
ON Employees.SSI = qOT.EmpID

GROUP BY Employees.SSI

PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")

Now you need to do something similar for the other sets of data you need
and FINALLY you would need to combine the three queries you have created
into one with

SELECT qWeek.SSI,
FROM (qWeek INNER JOIN qQtrToDate
ON qWeek.SSI = qQtrToDate.SSI)
INNER JOIN qPrior ON
qWeek.SSI = qPrior.SSI

There may be something simpler, but I am not seeing it at present.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Nick said:
Thank you John, I do not know what I did incorrectly but I could not get
the
query to work after pasting what you gave me however, I pasted SQL into a
new query and it worked fine. As for your first question, I am trying to
create a query that will total figures prior to the week requsted of the
current quarter, the total for the week(which I have) and then totals for
quarter to date. I assumed I would need a different query to accomplish
this.
Do I build another query with the pramameter given or can I expand on what
I
have? The report will have to show amount of leave given in a quarter
prior
to the week requested and then total leave after the week has been
completed
I already have the week totals.
Again Thank You!.
John Spencer said:
First, you will have to clarify. A report can only have one query or
one table as its source. So how would you have multiple queries as the
source of a report?

I would expect to see a query that looke like
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking
ON Employees.SSI = OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4)) and OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6

GROUP BY Employees.SSI

PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

After pasting SQL in PIVOT, I am receiving Error 3142. This query one
of
sevral on a report, will have to change all to read as this one does?
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4))
GROUP BY Employees.SSI, OvertimeTracking.aUTHORIZED
PIVOT "Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate]) In
("Lea0","Lea1","Lea2","Lea3","Lea4","Lea5","Lea6");


:

If you switch the crosstab query to SQL view you will see the actual
SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.

Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
N

Nick

John. I have tried this on a simalar query and could not get it to work. I am
sending SQL statment in hopes that I will grasp this. Thanks for your
patience. PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.oTDLhOURUSED) AS MaxOfoTDLhOURUSED
SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 6)) and otdldate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6
GROUP BY Employees.SSI
PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri");


John Spencer said:
Sorry about the delay in responding.

I have no solution for your problem. You might look at building additional
queries for the other information and then linking the queries together by
some unique field(s). OR you might look at using sub-reports in your main
report.

It kind of depends on how you wish to display the data in the report.

If you do use queries linked together you are probably going to need to
ensure that the queries return data for the same set of employees. If you
don't you could end up dropping employees from the report. That could mean
that yo need to change your INNER JOINS to LEFT (or Right) JOINS in order to
ensure that each employee is returned. The problem here is that if you
apply criteria to the table on the right of a left join then you negate the
left join. You would need to use a subquery to return the records or nest
queries.

Query One saved as qOT
PARAMETERS Weekendingdate DateTime;
SELECT EmpID, LeaveGranted, OTDLDate
FROM OverTimeTracking
WHERE Authorized = 4 and
OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6

Query Two uses query one in place of the OvertimeTracking table

TRANSFORM Max(qOT.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees LEFT JOIN qOT
ON Employees.SSI = qOT.EmpID

GROUP BY Employees.SSI

PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")

Now you need to do something similar for the other sets of data you need
and FINALLY you would need to combine the three queries you have created
into one with

SELECT qWeek.SSI,
FROM (qWeek INNER JOIN qQtrToDate
ON qWeek.SSI = qQtrToDate.SSI)
INNER JOIN qPrior ON
qWeek.SSI = qPrior.SSI

There may be something simpler, but I am not seeing it at present.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Nick said:
Thank you John, I do not know what I did incorrectly but I could not get
the
query to work after pasting what you gave me however, I pasted SQL into a
new query and it worked fine. As for your first question, I am trying to
create a query that will total figures prior to the week requsted of the
current quarter, the total for the week(which I have) and then totals for
quarter to date. I assumed I would need a different query to accomplish
this.
Do I build another query with the pramameter given or can I expand on what
I
have? The report will have to show amount of leave given in a quarter
prior
to the week requested and then total leave after the week has been
completed
I already have the week totals.
Again Thank You!.
John Spencer said:
First, you will have to clarify. A report can only have one query or
one table as its source. So how would you have multiple queries as the
source of a report?

I would expect to see a query that looke like
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking
ON Employees.SSI = OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4)) and OTDLDate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6

GROUP BY Employees.SSI

PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Nick wrote:
After pasting SQL in PIVOT, I am receiving Error 3142. This query one
of
sevral on a report, will have to change all to read as this one does?
PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.LeaveGranted) AS MaxOfLeaveGranted
SELECT Employees.SSI
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)=4))
GROUP BY Employees.SSI, OvertimeTracking.aUTHORIZED
PIVOT "Lea" & DateDiff("d",[oTDLdaTE],[Weekendingdate]) In
("Lea0","Lea1","Lea2","Lea3","Lea4","Lea5","Lea6");


:

If you switch the crosstab query to SQL view you will see the actual
SQL
statement that the Design view builds. Copy that and paste it to a
posting and we can try to modify it.

Of course, you could try modifying it yourself in accordance with the
prior posting. If you do the work yourself, you are likely to retain
the technique and advance your skill level significantly.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 

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


Top