One table, three fields; two lookup fields and one result field

[gaker10]

No I did not start with a specification as you defined it.



I did not know the dangers of multi-value fields until I started using them. They did exactly what I needed with the exception of trying to import them from a linked excel, which is nearly impossible without complicated code.

So is there any way for me to normalize/fix the database at this point? Or even solve my problem?

[June7]

Normalization would be like:

tblDrawings
DrawingID (PK)
DrawingDesc

tblDrawingRevisions
DrawingID (FK)
RevDate
Comment

tblDrawingParts
DrawingID (FK)
PartID
IsPrimary (yes/no field)

tblDrawingCageCode
DrawingID (FK)
Code

Fixing structure without destroying data will be tricky but doable.

[gaker10]

In this:

tblDrawingParts
DrawingID (FK)
PartID
IsPrimary (yes/no field)

What If I want to limit PartID to entries of a field of another table (E.g., a PartID table)? For the purpose of reducing typos.

Also, it appears that this normalization basically eliminates the need of several fields in one table. However, this makes it difficult to modify more than one field associated with the ID. Am I correct? This seems like the kind of question that has been asked before, so I am sure there is a way to modify several related peices of information based on one ID. For example, changing the cage code, revision, and partID for one drawingID (as per the structure in Post #17).

Also, if I have three or four cage codes for one ID, would each cage code be in its own table? Or all in one table?

[June7]

Use a combobox and set LimitToList property to yes.

My suggested structure was very rough because I don't really understand what all the entities are and how they are related. However, inclined to say one table for all associated cage codes.

[gaker10]

And if the cage code is dependent upon the DrawingID, Revision, and PartID simultaneously? How would that look?

[June7]

That's getting really complicated. Multiple cage codes for each combination?

Add unique ID fields into tblDrawingRev and tblDrawingParts.

Maybe another junction table

tblRevParts
ID
RevID
DrawingPartsID

tblCage
RevPartsID
Code

[gaker10]

I'm in a very complicated sort of industry, hehe.

Yes, cage codes are similar to vendors. We can have many vendors for one drawing/revision/partID combo.

Also, how would you update different information for one drawingID simultaneously?

[June7]

I don't understand the question. What do you mean by 'update'? Update what information?

[gaker10]

For example, based on this structure:

tblDrawings
DrawingID (PK)
DrawingDesc

tblDrawingRevisions
DrawingID (FK)
Rev
Comment

tblDrawingParts
DrawingID (FK)
PartID
IsPrimary (yes/no field)

tblDrawingCageCode
DrawingID (FK)
Code

Updating/modifying the information for the Rev, IsPrimary, and Code simultaneously?

Going through this, it seems that the data for my database has a lot of many-to-many relationships. I'm not even sure if this type of data CAN be normalized.

[June7]

I still don't understand what needs to be updated. Why would you change the data of committed records?

Why do you show my RevDate field as just Rev?

[gaker10]

Assuming RevDate is a date field, this is not a valid field, as the revision date does not matter. The revision itself is what matters (the revision can be any combination of alpha-numeric text). Did I misinterpret this field?

[June7]

Seems knowing when something happens is often useful. Doesn't hurt to have both date and identifier.

[gaker10]

Agreed. So then the structure would be:

tblDrawingRevisions
DrawingID (FK)
Rev
RevDate
Comment

So, if something was entered in the database incorrectly, that would be cause to "update" it, or in this case, correct it.

Also, how do I prevent the same Rev being entered for the same DrawingID?

Also, how would I add information for a new DrawingID? A new DrawingID means a new Rev, new Part(s), new Cage Code(s). Since these are all in different tables, how do I add this information simultaneously?

As it was, this was all in one record, so I would add it all at the same time.

[June7]

Use form/subform(s) arrangement for data entry/edit.

[orange]

gaker10,

I really think you would understand more of what is being proposed if you would spend 30-45 minutes
working through this tutorial from RogersAccessLibrary. I'm quite sure it could be the best use of your time at this point.
I'm also sure that the use of form/subform suggested by June is a proper construct that you will make use of, but I think you should understand your table and relationships and how they relate to the business issue first. You said you didn't really have time o Normalize and build the other tables, but this will save you time.

Anyway, regardless of what you decide, I do wish you well with your project.