Fiscal year (another try)

[DIH]

First off, let me say that the database I'm dealing with was created a
long while ago by someone who did not really follow proper database
design and now has been trust upon me. Table names have spaces (not a
huge deal - but still), the other large error is the use of the key word
"DATE" as a table field. With that said, here is a quick recap and my
question:

I previously posted that my company is now using a 4 week, 4 week, 5
week fiscal calendar. This will make a month (say October) not start on
the 1st and end on the 31st.

What I need to do is fix all the month to date reports/charts. I have a
form called "frmDailyProdReport" that has a calendar control in it that
the user selects a date from and the report will show the month to that
date figures (e.g. the user clicks 10/23/08 and the report shows data
from 10/1/08 thru 10/23/08).This is all pretty normal stuff for me.
Unfortunately now, the start of the month is no longer on the 1st. I
have created a table called, "tblFiscal2009LookUp" that holds the fiscal
year info.Its fields are: DayOfYear (10/1/08, 10/2/08 etc), WeekOfYear
(1, 2, 3 etc), MonthOfYear (1, 2, 3 etc), MonthName (Oct, Nov, Dec etc),
QuarterOfYear (1,2,3,4) and FiscalYear (2009).

The main table in the db is called, "DAILY SHIFT". This table contains
the date, how many units produced, downtime minutes, code number and
many other metrics.

I joined tblFiscal2009LookUp to "Daily Shift" with the date field. Here
is the sql:

SELECT [DAILY SHIFT].DATE, [DAILY SHIFT].LINE, [DAILY SHIFT].CODE,
[DAILY SHIFT].THEORETICAL, [DAILY SHIFT].ACTUAL, [DAILY SHIFT].DT,
tblFiscal2009LookUp.DayOfYear, tblFiscal2009LookUp.WeekOfYear,
tblFiscal2009LookUp.MonthOfYear, tblFiscal2009LookUp.MonthName,
tblFiscal2009LookUp.QuarterOfYear, tblFiscal2009LookUp.FiscalYear
FROM [DAILY SHIFT] INNER JOIN tblFiscal2009LookUp ON [DAILY SHIFT].DATE
= tblFiscal2009LookUp.DayOfYear;

The result of this query simply shows all the records in the DAILY SHIFT
table with the addition of the fiscal dates data from the
tblFiscal2009LookUp table.

Again, the control on the form is called,
[forms]![frmDailyProdReport]![cboStartDate].

So... What I need is a query (or at least the WHERE or HAVING sql
statement)that when the user selects a date from the calendar control,
the results will be only the records in that particular fiscal month to
date. I can then do the summing stuff.

Thanks to John W. Vinson [MVP] for getting me started (from my previous
post titled "Fiscal Year" - posted 11/17/08) on what I believe is the
right track by using a calendar translation table.

Thanks again in advance for any help,

Dave

[Marshall Barton]

DIH wrote:

I suggest that you change the combo box's RowSource query to
also join to the fiscal lookup table and include the needed
fields. They do not need to be visible in the drop list,
but you can retrieve them for the criteria by using
something like:

txtWOY = Me.cboStartDate. Columns(x)

Then your query can use criteria like:

WHERE
blFiscal2009LookUp.WeekOfYear=Forms!frmDailyProdReport.txtWOY

--
Marsh
MVP [MS Access]

[DIH]

I think I made a mistake and caused some confusion. There is no combo
box (only the **name** of the text box on the form is called
"cbostartdate" (the name of the text box really should be
txtstartedate). After the user selects a date from the calendar control,
that date is simply put into the text box called (confusingly)
"cbostartedate".

Bottom line is I somehow need to extract the time period from my fiscal
lookup table from the date the user selects in the form.

Many thanks and sorry for the confusion,

Dave

[Marshall Barton]

I don't see where you explained what "the time period" is.
If it is just one field in tblFiscal2009LookUp, then you can
use DLookup to retrieve it. If you need more than one field
to figure out "the time period", then open a recordset to
get a record from the table:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * " _
& "FROM tblFiscal2009LookUp " _
& "WHERE DayOfYear=" & Format(cbostartdate,
"\#yyyy-m-d\#")
txtWOY = rs!WeekOfYear
. . .
rs.Close : Set rs = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]

IH wrote:
>I think I made a mistake and caused some confusion. There is no combo
>box (only the **name** of the text box on the form is called
>"cbostartdate" (the name of the text box really should be
>txtstartedate). After the user selects a date from the calendar control,
>that date is simply put into the text box called (confusingly)
>"cbostartedate".
>
>Bottom line is I somehow need to extract the time period from my fiscal
>lookup table from the date the user selects in the form.
>
>

[DIH]

The time period would be from the date the user selects from the
calendar back to the beginning of that fiscal month (not the 1st of the
month as is the normal way of doing month to date).
For example: Fiscal Nov. 2009 (because of the unique way the company
wants to track months)is from 10/26/08 thru 11/22/08. So (I'll pick a
future date to be more obvious) when the user selects say 11/15/08, the
query will return (or sum where needed) records from 10/26/08 thru
11/15/08 (this would be fiscal Nov. month to date - see the table
below). It would know how to get only the dates in the fiscal month by
use of the translation table below. That's the issue, I don't know how
to use the translation table to lookup the dates needed.

Here is a portion of the lookup table called, "tblFiscal2009LookUp" :
Note: Our fiscal year starts in Oct which is why Nov "MonthOfYear is 2
So again, if the user selected 11/15/08 from the calendar control, the
query would see that that month is "2" and then return all the dates
with a month of 2 back to the first day with the month of 2. The other
fields besides "DayOfYear and "MonthOfYear" are also not necessarily
needed but I added them in anyway for future reference.

Basically, our company's months do not simply start on the 1st and end
on the last day of the month. They are all skewed by a number of days.

DayOfYear WeekOfYear MonthOfYear MonthName Quarter

10/26/2008 5 2 Nov 1
10/27/2008 5 2 Nov 1
10/28/2008 5 2 Nov 1
10/29/2008 5 2 Nov 1
10/30/2008 5 2 Nov 1
10/31/2008 5 2 Nov 1
11/1/2008 5 2 Nov 1
11/2/2008 6 2 Nov 1
11/3/2008 6 2 Nov 1
11/4/2008 6 2 Nov 1
11/5/2008 6 2 Nov 1
11/6/2008 6 2 Nov 1
11/7/2008 6 2 Nov 1
11/8/2008 6 2 Nov 1
11/9/2008 7 2 Nov 1
11/10/2008 7 2 Nov 1
11/11/2008 7 2 Nov 1
11/12/2008 7 2 Nov 1
11/13/2008 7 2 Nov 1
11/14/2008 7 2 Nov 1
11/15/2008 7 2 Nov 1
11/16/2008 8 2 Nov 1
11/17/2008 8 2 Nov 1
11/18/2008 8 2 Nov 1
11/19/2008 8 2 Nov 1
11/20/2008 8 2 Nov 1
11/21/2008 8 2 Nov 1
11/22/2008 8 2 Nov 1

I know this all seems a bit crazy of my company, but they have their
reasons!

Thanks for bearing with me on this rather long winded post.

Dave

[Marshall Barton]

Hmmm, you want the criteria start date text box to be the
earliest date that has the same month as cbostartdate. Use
this kind of code in the same place that sets cbostartdate:

intMonthNum = DLookup("MonthOfYear", _
"tblFiscal2009LookUp", _
"DayOfYear=" & Format(cbostartdate, "\#yyyy-m-d\#"))
Me.txtStartDate = DMin("DayOfYear", _
"tblFiscal2009LookUp", _
"MonthOfYear=" & intMonthNum)

Then the query date field can use criteria like:

Between Forms!frmDailyProdReport.txtStartDate
And Forms!frmDailyProdReport.cbostartdate

--
Marsh
MVP [MS Access]

[DIH]

Oh man thank you, thank, thank you!!!
That works perfectly. I truly appreciate you staying with me on this.
Now I know why you guys are called, "MVP's"
Awesome job,

Dave