Total Weeks and Concatenate Period Dates

[mabuhay87]

Original Data:

EmpNo---------Pay Period
1----------------11/11/2013
1----------------11/25/2013
1----------------12/9/2013
1----------------12/23/2013
1----------------1/6/2014
1----------------1/23/2014
1----------------2/10/2014



First Query Output: Count Total Weeks Each Pay Period

EmpNo----------Pay Period------------Total Weeks
1----------------11/11/2013---------------2
1----------------11/25/2013---------------2
1----------------12/9/2013----------------2
1----------------12/23/2013---------------2
1----------------1/6/2014------------------2
1----------------1/23/2014-----------------2
1----------------2/10/2014-----------------2

Second Query Output: Concatenate Pay Periods by Months, or by Year, or by showing all records.
By months:
EmpNo------------------Period
1----------------11/11/2013 - 11/25/2013
1----------------12/9/2013 - 12/23/2013
1----------------1/6/2014 - 1/23/2014
1----------------2/10/2014 -


By year:
EmpNo------------------Period
1----------------11/11/2013 - 12/23/2013
1----------------1/6/2014 - 2/10/2014


By all records:
EmpNo------------------Period
1----------------11/11/2013 - 2/10/2014

Please help. Thank you.

[June7]

Not sure what you want. Do you just want to show the begin and end date in a single field? Or do you want to summarize wages by some specified range?

Looks like the pay periods are always 2 weeks.

[mabuhay87]

1) show the begin and end date in a single field. How to query that?
2) how to query 2 weeks of a pay period?

Thank you.

[June7]

Is PayPeriod the end date? If the pay period is always 2 weeks, calculate the start or end date. 2 weeks is 14 days. Add or subtract 13. The 11th and 25th can't be in the same period, that would be 15 days.

SELECT PayPeriod - 13 & "-" & PayPeriod AS PP FROM table;

or


SELECT PayPeriod & "-" & PayPeriod + 13 AS PP FROM table;

To filter for a specific period:

SELECT * FROM table WHERE PayPeriod BETWEEN [enter start date] AND [enter start date] + 13;

[enter start date] can be input by reference to textbox on a form.

[mabuhay87]

Thank you June7.