R
Ronald Dodge
Access 2002, SP3
Initially posted this in the wrong newsgroup. I'm hoping someone can help
me out with regards to being able to group and aggregate data within the
query side without having to use a crosstab. Apparently, JET SQL uses a
more stringent different set of rules than what I'm use to using in ShowCase
Strategy Query.
Most of my SQL writing is actually more so based on using ShowCase Strategy
Query, which does allow for the below request when it's from out main JDE
database. However, I'm using Access Jet Engine for this project, and I'm
hoping I can do the same with it's query program rather than having to rely
on the report program to do it for me. The report program in Access is
similar to the ShowCase Strategy Report Writer program that I have used in
ShowCase, but I generally work with the Query side as I prefer to work with
the raw data as opposed to the masked data.
I so far have the following parameter query as shown below. What I would
like to do is group by Employee ID then group by "Date Worked", For the
"Date Worked", I would like to sum up all of the "Hours Worked" then if the
"fldbolPdLunch" is false, and the "Hours Worked" is greater than 3 for the
"Date Worked", then subtract a half hour from the "Hours Worked", and this
would become the new "Hours Worked" for the "Date Worked".
After the "Hours Worked" calculation has been resolved, I would then like to
have each day's "Hours Worked" summed up to be included in "Total Hours
Worked" for the employee over the time period that the query was ran for.
Generally, this query will be ran for a full week's time period at a time,
so as to allow for the proper calculations to take place dealing with the
pay rates, and determing how many hours are over time pay.
Using a crosstab query isn't the answer for this either as there's too many
things to take into account, such as an employee may have more than one
record in the tblHrsHist tbl for any one day of work given it may have done
things that may apply to multiple different work codes, and if the employee
doesn't get paid for lunch time, that complicate things more.
PARAMETERS
[START DATE] DateTime,
[END DATE] DateTime,
[EMPLOYEE ID] Text (255),
[FIRST NAME] Text (255),
[MIDDLE INITIAL] Text (255),
[LAST NAME] Text (255),
[SUFFIX] Text (255);
SELECT
tblHrsHist.fldlngHrsID,
tblHrsHist.fldstrEmpID,
tblEmp.fldstrFirstName,
tblEmp.fldstrMiddleInit,
tblEmp.fldstrLastName,
tblEmp.fldstrSuffix,
tblEmp.fldstrPayCode,
tblPayCode.fldbolPdLunch,
tblHrsHist.fldstrWrkCode,
tblHrsHist.flddblLogStartTime,
tblHrsHist.flddblActStartTime,
tblHrsHist.fldbolStartTimeAdj,
tblHrsHist.fldstrUsrNamStartTimeAdj,
tblHrsHist.flddblLogEndTime,
tblHrsHist.flddblActEndTime,
tblHrsHist.fldbolEndTimeAdj,
tblHrsHist.fldstrUsrNamEndTimeAdj,
Int(([flddblActEndTime]-[flddblActStartTime])*2400+.5)/100 AS [Time
Worked],
FormatDateTime(Int(tblHrsHist.flddblActEndTime-.28),0) AS [Date Worked]
FROM
tblPayCode INNER JOIN
(tblEmp INNER JOIN
tblHrsHist
ON tblEmp.fldstrEmpID=tblHrsHist.fldstrEmpID)
ON tblPayCode.fldstrPayCode=tblEmp.fldstrPayCode
WHERE (DateValue(FormatDateTime(Int(tblHrsHist.flddblActEndTime-.28),0))
Between [START DATE] And [END DATE]
Or [START DATE] Is Null
Or [END DATE] Is Null)
And (tblHrsHist.fldstrWrkCode=[WORK CODE]
Or [WORK CODE] Is Null)
And (tblEmp.fldstrFirstName=[FIRST NAME]
Or [FIRST NAME] Is Null)
And (tblEmp.fldstrMiddleInit=[MIDDLE INITIAL]
Or [MIDDLE INITIAL] Is Null)
And (tblEmp.fldstrLastName=[LAST NAME]
Or [LAST NAME] Is Null)
And (tblEmp.fldstrSuffix=[SUFFIX]
Or [SUFFIX] Is Null)
And (tblEmp.fldstrEmpID=[EMPLOYEE ID]
Or [EMPLOYEE ID] Is Null)
ORDER BY
tblHrsHist.fldstrEmpID,
tblHrsHist.flddblLogStartTime;
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
Initially posted this in the wrong newsgroup. I'm hoping someone can help
me out with regards to being able to group and aggregate data within the
query side without having to use a crosstab. Apparently, JET SQL uses a
more stringent different set of rules than what I'm use to using in ShowCase
Strategy Query.
Most of my SQL writing is actually more so based on using ShowCase Strategy
Query, which does allow for the below request when it's from out main JDE
database. However, I'm using Access Jet Engine for this project, and I'm
hoping I can do the same with it's query program rather than having to rely
on the report program to do it for me. The report program in Access is
similar to the ShowCase Strategy Report Writer program that I have used in
ShowCase, but I generally work with the Query side as I prefer to work with
the raw data as opposed to the masked data.
I so far have the following parameter query as shown below. What I would
like to do is group by Employee ID then group by "Date Worked", For the
"Date Worked", I would like to sum up all of the "Hours Worked" then if the
"fldbolPdLunch" is false, and the "Hours Worked" is greater than 3 for the
"Date Worked", then subtract a half hour from the "Hours Worked", and this
would become the new "Hours Worked" for the "Date Worked".
After the "Hours Worked" calculation has been resolved, I would then like to
have each day's "Hours Worked" summed up to be included in "Total Hours
Worked" for the employee over the time period that the query was ran for.
Generally, this query will be ran for a full week's time period at a time,
so as to allow for the proper calculations to take place dealing with the
pay rates, and determing how many hours are over time pay.
Using a crosstab query isn't the answer for this either as there's too many
things to take into account, such as an employee may have more than one
record in the tblHrsHist tbl for any one day of work given it may have done
things that may apply to multiple different work codes, and if the employee
doesn't get paid for lunch time, that complicate things more.
PARAMETERS
[START DATE] DateTime,
[END DATE] DateTime,
[EMPLOYEE ID] Text (255),
[FIRST NAME] Text (255),
[MIDDLE INITIAL] Text (255),
[LAST NAME] Text (255),
[SUFFIX] Text (255);
SELECT
tblHrsHist.fldlngHrsID,
tblHrsHist.fldstrEmpID,
tblEmp.fldstrFirstName,
tblEmp.fldstrMiddleInit,
tblEmp.fldstrLastName,
tblEmp.fldstrSuffix,
tblEmp.fldstrPayCode,
tblPayCode.fldbolPdLunch,
tblHrsHist.fldstrWrkCode,
tblHrsHist.flddblLogStartTime,
tblHrsHist.flddblActStartTime,
tblHrsHist.fldbolStartTimeAdj,
tblHrsHist.fldstrUsrNamStartTimeAdj,
tblHrsHist.flddblLogEndTime,
tblHrsHist.flddblActEndTime,
tblHrsHist.fldbolEndTimeAdj,
tblHrsHist.fldstrUsrNamEndTimeAdj,
Int(([flddblActEndTime]-[flddblActStartTime])*2400+.5)/100 AS [Time
Worked],
FormatDateTime(Int(tblHrsHist.flddblActEndTime-.28),0) AS [Date Worked]
FROM
tblPayCode INNER JOIN
(tblEmp INNER JOIN
tblHrsHist
ON tblEmp.fldstrEmpID=tblHrsHist.fldstrEmpID)
ON tblPayCode.fldstrPayCode=tblEmp.fldstrPayCode
WHERE (DateValue(FormatDateTime(Int(tblHrsHist.flddblActEndTime-.28),0))
Between [START DATE] And [END DATE]
Or [START DATE] Is Null
Or [END DATE] Is Null)
And (tblHrsHist.fldstrWrkCode=[WORK CODE]
Or [WORK CODE] Is Null)
And (tblEmp.fldstrFirstName=[FIRST NAME]
Or [FIRST NAME] Is Null)
And (tblEmp.fldstrMiddleInit=[MIDDLE INITIAL]
Or [MIDDLE INITIAL] Is Null)
And (tblEmp.fldstrLastName=[LAST NAME]
Or [LAST NAME] Is Null)
And (tblEmp.fldstrSuffix=[SUFFIX]
Or [SUFFIX] Is Null)
And (tblEmp.fldstrEmpID=[EMPLOYEE ID]
Or [EMPLOYEE ID] Is Null)
ORDER BY
tblHrsHist.fldstrEmpID,
tblHrsHist.flddblLogStartTime;
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000