Frank Freese
asked on
Pivot on weekday
Experts,
The query belows works but needs some help. I need this such that I can generate a report that prints M - F across the top and places the control OnTimeScrap under the appropriate day. I've tried transform and pivot but I could never gert the syntex correct, if it is even the right way to go.
SELECT tblSupervisorReporting.Pro ductionDat e, tblLineSupervisors.Surpevi sorLN, tblLineCode.LineCodeDescri ption, tblLineSupervisors.Shift, tblSupervisorReporting.OnT imeScrap
FROM tblShift INNER JOIN (tblLineCode INNER JOIN (tblLineSupervisors INNER JOIN tblSupervisorReporting ON tblLineSupervisors.Supervi sorID = tblSupervisorReporting.Sup ervisorID) ON tblLineCode.LineCodeID = tblLineSupervisors.LineCod eID) ON tblShift.ShiftID = tblLineSupervisors.Shift
WHERE (((tblSupervisorReporting. Production Date) Between [Enter Beginning Date] And [Enter Ending Date]) AND ((tblLineSupervisors.Activ e)=Yes));
The query belows works but needs some help. I need this such that I can generate a report that prints M - F across the top and places the control OnTimeScrap under the appropriate day. I've tried transform and pivot but I could never gert the syntex correct, if it is even the right way to go.
SELECT tblSupervisorReporting.Pro
FROM tblShift INNER JOIN (tblLineCode INNER JOIN (tblLineSupervisors INNER JOIN tblSupervisorReporting ON tblLineSupervisors.Supervi
WHERE (((tblSupervisorReporting.
SELECT Format(tblSupervisorReport ing.Produc tionDate, "ddd") AS Wkday, tblLineSupervisors.Surpevi sorLN, tblLineCode.LineCodeDescri ption, tblLineSupervisors.Shift, tblSupervisorReporting.OnT imeScrap
ASKER
How do I incorporate this into my query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I replaced my Select statement with yours as follows:
SELECT Format(tblSupervisorReport ing.Produc tionDate, "ddd") AS Wkday, tblLineSupervisors.Surpevi sorLN, tblLineCode.LineCodeDescri ption, tblLineSupervisors.Shift, tblSupervisorReporting.OnT imeScrap
FROM tblShift INNER JOIN (tblLineCode INNER JOIN (tblLineSupervisors INNER JOIN tblSupervisorReporting ON tblLineSupervisors.Supervi sorID = tblSupervisorReporting.Sup ervisorID) ON tblLineCode.LineCodeID = tblLineSupervisors.LineCod eID) ON tblShift.ShiftID = tblLineSupervisors.Shift
WHERE (((tblSupervisorReporting. Production Date) Between [Enter Beginning Date] And [Enter Ending Date]) AND ((tblLineSupervisors.Activ e)=Yes));
I t worked.
I then created a cross tab query using the above query:
TRANSFORM Count(qryByDateOnTimeSuper visors.OnT imeScrap) AS CountOfOnTimeScrap
SELECT qryByDateOnTimeSupervisors .Surpeviso rLN, qryByDateOnTimeSupervisors .LineCodeD escription , qryByDateOnTimeSupervisors .Shift
FROM qryByDateOnTimeSupervisors
GROUP BY qryByDateOnTimeSupervisors .Surpeviso rLN, qryByDateOnTimeSupervisors .LineCodeD escription , qryByDateOnTimeSupervisors .Shift
PIVOT qryByDateOnTimeSupervisors .Wkday;
I ran the query and received the following error:
The Microsoft Jet database engine does not recognize "Enter Beginning Date" as a valid field name or expression".
SELECT Format(tblSupervisorReport
FROM tblShift INNER JOIN (tblLineCode INNER JOIN (tblLineSupervisors INNER JOIN tblSupervisorReporting ON tblLineSupervisors.Supervi
WHERE (((tblSupervisorReporting.
I t worked.
I then created a cross tab query using the above query:
TRANSFORM Count(qryByDateOnTimeSuper
SELECT qryByDateOnTimeSupervisors
FROM qryByDateOnTimeSupervisors
GROUP BY qryByDateOnTimeSupervisors
PIVOT qryByDateOnTimeSupervisors
I ran the query and received the following error:
The Microsoft Jet database engine does not recognize "Enter Beginning Date" as a valid field name or expression".