Using a Calculation to Relate Tables?

[Jeff Lehman via AccessMonster.com]

I am designing an application that will track the movement of production
items through our plant. Our order writing system already exists in Access
and I am designing a table that will link to the system. This new table will
primarily contain date/time information that will show when a item enters and
departs a certain department within the plant

I want to link the table with a unique value...so I want to use the order
number and product ID number concantentated together to form that unique
number. This unique number will be called a LineNumber. I have read through
a lot of posts on here that explain that its bad design to store data that
you can create during run-time as needed, but I am having trouble figuring
out how to use the LineNumber to relate the tables.

The tables are as follows (All of the tables already exist except for the
first one):

TrackingInfo - Tracks date/time and dept info to show where a certain item is.

OrderDetailInfo - Currently in our order system...contains line item that is
on that order
ProductTable - Listing of all of our products (linked to orderDetailInfo
table)
OrderTable - Contains Customer ID/Order Date etc...

My explanation may be a bit too generic, but I need a push in the right
direction on what would be the best way to relate the tables .... Thanks!

 

[Peter's Software]

The OrderDetailInfo table should already have a unique primary key so
why not just use that to link on?

Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com

 

[Jeff Lehman via AccessMonster.com]

I guess I am overemphasizing the importance of the LineNumber in my mind, but
I didnt express it well in my posting. I want to use the line number field
as a lookup value when a Dept Head pulls up a item to input the status of it..
...would that have an effect on your advice?

Thanks!
Jeff

 

[Vincent Johns]

I guess I am overemphasizing the importance of the LineNumber in my mind, but
I didnt express it well in my posting. I want to use the line number field
as a lookup value when a Dept Head pulls up a item to input the status of it..
..would that have an effect on your advice?

Thanks!
Jeff

Peter's advice is what I too would suggest, but I would also set up a
query to link the two values. Actually, the query could link the
primary key for [OrderDetailInfo] not only to your LineNumber value, but
also to any similar value showing order number, customer's name,
product ID, product name.... there are lots of possibilities. I assume
that the LineNumber shows the same info that the Dept. Heads use now, is
that correct?

Anyway, my suggested query would look something like this:

SELECT [OrderDetailInfo].[OrderDetailInfoID], [OrderNumber] & "-" &
[ProductID] AS LineNumber
FROM [OrderDetailInfo] ORDER BY [OrderDetailInfo].[DueDate];

In your new [TrackingInfo] table, you could set up a Lookup field based
on this query, or you could set up a form with a list box based on the
query. Display only the 2nd ([LineNumber]) field, but store the value
of the 1st ([OrderDetailInfoID]) field into the record.

If you do this, you will be able to change the appearance of the
list-box choices at a later time, if you need to, without affecting any
of the contents of the tables.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.