asked on

access query fiscal year

How do I modify the below query to select data from the current and last fiscal year instead of calendar year?

SELECT DISTINCT t1.qProductCategory,
(SELECT SUM(t2.qQuantity)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
Year(t2.qDate) = Year(now) -1 ) AS Pre1_Qty,
(SELECT SUM(t2.qQValue)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
Year(t2.qDate) = Year(now) -1 ) AS Pre1_Value,
(SELECT SUM(t2.qQuantity)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
Year(t2.qDate) = Year(now)) AS Now_Qty,
(SELECT SUM(t2.qQValue)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
Year(t2.qDate) = Year(now)) AS Now_Value

FROM YourTable AS t1

Jim Horn

Define 'fiscal year' for us. You'll likely need to populate a table of months (weeks, days) so that Access knows as well.

GRayL

If you mean like the US DoD FY Oct to Sep?

GRayL

Try this:

SELECT DISTINCT t1.qProductCategory,
(SELECT SUM(t2.qQuantity)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
iif(Month(qdate)<10,Year(qdate),Year(qdate)+1) =
iif(Month(date)<10,Year(date),Year(date)+1) -1 ) AS Pre1_Qty,
(SELECT SUM(t2.qQValue)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
iif(Month(qdate)<10,Year(qdate),Year(qdate)+1) =
iif(Month(date)<10,Year(date),Year(date)+1) -1 ) AS Pre1_Value,
(SELECT SUM(t2.qQuantity)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
iif(Month(qdate)<10,Year(qdate),Year(qdate)+1) =
iif(Month(date)<10,Year(date),Year(date)+1)) AS Now_Qty,
(SELECT SUM(t2.qQValue)

FROM YourTable AS t2
WHERE t2.qProductCategory = t1.qProductCategory AND
iif(Month(qdate)<10,Year(qdate),Year(qdate)+1) =
iif(Month(date)<10,Year(date),Year(date)+1)) AS Now_Value

FROM YourTable AS t1;

GRayL

Hi Jim.

Kurt4949

ASKER

fiscal year means the year ends in lets say November 31st instead of December 31st. I haven't tried your solution GRayL but I will later today. Could you briefly explain whats happening there?

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial