Access - Using the Value - Date - to follow another record. In query calculation

[Steven Mar]

Good day

I have been battling to get something working for a while now, I am no expert and cant find a solution anywhere at this point.

I have only been using the Start Date and Defacto Start Date to get the dates, but if there is an early delivery or any delay then the dates are essentially inaccurate, and having to type in the information everyday for all the parts will take too long.

If you look at the attachment, you will see the table and query that I am using, it is just a mock up of what I am trying to achieve.

My goal is for the progression to follow one another, but they cannot overlap or cross.
Parts have to go through stages 1 - 10, each stage stage takes 3 days unless there is a hold up or a different paint layout used in stage 5 which makes it a 4 day stage.
The parts are supposed to arrive 3 days after the previous one, if not the start date will have the new date in - which allows for delays to be taken into account -
and every 3rd or 4th day all the parts effectively move one stage forward, where possible.

Keeping accurate dates is important especially when a customer wants an accurate delivery date. with the amount of parts passing through it is difficult to tell exactly. Especially when having to swap parts around for preference purposes, or delays for one part over another.

Having to swap out parts with another will also have to be taken into account, as delays are inevitable.

If I am looking at this the wrong way please let me know. I would love to get this working, the way I imagined.
I appreciate any response to this.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[jdraw]

Welcome to AWF!
Initial thought is you are dealing with Parts and Stages and your tables relationships should start with that. In addition each stage activity has an expected duration. Since in your processing you say "keeping accurate dates is important", then it may be that you need expected dates and actual dates within each stage for each part. That would seem to provide info for detail date/duration statistics.
You may wish to review normalization in order to get your tables and relationships set up and tested. Getting your tables and relationships designed to support your requirement is a critical step. It seems to me you are using a table structure as an interface. Better to get your tables designed and tested with test data, and use a form or forms as the user interface to the database.

 

[Steven Mar]

Thank you for the speedy reply,

My first thought was to make a separate table for the stages, but abandoned the idea as stage 5 does vary.
also if I had to put expected dates and actual dates I wont be reaching the goal point because then it would require a daily entry.

It will also have need to follow on each other which does require the ability to get the date from the previous record from the stage it will be moving to( which may change based off of the delays or changes made), that seems to be my biggest challenge.

I need the output in two different ways, which will be graphically, showing which part is in which stage(that is current so every third/fourth day of viewing this you will be able to see exactly what is where) and then in table format showing not only current but the projected completions and delivery dates.

The table I attached was just a quick draw up to give a rough idea of what I currently have.
If I am looking at it from the wrong angle, please I would not turn away a different view on it. That is how people learn.
Thank you.

 

[theDBguy]

Hi. Welcome to AWF!

I have moved your thread out of the Introduction forum. You can still post an introduction there, if you like.

 

[plog]

First, use only alphanumeric characters in table, query and field names. No spaces, no hashes, only numbers and letters. Just makes coding and querying easier down the road. [Defacto Start Date] - > [DefactoStartDate], [Part#] -> [PartNum]

Then, I would use a stage table. It would simply have 1 numeric field and 10 records--one for each number 1-10. Next I would create a function in a module to determine the StageDate. You would pass that function DefactoStartDate, StartDate, PaintLayout and StageNumber, it would do the logic necessary and return the date needed based on all values it is passed.

Then for your query to get all stages for all parts this would be the SQL:

SELECT PartNum, DefactoStartDate, StartDate, PaintLayout, StageNumber, get_StageDate(DefactoStartDate, StartDate, PaintLayout, StageNumber,)
FROM Progression, tblStages

 

[Pat Hartman]

also if I had to put expected dates and actual dates I wont be reaching the goal point because then it would require a daily entry.

You need a table that defines the stages and how long each normally takes.
I presume, you need to make some update to have the item progress from one stage to the next. If you don't how would the app know the current stage is complete?
When a stage is marked complete, you get the next stage from the query and calculate its end date based on the expected duration. Then you can run a query daily that lists all items that have passed their expected completion date but have not been marked with a complete date.

In addition to the table that defines the stages, you need a child table to log the stage progress for each item.

 

[Steven Mar]

Good day

Thank you for your responses

I will definitely make those changes and not use spaces going forward.

The Table with the stages are easy, how do I get the records to follow on one another?

Also How would I avoid a daily entry though, it would be tedious.

Thank you

 

[Pat Hartman]

In a relational database, records have no defined order. Tables and queries are unordered sets. Think of a bag of marbles. The ONLY way to provide a specified sequence is to use a query with an order by clause that orders on a unique value. If the fields you sort on do not provide a unique value, then the data will be sequenced by those fields but the "duplicate" details could be in different orders at different times.

What happens when a stage is complete? Does someone mark the record? Or are you expecting something to happen automagically? Automagically is doable but that would require that you update the items that are late and define some rules for handling them subsequently.

As Doc says most eloquently, if you can't tell us how a process works with precision using standard language, there is no way we can help you to program the process because computers may be faster than a speeding bullet but they are dumb as rocks.

 

[jdraw]

Also How would I avoid a daily entry though, it would be tedious.

What kind of report do you want/need to control/manage workflow? Have you mocked up some sort of report?
Depending on the details you need from "the database", you will have to set up some forms and/or procedures to ensure data is captured to allow the necessary report(s). ...delays, swaps, customer inquiries, product status..etc.