Calculating Fiscal Year Sales in a query

[gmath]

I'm making a query that has the following Fields
Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)

Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date".

I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:

Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013

I can't figure out how to link my criteria back to the "Date" field, or if I'm even going down the write path.

I appreciate all help.

 

[plog]

Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013

You're thinking too short term. You shouldn't calculate FY2013, you should set the query up to calculate the prior FY--whichever FY that is whenever you run it.

To do this, I think you should create a fiscal year function. You pass it a date, it returns the FY that date falls in. With that you can have just one query that gives you the 3 prior FY data, instead of setting up 3 and having to change them every year.

So, step 1 is to create a FY function in a module to turn a date into a FY. Give that a shot and post back what you have.

 

[gmath]

The reasoning behind thinking short term is the database I'm setting up was suppose to be a quick and dirty database for concept. Once I have the layout of what I think I want and need, I will build it so it updates from other databases.

The end result of what I'm looking for is to have a report that shows that my sales for the past fiscal years have been going up or down.

Not sure if I'm ready to write a function for this.

 

[gmath]

I have spent some time considering your suggestion about creating a fiscal year function, just not sure how to implement it. I can't see how to implement that into the query.

I would think to your suggestions I would have a function that set up a fiscal year minus 1 to get the previous, minus 2 to get 2 years ago, etc.

Each record includes an ID, Invoice, Date, Customer Number, Product, Whse, Qty, Sales, Price, Cost, and Customer PO. I am looking to have my final report break the data up by Customer Number, and then Product. On each line for the Product I wanted to have the Product, Whse, Description, On Hand, Unit Cost, Unit Price, LastSale, FY13, FY14, FY15, Total 3yr Sales.

I was thinking that I should be able to set this up through a query and then make a report. Being as I have never generated a Database from scratch, I have only manipulated what others have created, and even with that it has been a couple years since messing around with it. So any help, suggestions would be appreciated.

 

[plog]

I can't see how to implement that into the query.

Can you provide specifics? Table name, field names, function signature (the first line in VBA where you declare it as a function).

Generally its going to look like this in a query:

FY: getFiscalYear([SalesDate])

 

[gmath]

Sure thing, I'm pulling data from 2 tables:
Table: PDZRN1
Fields: ID, Product, Whse, PMgr, PCode, PCat, Description, Unit, Stat, On Hand, On Ord, Unit Cost, Cost Exp, Unit Price, LeadTime, UnitPrcExpDate

Table: Sales_History
Fields: ID, Invoice, Date, Customer Number, Product, Whse, Qty, Sales, Price, Cost, Customer PO.

Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)

I haven't written a Function yet for 2 reasons, I wasn't sure how to pull it once it was written, and I wasn't sure if I should be converting the dd/mm/yyyy to just a fiscal year. With Excel if I wrote a Macro, I would generate a whole column with that data, with Access I wasn't sure I needed to generate another query that converts link the new query and pull the data that way.

 

[plog]

Yes, you will be returning an integer from your fiscal year function (e.g. 2013, 2014, etc).

It's a medium sized road to get where you want. Here's the main stops on the way:

1. Write FY function
2. Use FY function in query (Q1) to put dates into FY
3. Make it a totals query.
4. Create 3 fields in query to determine prior 3 fiscal years.
5. Create final query using Q1 that links all years data together.

Write and test that function and we can move onto #2.

 

[gmath]

Ok so I have a new query FY-SalesHist which has the following fields: Invoice, Product, FY, Date.

I have the relationship between the invoice, now what is your next recommended step into obtaining sales by fiscal year?

 

[plog]

Which of those fields contain the sales? Also, what's the SQL for that query?

 

[gmath]

Sales is located in Sales_History as Sales

 

[plog]

And what field in Sales_History determines the sales date? And whats the signature of your FY function?

 

[gmath]

The sales date is the Date field, and if I think i understand what you mean by the signature of my FY function, I called it GetFiscalYear.

I've taken a screenshot of both the relationship for the query and the function, hopefully that can clarify.

 

[plog]

'Date' is a poor choice for a field name because its a reserved word and will make codying and writing queries difficult. I suggest you rename it by prefixing them with what it represent (e.g. SalesDate).

Also, I just wanted the table with the Sales info in it. Throw away everything else and build a query based off of just it. That means we are at Step 2:

2. Use FY function in query (Q1) to put dates into FY.

So create a query based on Sales History that does that. Bring in only the fields you want to show in your final result.

Post back that SQL. Or, go for extra credit and do Step 3 at the same time (turn it into a totals query). Either way, post your SQL.

 

[gmath]

I was considering changing the "date" field name, that was how it pulled out of the spreadsheet I imported so I left it for the time being. It is changed now.

Here is the SQL, I've also included a screenshot of the design view.

SELECT Sales_History.[Customer Number], Sales_History.Product, Sales_History.Whse, Sum(Sales_History.Sales) AS SumOfSales, GetFiscalYear([SalesDate]) AS FY
FROM Sales_History
GROUP BY Sales_History.[Customer Number], Sales_History.Product, Sales_History.Whse, GetFiscalYear([SalesDate])
ORDER BY Sales_History.[Customer Number], Sales_History.Product;

 

[plog]

Good. Now a little touch up and then step 4:

1. Give the SUM(Sales) fields a name. Change it from Sales to this:

FY_Sales: Sales

Leave it as a Sum underneath. This will just make it easier to work with in the next steps.

2. Next add 3 calculated fields to determine the specific years that precede the record you are on. That means add 3 fields like this to that query:

FY_1: GetFiscalYear([SalesDate])-1
FY_2: GetFiscalYear([SalesDate])-2
FY_3: GetFiscalYear([SalesDate])-3

Underneath each should be 'Group By'. Once done save that query as 'sub_QueryFY'. And post a screenshot like you have been just so I can verify.

 

[gmath]

Ok so I've made the changes recommended, but I must say I'm a little confused. By putting the FY_1: GetFiscalYear([SalesDate])-1, the only thing that happens with the column is it subtracts 1 year from the FY, it doesn't collect all the sales for that fiscal year.

 

[plog]

Looks good and you are right, those fields are what I call 'data hooks', they are an intermediate step which will allow us to link to the relevant data. Here's how that happens.

(I'm assuming that query you just posted is called 'sub_QueryFY')

1. Create a new query and bring that query in twice. The second instance should show its name as 'sub_QueryFY_1'.

2. Link the 2 instances of the query like so:
sub_QueryFY.[Customer Number] to sub_QueryFY_1.[Customer Number]
sub_QueryFY.[Product] to sub_QueryFY_1.[Product]
sub_QueryFY.[Whse] to sub_QueryFY_1.[Whse]
sub_QueryFY.[FY_1] to sub_QueryFY_1.[FY].

That last link is the key, you are linking different FY's there, this will allow you to bring in the prior FY totals.

3. Change all your links to LEFT JOINS. That is show all values from sub_QueryFY and just those that match in sub_QueryFY_1.

4. From sub_QueryFY bring down [Customer Number], [Product], [Whse], FY and FYSales.

5. Bring down FYSales from sub_QueryFY_1 and then change to this to give it a more distinct name:

FYSales_1: FYSales

Run that query and you will have prior year sales for every grouping.

To get the next 2 prior years, you would repeat the above steps...bring in another instance of sub_QueryFY, link appropriately (FY_2 to FY, FY_3 to FY), change to LEFT JOINS, bring down and rename FYSales.


Attached is a screenshot with a simplified subquery demonstrating what you should do.

 

[gmath]

I get a type mismatch in expression. The only thing I can think is that FY_1 is not the same type as FY since the others are the same field.

 

[plog]

Yes, looks like your GetFiscalYear function returns text, then when we do math on FY_1, FY_2 and FY_3 it converts those to numbers.

You can either make your function return an integer or use Cint in your FY field to convert it to an integer after it gets returned.

 

[gmath]

After some tweaking, I took care of the mismatch. I just ended up changing the GetFiscalYear function to return an integer. The query appears to be working just as you had said it would.

Just one more question, and this is probably a simple question that is staring me right in the face, but it looks like FYSales is a total of all my sales, if I want to narrow that to just include the last 3 years, how do I do that where I'm not being "short sighted" and have to go back and change my date range every year.