How do I determine calendar quarters in Access ?

[Guest]

I enter payroll figures ( date, hours worked) and use Access 2002 to
calculate deductions and prepare payroll slips. I aslo need to subtotal
these deductions in addition to hours worked for each calendar quarter. I
would like to use a template that would allow me to subtotal payroll data
(hours worked, SS Tax, Med Tax, Fed Inc Tax, etc.) by looking up dates worked
and segregating them into the repective calendar quarters. Thank you.

 

[Allen Browne]

In your query, enter this into a fresh column in the Field row:
TheYear: Year([PayDate])
replacing PayDate with your field name.

In the next column, enter this in the Field row:
TheQuarter: DatePart("q", [PayDate])

You can now group by the year and quarter to get your totals.

 

[Guest]

Thanks for the reply. I gave this a try and kept receiving error: "The
expression you entered contains invalid syntax. You may have entered an
operand without an operator." Sorry, but maybe I am not assuming something I
should be. Thanks.

In your query, enter this into a fresh column in the Field row:
TheYear: Year([PayDate])
replacing PayDate with your field name.

In the next column, enter this in the Field row:
TheQuarter: DatePart("q", [PayDate])

You can now group by the year and quarter to get your totals.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I enter payroll figures ( date, hours worked) and use Access 2002 to
calculate deductions and prepare payroll slips. I aslo need to subtotal
these deductions in addition to hours worked for each calendar quarter. I
would like to use a template that would allow me to subtotal payroll data
(hours worked, SS Tax, Med Tax, Fed Inc Tax, etc.) by looking up dates
worked
and segregating them into the repective calendar quarters. Thank you.

 

[Michael Gramelspacher]

This is rather intimidating. It is a query to run in Northwind. It
totals sales by quarter. It is something I copied from an example by
Hugo Korenlis. For the parameter use 1/1/1998.

It is only an example and may or may not be useful.

PARAMETERS [Enter Date:] DateTime;
SELECT Orders.CustomerID,
SUM(IIf(DATEDIFF("q",DATEADD("q",DATEDIFF("q",2,[Enter
Date:])-4,2),Orders.ShippedDate)=0,[Order Subtotals].Subtotal,0)) AS
1st_Qtr_Sales, SUM(IIf(DATEDIFF("q",DATEADD("q",DATEDIFF("q",2,[Enter
Date:])-3,2),Orders.ShippedDate)=0,[Order Subtotals].Subtotal,0)) AS
2nd_Qtr_Sales, SUM(IIf(DATEDIFF("q",DATEADD("q",DATEDIFF("q",2,[Enter
Date:])-2,2),Orders.ShippedDate)=0,[Order Subtotals].Subtotal,0)) AS
3rd_Qtr_Sales, SUM(IIf(DATEDIFF("q",DATEADD("q",DATEDIFF("q",2,[Enter
Date:])-1,2),Orders.ShippedDate)=0,[Order Subtotals].Subtotal,0)) AS
4th_Qtr_Sales,
SUM(IIf(DATEDIFF("yyyy",DATEADD("yyyy",DATEDIFF("yyyy",2,[Enter
Date:])-1,2),Orders.ShippedDate)=0,[Order Subtotals].Subtotal,0)) AS
Last_Yr_Sales
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=[Order
Subtotals].OrderID
WHERE Orders.ShippedDate Is Not Null And
Orders.ShippedDate>=DATEADD("yyyy",DATEDIFF("yyyy",2,[Enter
Date:])-1,2)
GROUP BY Orders.CustomerID;

 

[Allen Browne]

Try just one expression at a time? Which one gives the error?

The Year() should work in any version of Access.
In an old version, you could replace the 2nd one with:
((Month([PayDate]) - 1) \ 3) + 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply. I gave this a try and kept receiving error: "The
expression you entered contains invalid syntax. You may have entered an
operand without an operator." Sorry, but maybe I am not assuming
something I
should be. Thanks.

In your query, enter this into a fresh column in the Field row:
TheYear: Year([PayDate])
replacing PayDate with your field name.

In the next column, enter this in the Field row:
TheQuarter: DatePart("q", [PayDate])

You can now group by the year and quarter to get your totals.

I enter payroll figures ( date, hours worked) and use Access 2002 to
calculate deductions and prepare payroll slips. I aslo need to
subtotal
these deductions in addition to hours worked for each calendar quarter.
I
would like to use a template that would allow me to subtotal payroll
data
(hours worked, SS Tax, Med Tax, Fed Inc Tax, etc.) by looking up dates
worked
and segregating them into the repective calendar quarters. Thank you.

 

[Guest]

Great. I got it now. I was entering "TheYear: Year([PayDate])" and getting
the error when I should have been entering just "Year([PayDate])". Thanks
again.

Try just one expression at a time? Which one gives the error?

The Year() should work in any version of Access.
In an old version, you could replace the 2nd one with:
((Month([PayDate]) - 1) \ 3) + 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply. I gave this a try and kept receiving error: "The
expression you entered contains invalid syntax. You may have entered an
operand without an operator." Sorry, but maybe I am not assuming
something I
should be. Thanks.

In your query, enter this into a fresh column in the Field row:
TheYear: Year([PayDate])
replacing PayDate with your field name.

In the next column, enter this in the Field row:
TheQuarter: DatePart("q", [PayDate])

You can now group by the year and quarter to get your totals.

I enter payroll figures ( date, hours worked) and use Access 2002 to
calculate deductions and prepare payroll slips. I aslo need to
subtotal
these deductions in addition to hours worked for each calendar quarter.
I
would like to use a template that would allow me to subtotal payroll
data
(hours worked, SS Tax, Med Tax, Fed Inc Tax, etc.) by looking up dates
worked
and segregating them into the repective calendar quarters. Thank you.

 

[Guest]

Now as a follow-up on this same matter, I wish to also track
Attendance/Performance along with weekly hours, taxes, etc. The scenario is
that if attendance/good performance is acceptable (yes/no) for consecutive
weeks then 1) after certain specified time (months) a pay raise will be given
and 2) at the end of each yearly quarter (1st, 2nd, 3rd, 4th) a pay
differential will be given based on total hours worked in quarter. Hope this
is not too confusing. Thanks in advance !

 

[Allen Browne]

Once you have the GROUP BY working for the quarter, Sum the yes/no field.
Since True is -1 and False is 0, if you get 13, the yes/no field was true
for all the weeks in the quarter.

If you wish to sum across a different time frame than you are grouping on,
you probably need a subquery. Here's an introduction to that concept:
http://allenbrowne.com/subquery-01.html

 

[Guest]

Thats all very good, but...

Our fiscal year starts December 1. I have a report that needs to translate
a date into the correct fiscal quarter.

If I were doing it in Excel I would pop this formula into a cell...
=MOD(CEILING(22+MONTH(dateserial)-11-1,3)/3,4)+1

How do I drop this into a query where dateserial is the date from the
recordsource?

Thanks,
owp^3

 

[Michel Walsh]

DatePart("q", DateAdd("m", 1, YourDateHere) )

So, for the first of March, DateAdd pushing the value of the date by one
month places it in April, the second quarter, YOUR second quarter in your
fiscal year.

Alternatively, you can add -11 instead of +1 month.


Hoping it may help,
Vanderghast, Access MVP

 

[Marshall Barton]

Our fiscal year starts December 1. I have a report that needs to translate
a date into the correct fiscal quarter.

If I were doing it in Excel I would pop this formula into a cell...
=MOD(CEILING(22+MONTH(dateserial)-11-1,3)/3,4)+1

How do I drop this into a query where dateserial is the date from the
recordsource?

The quarter is:

DatePart("q",DateSerial(Year([dateserial],Month([dateserial])+1,1)

FYI: dateserial is a poor choice for a field name.

 

[Guest]

Thanks Michel & Marshall both solutions helped me think it through, though
Michel's was a little simpler.

Here's what I settled on
Expr1: DatePart("q",DateAdd("m",1,[BU_Target_Install])) & "Q " &
DatePart("yyyy",[BU_Target_Install])

with a result of 1Q 2007.

As to the dateserial comment, you are right I would never use that as a
variable name. I was just trying to indicate what kind of value would be in
the cell instead of a cell reference.

Thanks Again!!!

owp^3

Our fiscal year starts December 1. I have a report that needs to translate
a date into the correct fiscal quarter.

If I were doing it in Excel I would pop this formula into a cell...
=MOD(CEILING(22+MONTH(dateserial)-11-1,3)/3,4)+1

How do I drop this into a query where dateserial is the date from the
recordsource?

The quarter is:

DatePart("q",DateSerial(Year([dateserial],Month([dateserial])+1,1)

FYI: dateserial is a poor choice for a field name.