I wouldn’t use a cross tab query for this at all. I'd suggest using a form
with five subforms for the form, and a report with five subreports for the
report. I'll describe it for the report, but the form/subforms would be
similar. I'm assuming the report is for one week's data only.
Firstly to select the week in question create an unbound dialogue form,
frmWeeklyReport say, with a control, txtWeekStart say, in which to enter the
date of the Monday on which the week starts, and a button which opens the
report.
If you want the week selected to always be Monday to Friday, in the
BeforeUpdate event procedure of the txtWeekStart control put:
Const MESSAGETEXT = "Date must be a Monday."
Dim ctrl as Control
Set ctrl = Me.ActiveControl
If Not IsNull(ctrl) Then
If WeekDay(CDate(ctrl)) <> vbMonday Then
MsgBox MESSAGETEXT, vbExclamation, "Invalid date"
Cancel = True
End If
End If
This will force the user to enter a Monday date. Otherwise any date on any
day of the week can be entered and the 5 days from then will be returned.
For the report's RecordSource you'll need a query which returns the Crews as
distinct values and the dates for each day of the week. I'll assume
everything is in one table called WorkLog, so the query would be like this:
PARAMETERS
Forms!frmWeeklyReport!txtWeekStart DATETIME;
SELECT DISTINCT [Crew],
Forms!frmWeeklyReport!txtWeekStart As Day1,
DATEADD("d",1, Forms!frmWeeklyReport!txtWeekStart) As Day2,
DATEADD("d",2, Forms!frmWeeklyReport!txtWeekStart) As Day3,
DATEADD("d",3, Forms!frmWeeklyReport!txtWeekStart) As Day4,
DATEADD("d",4, Forms!frmWeeklyReport!txtWeekStart) As Day5
FROM [WorkLog]
ORDER BY [Crew];
Create a report based on this query and layout the bound controls in the page
header so the Day1 is indented:
………………..Day1…….Day2…….Day3…….Day4…….Day5
and in the detail section put the control bound to the Crew field on the left:
Crew
Sort the report by Crew in the Sorting and Grouping dialogue in report design
view.
Next create the 5 subreports. They'll all be the same but with different
RecordSource properties so you can design the first then copy, rename and
change its RecordsSource for each of the others. Layout the subreport with
the work order, location etc controls above each other in a narrow format.
Do not include the crew or workdate. The query for the first subreport's
RecordSource will be like this:
PARAMETERS
Forms!frmWeeklyReport!txtWeekStart DATETIME;
SELECT [Crew], [Work Order], [Location], <etc>
FROM [WorkLog]
WHERE [Workdate] =
Forms!frmWeeklyReport!txtWeekStart;
The query for the second report's RecordSource will be:
PARAMETERS
Forms!frmWeeklyReport!txtWeekStart DATETIME;
SELECT [Crew], [Work Order], [Location],, <etc>
FROM [WorkLog]
WHERE [Workdate] =
DATEADD("d",1, Forms!frmWeeklyReport!txtWeekStart);
and so on until the fifth subreport. Sort each subreport by Work Order.
In the main report place the first subreport in the detail section alongside
the Crew control and under the Day1 control, the second under the Day2
control and so on.
Link each subreport to the main report by setting its LinkMasterFields and
LinkChildFields properties to Crew (change the field names here and in the
above to your real ones as appropriate).
Make sure that that the main report's detail section, and those of each
subreport have their CanGrow property set to True (Yes in the properties
sheet), the subform controls in the main report have their CanGrow property
set to True, and that any controls in the subforms which need to grow
vertically also have their CanGrow property set to True. Also set the main
report's details section's KeepTogether property to True.
When you open the report from the dialogue form you should get each crew
listed in order, with alongside, their work for each day of the week in
question under each date in the page header.
Ken Sheridan
Stafford, England
The infopath side is working fine and has no bearing on the access end in
this case.
Well first let me thank you for your help and then let me tell you that it
has worked.
The crosstab query works and the the only problem now is that all the data
is displaying vertically. When I created the report from the crosstab query
the data is still showing up vertically under one date even thought there are
different dates. The query is calculating the totals of the rows for the
number of work orders for each crew on a specific date.
No experience working InfoPath into Access, so perhaps another newsgroup
reader can offer ideas.
[quoted text clipped - 93 lines]
--
.
