1st time Access Database

[Beetle]

I might agree with *some* of what Steve said in his last post, depending
upon interpretation.

First, regarding your question about the PK/FK fields. When I read that
the first time my interpretation was that you were just asking what the
purpose of the FK field is. If you were actually asking whether the field
names
are required to be the same, then Steve is correct. They don't have to
have the same name, but they typically do just for clarity.

Additionally, in the table structure that he suggested he included a table
(tblProducts) to store information about the different product types
(silk screens, etc.). After re-reading the thread, I see that you did
mention previously that you want to keep track of this info, so you would want
to include a table like this in your structure.

However, I disagree with the structure of the TblCustomerEndProduct that
he suggested. His suggestion was;

TblCustomerEndProduct
*****************
CustomerEndProductID
CustomerID
ProductID
DrawingNumber
Revision
DashNumber,
Serilized(Yes/No)
FileCabinetDrawerID

Let's suppose you have a drawing with 10 revisions. With the above table
structure, you would have to enter the same drawing number 10 times, along
with different revision numbers, filing cabinet drawers, etc.

In the junction table that I suggested (modified to include a FK to
tblProducts);

tblCustomerDrawings
****************
CustomerID (FK to tblCustomers)
ProductID (FK to tblProducts)
RevisionID (FK to tblRevisions)
DateProduced

All you would need to do is enter a particular RevisionID (which, in this
case,
would typically be done via a combo box). All other data related to that
RevisionID (Drawing, File Cabinet Drawer, etc.) could be determined by
a simple query.

Again, this is based on my assumption that a Drawing can have multiple
Revisions, and each Revision could be stored in a different location,
therefore it is really the Revisions that you need to keep track of.

 

[harris128]

Thanks guys for all your help. One more point. I want to keep all the
revisions of a drawing in the same location (by customer). Normally, if the
customer sends me a new revision of the drawing, it usually replaces the
present revision. I would keep the previous revs for historical purposes to
refer back to, if need be.

I say, by customer, because I can have the same drawing for two customers,
but each customer may be building parts to different revs of that drawing.
Make sense?

harris128