complex date criteria for query

[MAINOH]

Hi,

Details:

tblTIMESHEETDATA
Fields:
WRKDAY (format date/time-short date)
SUBNAME
PROJECT
ACTIVITY
ENGDESC
HOURS

What I need is a query that will sum hours by SUBNAME/PROJECT/ACTIVITY grouping. This would be easy;
EXCEPT I need the hours grouped and summed based on pay periods. The pay period is always the 1-15th and the 16th-last day of month.

So if John Doe worked the following (for ease assume it is same project/activity):
1/4 2 hours
1/7 3 hours
1/18 1 hour
1/25 2 hours
2/14 4 hours
2/27 1 hours
3/28 6 hours

I would need a query that gave the following:
Period ending 1/15 5 hours
Period ending 1/31 3 hours
Period ending 2/15 4 hours
Period ending 2/28 1 hour
Period ending 3/31 6 hours

Any assistance is greatly appreciated.

Thank you so much!

Lisa

 

[PHV]

A starting point point:
SELECT SUBNAME,PROJECT,ACTIVITY
,Format(WRKDAY,'yyyymm') & (Day(WRKDAY)\16) AS PayPeriod
,Sum(HOURS) AS TotalHours
FROM tblTIMESHEETDATA
GROUP BY SUBNAME,PROJECT,ACTIVITY
,Format(WRKDAY,'yyyymm') & (Day(WRKDAY)\16)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SgtJarrow]

Perhaps this???

SELECT Month([WRKDAY]) AS [Month], IIf(Day([WRKDAY])<=15,1,2) AS Period, tblTIMESHEETDATA.SUBNAME, tblTIMESHEETDATA.PROJECT, tblTIMESHEETDATA.ACTIVITY, Sum(tblTIMESHEETDATA.HOURS) AS SumOfHOURS
FROM tblTIMESHEETDATA
GROUP BY Month([WRKDAY]), IIf(Day([WRKDAY])<=15,1,2), tblTIMESHEETDATA.SUBNAME, tblTIMESHEETDATA.PROJECT, tblTIMESHEETDATA.ACTIVITY;

I used Month and Day functions to separate the WRKDAY field into two different periods....then group by those.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[MAINOH]

Thank you both for your input! Your suggestion works wonderfully Robert!