Help with a report

[abourg8646 via AccessMonster.com]

I'm new with access. I have 2 tables. 1st is employee which has employee name,
post, shift, and time,the 2nd is schedule which has date, hrs worked,
schedule, lwop, and some other fields. I made a form from the employee tabe
and a sub form from the schedule table. I made a quary with name, post, shift,
time, date, and schedule. I then made a crosstab quary with name, post, shift,
and time as row headings, date as a colum heading and schedual as a value. I
did this so that when I run my report then the date will go across the top of
the page. All this works good but I can't limit the dates to 1 week. The form
looks like this:
5/1/09 5/2/09 5/3/09 ect.
Jr post1 1st shift Off 0600 0600
Lb post2 2nd shift 0600 0600 1400

Like I said it all works but the date just keeps going on and on this report
I'm triing to limit it to 7 days the next report will for the whole month.
Another question is since the date is in a colum heading now will I be
limited to 255 days.

 

[KARL DEWEY]

This will always pull the current week
WHERE [YourDateField] Between Date()-Weekday(Date())+1 And
DateAdd("ww",1,Date()-Weekday(Date()))

Last week --
Between (Date()-Weekday(Date())+1)-7 And
(DateAdd("ww",1,Date()-Weekday(Date())))-7

 

[abourg8646 via AccessMonster.com]

This will always pull the current week
WHERE [YourDateField] Between Date()-Weekday(Date())+1 And
DateAdd("ww",1,Date()-Weekday(Date()))

Last week --
Between (Date()-Weekday(Date())+1)-7 And
(DateAdd("ww",1,Date()-Weekday(Date())))-7

I'm new with access. I have 2 tables. 1st is employee which has employee name,
post, shift, and time,the 2nd is schedule which has date, hrs worked,

[quoted text clipped - 13 lines]

Another question is since the date is in a colum heading now will I be
limited to 255 days.

when I put that into the criteria in my quary then it said that it does't
recognize 5/1/09 as a valid field name or expression

 

[KARL DEWEY]

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in apost.

This will always pull the current week
WHERE [YourDateField] Between Date()-Weekday(Date())+1 And
DateAdd("ww",1,Date()-Weekday(Date()))

Last week --
Between (Date()-Weekday(Date())+1)-7 And
(DateAdd("ww",1,Date()-Weekday(Date())))-7

I'm new with access. I have 2 tables. 1st is employee which has employee name,
post, shift, and time,the 2nd is schedule which has date, hrs worked,

[quoted text clipped - 13 lines]

Another question is since the date is in a colum heading now will I be
limited to 255 days.

when I put that into the criteria in my quary then it said that it does't
recognize 5/1/09 as a valid field name or expression

 

[abourg8646 via AccessMonster.com]

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in apost.

This will always pull the current week
WHERE [YourDateField] Between Date()-Weekday(Date())+1 And

[quoted text clipped - 12 lines]

when I put that into the criteria in my quary then it said that it does't
recognize 5/1/09 as a valid field name or expression

I went to the quary SQL and tried to paste those lines in there and it comes
back with a syntax error
and it highlights the WHERE.

 

[abourg8646 via AccessMonster.com]

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in apost.

[quoted text clipped - 4 lines]
I went to the quary SQL and tried to paste those lines in there and it comes
back with a syntax error
and it highlights the WHERE.

here is what the quary SQL looks like

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift, [Employee
Query].Post, [Employee Query].Time
FROM [Employee Query]
GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift, [Employee
Query].Post, [Employee Query].Time
ORDER BY Format([Date],"Short Date") DESC
PIVOT Format([Date],"Short Date");

 

[John Spencer]

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]
GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

ORDER BY [Employee Query].[Date] DESC
PIVOT Format([Date],"Short Date");

Any query is limited to returning a maximum of 255 columns. So you
could return a maximum of 255 - 4 date columns.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in apost.

[quoted text clipped - 4 lines]

when I put that into the criteria in my quary then it said that it does't
recognize 5/1/09 as a valid field name or expression

I went to the quary SQL and tried to paste those lines in there and it comes
back with a syntax error
and it highlights the WHERE.

here is what the quary SQL looks like

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift, [Employee
Query].Post, [Employee Query].Time
FROM [Employee Query]
GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift, [Employee
Query].Post, [Employee Query].Time
ORDER BY Format([Date],"Short Date") DESC
PIVOT Format([Date],"Short Date");

 

[abourg8646 via AccessMonster.com]

It still gave me a syntax error. Is there another way to display a field
across the page other then a crosstab quary?

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]
GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

ORDER BY [Employee Query].[Date] DESC
PIVOT Format([Date],"Short Date");

Any query is limited to returning a maximum of 255 columns. So you
could return a maximum of 255 - 4 date columns.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
[quoted text clipped - 15 lines]

ORDER BY Format([Date],"Short Date") DESC
PIVOT Format([Date],"Short Date");

 

[John Spencer]

I note that you have a field named Time and one named date. Those are
both reserved words and as such when used as a field name should be
surrounded with square brackets. Also I messed up and put the WHERE
clause in the wrong place. And there is no reason to order by the date
field and that might even cause an error. If you want your dates to
display in order across the top, you need to drop the format statement
or format the dates in yyyy-mm-dd format.

And while there are methods to display data from a column across the
page they are very complex to implement.

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time


PIVOT [Employee Query].[Date]

You can add a where clause on some other field or fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

It still gave me a syntax error. Is there another way to display a field
across the page other then a crosstab quary?

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]
GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

ORDER BY [Employee Query].[Date] DESC
PIVOT Format([Date],"Short Date");

Any query is limited to returning a maximum of 255 columns. So you
could return a maximum of 255 - 4 date columns.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Post your query SQL. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in apost.

[quoted text clipped - 15 lines]

ORDER BY Format([Date],"Short Date") DESC
PIVOT Format([Date],"Short Date");

 

[abourg8646 via AccessMonster.com]

this worked but it only pulled in the 1st 3 days. What I'm lookinf for is to
be able to select what dates the report will pull in. Like 3May -6 May or 7
May - 13 May.

I note that you have a field named Time and one named date. Those are
both reserved words and as such when used as a field name should be
surrounded with square brackets. Also I messed up and put the WHERE
clause in the wrong place. And there is no reason to order by the date
field and that might even cause an error. If you want your dates to
display in order across the top, you need to drop the format statement
or format the dates in yyyy-mm-dd format.

And while there are methods to display data from a column across the
page they are very complex to implement.

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

PIVOT [Employee Query].[Date]

You can add a where clause on some other field or fields.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

It still gave me a syntax error. Is there another way to display a field
across the page other then a crosstab quary?

[quoted text clipped - 28 lines]

ORDER BY Format([Date],"Short Date") DESC
PIVOT Format([Date],"Short Date");

 

[John Spencer]

If you want to enter the date range as a parameter then try

Parameters [Start date] DateTime, [End Date] DateTime
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]

WHERE [Employee Query].[Date]
Between [Start Date] And [End Date]

GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

PIVOT [Employee Query].[Date]

You can add your own ORDER BY clause to put the records in order by
Employee Name or whatever you choose.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I note that you have a field named Time and one named date. Those are
both reserved words and as such when used as a field name should be
surrounded with square brackets. Also I messed up and put the WHERE
clause in the wrong place. And there is no reason to order by the date
field and that might even cause an error. If you want your dates to
display in order across the top, you need to drop the format statement
or format the dates in yyyy-mm-dd format.

And while there are methods to display data from a column across the
page they are very complex to implement.

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time


PIVOT [Employee Query].[Date]

You can add a where clause on some other field or fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

It still gave me a syntax error. Is there another way to display a field
across the page other then a crosstab quary?

TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]
GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

WHERE [Employee Query].[Date]
Between DateAdd("d",-6-Weekday(Date()), Date()) And
DateAdd("d",1-Weekday(Date()),Date())

ORDER BY [Employee Query].[Date] DESC
PIVOT Format([Date],"Short Date");

Any query is limited to returning a maximum of 255 columns. So you
could return a maximum of 255 - 4 date columns.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Post your query SQL. Open in design view, click on VIEW - SQL
View, highlight all, copy, and paste in apost.
[quoted text clipped - 15 lines]
ORDER BY Format([Date],"Short Date") DESC PIVOT Format([Date],"Short
Date");

 

[abourg8646 via AccessMonster.com]

I really appreciate the help but now I’m getting a Syntax error in the
parameter clause.

If you want to enter the date range as a parameter then try

Parameters [Start date] DateTime, [End Date] DateTime
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]

WHERE [Employee Query].[Date]
Between [Start Date] And [End Date]

GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

PIVOT [Employee Query].[Date]

You can add your own ORDER BY clause to put the records in order by
Employee Name or whatever you choose.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I note that you have a field named Time and one named date. Those are
both reserved words and as such when used as a field name should be

[quoted text clipped - 62 lines]

ORDER BY Format([Date],"Short Date") DESC PIVOT Format([Date],"Short
Date");

 

[Douglas J. Steele]

John accidentally left out the semi-colon between the PARAMETERS declaration
and the SQL statement:

Parameters [Start date] DateTime, [End Date] DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
WHERE [Date]
Between [Start Date] And [End Date]
GROUP BY [Employee Name], Shift,
Post, [Time]
PIVOT [Employee Query].[Date]

Please heed John's advice about renaming your Date and Time fields! For a
comprehensive list of names to avoid (as well as a link to a free utility to
check your application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I really appreciate the help but now I'm getting a Syntax error in the
parameter clause.

If you want to enter the date range as a parameter then try

Parameters [Start date] DateTime, [End Date] DateTime
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Query].[Employee Name], [Employee Query].Shift
, [Employee Query].Post, [Employee Query].Time
FROM [Employee Query]

WHERE [Employee Query].[Date]
Between [Start Date] And [End Date]

GROUP BY [Employee Query].[Employee Name], [Employee Query].Shift,
[Employee Query].Post, [Employee Query].Time

PIVOT [Employee Query].[Date]

You can add your own ORDER BY clause to put the records in order by
Employee Name or whatever you choose.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I note that you have a field named Time and one named date. Those are
both reserved words and as such when used as a field name should be

[quoted text clipped - 62 lines]

ORDER BY Format([Date],"Short Date") DESC PIVOT Format([Date],"Short
Date");

 

[John Spencer]

Thanks for the backup.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[abourg8646 via AccessMonster.com]

Thanks that worked great on the crosstab quary but it wont let me create a
report.

John accidentally left out the semi-colon between the PARAMETERS declaration
and the SQL statement:

Parameters [Start date] DateTime, [End Date] DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
WHERE [Date]
Between [Start Date] And [End Date]
GROUP BY [Employee Name], Shift,
Post, [Time]
PIVOT [Employee Query].[Date]

Please heed John's advice about renaming your Date and Time fields! For a
comprehensive list of names to avoid (as well as a link to a free utility to
check your application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I really appreciate the help but now I'm getting a Syntax error in the
parameter clause.

[quoted text clipped - 30 lines]

ORDER BY Format([Date],"Short Date") DESC PIVOT Format([Date],"Short
Date");

 

[abourg8646 via AccessMonster.com]

When I go to Report wizard and pull up the crosstab quary then there is
nothing to select.

Thanks that worked great on the crosstab quary but it wont let me create a
report.

John accidentally left out the semi-colon between the PARAMETERS declaration
and the SQL statement:

[quoted text clipped - 19 lines]

ORDER BY Format([Date],"Short Date") DESC PIVOT Format([Date],"Short
Date");