preserving a relationship

[Jeff C]

I am using make table queries attempting to properly construct one to many
relationships between Depts, Mfg, Model#s, and Serial Numbers. I am having a
problem getting a basic table..say just the Mfgs....to relate to the original
table to preserve the overall relationship between the items. In the query I
have tried saying "Yes" to the "Unique Values" property but without bringing
over an ID field I have no relationship. Anyone have an idea for me? Thank
You.

 

[Sharkbyte]

I'm not sure, Jeff, but it sounds like you are going the wrong way with the
relationship. The Mfg. should have Dept.s and Models, and the Models would
have Serial #s.

Something like this:

tblMfg
MfgID (PK)
MfgName

tblDept
DeptID (PK)
DeptName
MfgID (FK)

tblModel
ModelID (PK)
MfgID (FK)

tblSerialNum
SerialID (PK)
ModelID (FK)

Now you can tie everything back to an Mfg, or Dept, or Model.

HTH

Sharkbyte

 

[Jeff C]

Thanks Shark..I'll work on this and respond more to this thread with problems
if the y occur. I appreciate the help.

 

[Jeff C]

I seem to be doing circles getting the tables related to each other
correctly. I really want to preserve relationships to the original table
imported from excel, and have isolated out the mfgs, and mdls. The
departments happen to be the location of where some of the items are. The
serial numbers can probaly use the autonumber Key from the original dtabase,
but relating the 5 tables is getting me confused. Mfg, Mdls, Serial,
Original, Department...the original has everything in it.

 

[Sharkbyte]

I believe your Original table is causing all the problems. There is no
reason to maintain relationships to that table. The other four tables,
combined, simply make up the exact same data.

If you were to give me the data, I could take those four tables and exactly
recreate your Original table data. It jus wouldn't be normalized. =)

Is there a particular reason you are trying to relate back to the Original
table?

Sharkbyte

 

[Jeff C]

The original table was generated by a third party equipment leasing company.
This company is responsible for the actual inventory of the items (about
5000). IN the future when they update the inventory I will be able to cross
it over. I am maintaining a different set of info on the pieces of equipment
so it seemed to make best sense I started out normalized. Thanks for your
ideas..I am still working on it.

 

[Sharkbyte]

Jeff:

Just for clarification. When I said "...it wouldn't be normalized." I was
referring to my re-creation of the Original table. Not the four tables we
have been discussing. =)

Good luck

Sharkbyte