constants vary by date

[Mark Cline]

I have a database where the efficiencies of different assembly lines are
calculated. These calculations depend on the amount of time it takes for the
assembly line to produce one unit. I have a table where the "process time"
(the amount of time it takes to produce 1 unit at 100% efficiency) for each
assembly line is stored. These process times will change over time as new
equipment or processes are introduced to increase production. Here's where
the problem lies...

Daily, weekly, and monthly summaries are made showing the line efficiencies.
I need data that was entered in the past to continue to be connected with
its process time while new data needs to be connected with new process times.
In some instances, results of calculations using both old process times and
new process times need to be shown on the same report. What is the best way
to go about doing this? Thanks in advance.

 

[Jeff Boyce]

Mark

If you are saying you need to keep both the old process times and any new
process time(s), then you'll have to store both the old and the new process
times.

You can use a pair of date fields to get a date range for the process times
(from DateA to DateB).

If you use a primary key for the process time record, you can store that
key/ID and always point back to the process time that was in effect when
that particular record was written.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Wickerath]

Hi Mark,

This sounds like a case where you need to save historical data. A couple of
examples can be found in the Northwind sample database (Northwind.mdb). If
you click on Tools > Relationships in this database, you will see that
UnitPrice is being stored in both the Products table and the Order Details
table. Likewise, the Customer details (ShipName, ShipAddress, ShipCity, etc.)
are being stored in the Orders table, as well as the Customers table (albeit
different field names).

The UnitPrice data stored in the Order Details table, and the Customer
information stored in the Orders table represents the current values at that
point in time, when the record was created. Later on, if the price is
increased (UnitPrice in the Products table), or a customer changes an
address, a report of past sales will represent the information that was
correct at the time the order was placed. You can store your efficiency
values in a similar manner; ie. in a separate table as you currently are to
represent the efficiency today, and with each record in a related table to
store the efficiency value that was in effect at the time the record was
entered.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Mark Cline]

The only problem with that is we won't know how long a process time will be
in effect so we won't be able to input an ending date. Is there a way to get
it to know that the ending date is the date of the newest entry for a
particular line? Also, how would I go about setting this all up. I have
everything already set up to run if you ignore the changing process times so
doing this with minimal change would be preferred. Thanks.

 

[Jeff Boyce]

Mark

You wouldn't have to "fill in" an end-date to use the record. In fact, you
could consider that any process row without an end-date is "current", while
those with end-dates are "historical".

If you create a query that looks for process rows where the end-date is null
(criterion), you should get the current ones.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mark Cline]

I'm doing my data entry via webpages. Is there a way to get the code
required to do the unit price lookup and still have the web based data entry?

 

[Tom Wickerath]

Hi Mark,

I would think the appropriate INSERT query could select the unitprice from
the applicable table and insert that value into the detail table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________