Results 1 to 5 of 5
  1. #1
    OCStan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    15

    Most recent date

    I have a form used for manufacturing scheduling. There is a field with the structure name and five fields with dates of parts manufactured. I would like to add a field that would show the date of the last part made which would indicate when the structure is complete. Here are the field names [structure] [date_1] [date_2] [date_3] [date_4] [date_5] [last_date]. Any ideas on what code to add to [last_date] in order to show the most recent date in fields 1 thru 5? I am a novice at this so any help would be appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should research and practice Normalization.
    Fields with names like
    [date_1] [date_2] [date_3] [date_4] [date_5] [last_date]
    it indicates non-normalized structure.
    Are these the real field names?
    Can you tell us more about the business processes involved? And what each of the dates represents?
    Perhaps you could give us a few examples to help us understand your situation.

    Good luck.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    first you need to understand that databases are not a large excel - suggest google normalisation to understand the basics - excel is not normalised and you have an excel type structure to your table. Excel shows data and presentation in one view, databases store data in tables and uses queries, forms and reports for presentation.

    So your table should look something like

    Structure Part CompletedDate
    1.............5....01/01/2016
    1.............6....02/01/2016
    etc

    which is then easy to find the latest date in a query

    SELECT Structure, max(CompletedDate) as latestDate
    FROM myTable
    GROUP BY Structure

    for your current data structure you will need some complex vba to parse through the different fields to determine which is the highest value.

  4. #4
    OCStan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    15
    I am manufacturing containers which may have 1 to 5 parts to complete the container. I have dates for each part made and would like to know when the last part is made in order to schedule shipping the complete container. Example - the base is made on 10/5/16, the walls are made on 10/12/16, the lid is made on 10/1/16, the last part made on 10/12/16.
    The date field names are SP_1, SR_2, SR_3, SR_4, SR_5.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am manufacturing containers which may have 1 to 5 parts to complete the container.
    So a container may be composed of 1 to 5 parts?
    Or, a given container may be in 1 to 5 completion statuses?

    It sounds like a Complete container consists of 5 parts (base, walls,lid).
    It also sounds like different parts can be made on different days/dates.
    There is no indication of assembly/welding parts to be a completed container.
    Are all containers the same size?

    Seems like the basic issue is related to identifying the shipping date for a completed container???

    I would expect that when you are in the operational stage of a business that your production schedule is quite well known.
    For example, time to build a base, time to build walls, time to build lid have nominal values. Depending on the resources, (people, equipment and material) the probable time to build a completed container would/could be estimated. And the probable ShipmentDate could be estimated.

    I'd like to see more description of the processes involved. It just doesn't ring right that you can't set up a shipping schedule until the last part is physically built.

    Now you know your business much better than any reader, so perhaps some detail will get us all on the same page.

    Sounds like this sort of series of processes is involved: (I have purposely left out Customer and Shipper)

    ReceiveOrder-->GetMaterials--->Buildparts---->Assemble/Complete Container-->Inspect/Review--->Ship
    Good luck with your project.

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

Similar Threads

  1. Query for most recent date
    By benjamin.m.winchester in forum Queries
    Replies: 3
    Last Post: 12-22-2015, 01:33 PM
  2. Replies: 1
    Last Post: 10-02-2015, 12:28 PM
  3. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  4. exclude the most recent date
    By crowegreg in forum Queries
    Replies: 2
    Last Post: 10-22-2013, 09:53 AM
  5. Get Recent Record without Id or date
    By pyog in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 09:43 AM

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