Queries & Fiscal weeks

[Jul B.]

I inherited a weekly report that shows the total spend by commodity for each
fiscal week. I've been asked to report the fiscal weeks along with the total
month to date figures, which sounded simple. However, the trouble occurs
when a fiscal week is split between 2 months. In the past, an append query
posted the totals for each commodity to the corresponding FW and that's what
was copied & pasted into an email. I have created an additional query to
pull the MTD figures but am struggling on how can I easily report this info
splitting the FW into 2 separate months? I have until the end of the
month....help!

 

[Jeff Boyce]

Define "month-to-date" ...

In my world, "month-to-date" means from the first day of the month to the
current date (in the month). It wouldn't matter (again, in my world)
whether "fiscal weeks" were being reported or not.

And "how" will depend on "what" -- what does your data structure look like?

But maybe we don't live in the same worlds...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jul B.]

Jeff-
We are in the same world, at least regarding month to date figures. I'm
trying not to totally upset the apple cart in my department since I'm taking
over for someone who created this database...also trying not to re-create the
wheel, either. So, there is a macro which pulls all of the spend data by
commodity and posts it to a table - this info was copied from the table &
communicated previously via a weekly email update. The table looks similar
to below:

FW Commodity 1 Spend$ Commodity 2 Spend $ Commodity 3 Spend $ etc
1 XXXXXX XXXXXX
XXXXXX
2 XXXXXX XXXXXX
XXXXXX
3 XXXXXX XXXXXX
XXXXXX
4 XXXXXX XXXXXX
XXXXXX
5 XXXXXX XXXXXX
XXXXXX

The issue now is I'm trying to run several reports to get the requested info
- the FW is necessary (for whatever reason) in addition to the MTD which
would vary based on where the end of the month falls, i.e. FW35 has data for
Aug & Sep.

Currently, I'm running the previous report along with another query I
created for the MTD figures (9/1-9/14) and trying to match up figures for the
previous month for the same FW. For instance, FW 35 has data for 8/30 & 8/31
so I have to back out those figures from that FW total. I'd like to be able
to somehow break out the totals into separate months with their respective
totals by fiscal week and MTD...sort of like below:

FW Month Commodity 1 Spend$ Commodity 2 Spend $ Total MTD
35 Aug XXXXXX XXXXXX
XXXXX

35 Sep XXXXXX XXXXXX
XXXXX
36 Sep XXXXXX XXXXXX
XXXXX
37 Sep XXXXXX XXXXXX
XXXXX
38 Sep XXXXXX XXXXXX
XXXXX
39 Sep XXXXXX XXXXXX
XXXXX

39 Oct XXXXXX XXXXXX
XXXXX

I just don't know how to correspond the FW to different months. Thanks for
your help!

 

[Jeff Boyce]

It was my intent to suggest that month-to-date is NOT related to FW. After
all, if we measured today, month-to-date would be to/through 9/14/2009,
regardless of how FW was being calculated, right? So I'm checking again ...
are we using the same definition of "month-to-date"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mark Andrews]

If you are doing lots of fiscal based calculations sometimes it makes sense
to use a data warehousing technique of a time dimension table. Basically
that's just a table with a date (one day at a time) as the key with various
columns such as (month, day, year, fiscal week, fiscal month, etc...). Then
you can just join this table into any query you have and you get the fiscal
week. So it easily distingusihes that records are in fiscal week 5 but
sometimes in fiscal month 1 and sometimes in fiscal month 2.

Of course you have to make sure you time dimension table always covers all
the dates you have in the database, and you always strip off the time when
joining etc.... You should be able to find code to generate a time
dimension table.

If you only have a little fiscal math you can always just calculate it on
the fly as well.

My two cents,
Mark
RPT Software
http://www.rptsoftware.com

 

[Jeff Boyce]

Mark

I'll offer a related wrinkle to your data warehouse technique ...

If the existing data structure (not sure I understand it yet) can
accommodate this, a query could be used to generate the records you
describe, and then serve as the "table" you mentioned. No actual physical
table may need to be created.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jul B.]

Thanks for the responses. The query I inherited and run each Monday, pulls
purchase order receipts for the previous week. The query updates a table
with the qty & dollar figure by commodity. So the fiscal week is already
available - I just need to be able to split it out by date.

A report/chart is generated comparing the monthly spend & trend compared to
last year by fiscal week. I've just been recently asked to add the actual
MTD receipts along with the fiscal week info. So, on a weekly basis, I've
been running the query/report/chart for the FW data, then running a query I
created to capture the MTD receipts & tabulating in Excel. In the meantime,
I'll have to run the figures for split weeks separately. For example, run
the receipts for 8/30 & 8/31, tally them to the appropriate FW35 in an Excel
spreadsheet, then run the receipts for 9/1-9/3 & post to another column in
Excel.

I am probably, at best, dangerous in Access - not a beginner, but not
well-versed in VBA, etc. I really appreciate your help!

 

[Jeff Boyce]

I'll try once more ...

DON'T use the fiscal week query.

Create a new query that pulls by date to get your new "month-to-date"
results.

Regards

Jeff Boyce
Microsoft Office/Access MVP