Fiscal Production Week

[Bones_11]

Hello all,

I have a database with a main form (Production Form) which is based on a table that records timings for production processes.

Within this form, I have two fields: ProductionDate and ProductionWeek. I also have a separate MasterDates table (based on DCrake's example table from this very forum), which contains the following two fields: FKDMYDate and FKWeekOfFiscalYear.

What I'm looking to do is have the ProductionWeek field auto-populated with the fiscal week by reference to FKWeekOfFiscalYear in the MasterDates table; after ProductionDate has been entered - by linking ProductionDate to FKDMYDate. For info, Production Date is populated on the form by way of a button which provides the current date when clicked.

I can't figure out how to make this work or what relationships I need to have in place.

I've tried the following so far:

SELECT [MasterDates].[FKWeekOfFiscalYear], [MasterDates].[FKDMYDate] FROM MasterDates;

... which (as expected) only returns a list of the figures held in the FKWeekOfFiscalYear column (i.e. 1 repeated seven times, 2 repeated seven times... etc.).

I've been trying to adapt the above, including the below:

SELECT ProductionDate, ProductionWeek
FROM [Production_Form]
INNER JOIN MasterDates
ON [MasterDates].[FKWeekOfFiscalYear]=[Production_Form].[ProductionWeek]
WHERE [MasterDates].[FKDMYDate]=[Production_Form].[ProductionDate];

... although I can't seem to make it work.

Any help would be massively appreciated, including where to place relevant code (I currently have the above code placed in "Row Source" for ProductionWeek on the form) - as this could also be where I'm going wrong!

Apologies for the long post, although I thought it best to provide as much info as I could. Please let me know if having a sample of the database would help if I'm not making it clear enough.

Cheers.

 

[maw230]

Have you tried to join the two tables and run a Select query to make sure that your Date and Week fields are able to be joined?

After that, once ProductionDate is populated on the form you can write a query to update ProductionWeek as FKWeekOfFiscalYear by using the ProductionDate displayed on the form or in the table if that's the case. You can then trigger the query via the button you're already using or some other event.

 

[Galaxiom]

Are you sure the production week cannot be directly calculated from the date rather than having to look it up in another table?

Access has powerful functions for working with dates and it should be possible to combine them in a custom function to get production week from the date unless there is no repeating logical pattern to definition of the production week.

 

[Bones_11]

Have you tried to join the two tables and run a Select query to make sure that your Date and Week fields are able to be joined?

After that, once ProductionDate is populated on the form you can write a query to update ProductionWeek as FKWeekOfFiscalYear by using the ProductionDate displayed on the form or in the table if that's the case. You can then trigger the query via the button you're already using or some other event.

Thanks for your reply.

I've tried various joins, although I think my failure to achieve what I require is more down to a lack of knowledge than anything else.

I have written a query already:

SELECT [Production Form].ProductionDate, DatePart("ww",[ProductionDate]) AS ProductionWeek
FROM [Production Form];

... although this returns the week of the calendar year (as I expect). Unsure how to adapt this.
Could you help with how I would go about implementing your suggestion as it sounds fairly simple although I'm unsure how to proceed?

 

[Bones_11]

Are you sure the production week cannot be directly calculated from the date rather than having to look it up in another table?

Access has powerful functions for working with dates and it should be possible to combine them in a custom function to get production week from the date unless there is no repeating logical pattern to definition of the production week.

Thanks for the response Galaxiom.

I've done a bit of research into this, and tried to modify examples I've seen, although I can't find anything that works for my particular fiscal year (beginning 1st July every year). See my previous post for the basic DatePart function I have. Any ideas on how I would adapt this for years beginning 1st July?

I'm sure there's a way of doing it via a function, although I'm not too clued up on the ways of going about doing this as regards custom functions. I also assumed it would be better to refer to a set of data from another table rather than rely on a function to calculate this for me, but at this moment I'm happy to try anything that might work!

 

[maw230]

I think you would want to join the two tables on the Date fields and then pull in your FiscalWeek.
Galaxiom's suggestion is probably the preferred method here. If the fiscal year follows some sort of repeating logic you should be able to write a function.

 

[Galaxiom]

Does this function give the right results?
Put it in a standard module and call it from anywhere in the project including queries.

Public Function ProdWeek(dte As Date) As Integer
 
Dim Day1 As Date
 
    Day1 = DateSerial(Year(dte), 7, 1)
    If dte < Day1 Then Day1 = DateSerial(Year(dte) - 1, 7, 1)
    ProdWeek = DateDiff("w", Day1, dte) + 1
 
End Function

 

[Bones_11]

Does this function give the right results?
Put it in a standard module and call it from anywhere in the project including queries.

Public Function ProdWeek(dte As Date) As Integer
 
Dim Day1 As Date
 
    Day1 = DateSerial(Year(dte), 7, 1)
    If dte < Day1 Then Day1 = DateSerial(Year(dte) - 1, 7, 1)
    ProdWeek = DateDiff("w", Day1, dte) + 1
 
End Function

Thanks for taking the time to create this Galaxiom, it looks like it could work. Question is, where/how exactly do I place this function in my form? I've tried entering your code above verbatim into both the Before Update event of the ProductionWeek field on the form, and in the ProductionWeek for query, but can't seem to get it to work!

 

[pr2-eugin]

Thanks for taking the time to create this Galaxiom, it looks like it could work. Question is, where/how exactly do I place this function in my form?

I think Galaxiom has the answer in his post.

Put it in a standard module and call it from anywhere in the project including queries.

You can use the code as,

SELECT [Production Form].ProductionDate, [COLOR=Red][B]ProdWeek([Production Form].ProductionDate)[/B][/COLOR] AS ProductionWeek
FROM [Production Form];

 

[Bones_11]

I think Galaxiom has the answer in his post.

<image>

You can use the code as,

SELECT [Production Form].ProductionDate, [COLOR=red][B]ProdWeek([Production Form].ProductionDate)[/B][/COLOR] AS ProductionWeek
FROM [Production Form];

I got it working in the query! Many thanks for the step-by-step, I now have a grasp on the process. Thanks again also to Galaxiom for the code!

 

[pr2-eugin]

Glad you have it working ! We are happy to help. Good Luck