Results 1 to 3 of 3
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Assign month and week# to dated records based on production cycle rules

    Hello,

    I need help programming the following.
    I have a table that contains my product demand, which looks like this.
    This is saying 20,000 is due by 4th week of July then 20,000 is due by second week of August then 115,000 by third week of August and so on.

    Click image for larger version. 

Name:	1.JPG 
Views:	17 
Size:	48.9 KB 
ID:	42511


    I have another table which contains a set of production rules dictating when a particular item is being typically produced with in the production cycle.

    Item_ID Rule
    ITEM#1 1
    ITEM#1 4
    ITEM#2 1
    ITEM#3 3

    This is saying, item#1 is being produced on week 1 and week 4 of each month, item#2 is being produced on week 1 and item#3 on week 3.

    What I need to do is assign each line record the week and month numbers each must be produced based on the date it is due by, and the rule or rules that apply to each item so it is produced on time.

    The end result should look like this



    Click image for larger version. 

Name:	2.JPG 
Views:	17 
Size:	59.0 KB 
ID:	42515

    Examples:

    If an item is due on week 2 of Sept and we produce it on week 3 of each month, I cannot produce it on week 3 of Sept or it will be late so I need to assign to these records (and all of the same before that date) the production month of August and the week of production 3. The program should select all records with a due dates from prior to the end of week 2 of Sept, assign the production month and week numbers and loop to the end of the table (I get 6 months worth of revolving records).

    If today is July 24th and an item is being produced on week1 of each month, I need to produce on Aug. week1, everything past due and due to end of week1 of Sept.

    It would easily be doable with a select query if only 1 rule applied to each item but some items have 2 rules because we produce them twice a month and I do not have the skills to program that.

    Some items will not have a rule, these should be assigned the same month # they are due on but the prior week #.
    For example if Item#5 is not in the rule table and 10,000 are due 7/10 (past due in that case), plus 10,000 on 8/10 and 10,000 on 9/10, then 15,000 on 10/12, the program should assign the 7/10 + 8/10 lines a production month of 08 and production week of 2 (so it is on time 8/10 which is week3), then should assign the 10/12 record a production date of October week 2 (the week of 10/05).

    Months that have 5 or 6 weeks are treated as following. It is the case for August 8/31 which would be week 6.
    Week 5 becomes 4 of the current month and week 6 becomes week 1 of the following month.
    This is already calculated as such in the table that contains the demand orders, you can see above that the 8/31 records has been assigned a due month of 09 and due week# 1 thus is being manufactured on week4 of August to be on time. Aug 24 and 28 are week 5 and have been assigned week 4 so each month is divided in 4 quarters to coincide with the rules 1 to 4.

    To make matters more complex, if an item has 2 cycles within the month, we won't produce it within the previous cycle. So in the example if ITEM#1 that is produced week 1 and week 4, I do not need to produce week 1 of the following month into the previous week 4 cycle.

    On the other hand if item#1 was only produced once a month on week1, then I would produce in August week 1 all due dates from past due to the end of week September week 1. Then September 1 week production cycle I would produce due dates of Sept week 2 to 4 (and any past due) plus October week 1 (disregard the extra attached images which had errors in that matter).

    TIA.
    Attached Thumbnails Attached Thumbnails 2.JPG   2.JPG   2.JPG  

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what do you consider to be the 1st week of august? 1st to the 7th? So 2nd week is 8th to 14th? Or perhaps 1st and 2nd with week 2 being 3rd to 9th? Or week 1 is the week the first of the month starts in, regardless of the day? Or something else?

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    As Ajax pointed out, week numbering rules must be stated to begin with, as they are country specific! I myself use ISO week definition, where a week starts with Monday (weekday 1), and ends with Sunday (weekday 7). And a week belongs to year, to which it's Thursday belongs into. I haven't found a rule for it, but logically from this follows, that a week belongs to month, to which it's Thursday belongs into too.

    Another thing to consider is, that there is no need for calculate those month and week numbers (btw I'd prefer formats YYYYMM and YYYYWW for them) in your work/business tables. Instead create a table e.g. tbCalendary: CalDate, YYYYMM, YYYYWW, YYYYMMWn... (YYYYMMWn being the number of week in month, like 2020071 for 1st week in July 2020). You enter dates for period into future enough, and then run update queries to calculate month and week numbers (or you do it when entering dates). And because the calendary table is updated not oft, you can have same info in separate fields in different format too (like Wn for week number for month additinally to YYYYMMWn). And then you need in your other table date only - you can always read month and week numbers from calendary table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-24-2018, 12:33 PM
  2. Replies: 26
    Last Post: 11-05-2015, 01:58 PM
  3. 1st week of month as Week 1
    By som_35711 in forum Queries
    Replies: 3
    Last Post: 02-03-2015, 12:43 AM
  4. Replies: 3
    Last Post: 09-19-2013, 10:18 AM
  5. Replies: 2
    Last Post: 08-10-2013, 06:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums