I have the following tables in Access 2010 that make-up equipment technical specs for a company that has 6 different systems with same types of equipment in each system. I would like a composite/unique id based on System ID & Unit ID to differentiate which equipment is in which system. The equipment ids can be duplicated, but with the system id they should be unique. I have tried the following tables:
Table A - System Info
System ID (PK) unique
Table B - Units Info
Unit ID (PK)
System ID (FK)
Unit ID and System ID are set as primary keys with field properties required but not indexed. Access did not seem to like them being indexed.
Table C - Equipment Specs
Equip ID (PK)
System ID (FK) -> Table B
Unit ID (FK) -> Table B
Equip ID, System ID, & Unit ID would be composite & unique for each value.
I cannot seem to get this design to work. There will be other tables for different types of equipment that refer back to Table C . I have started over and I have been successful in creating Table A and Table B but am having trouble with Table C. Any help will be greatly appreciated.
First, I'll assume that you have additional fields to add to the system table as it's pretty pointless in its current state.
The way the Unit table sits, a unit is only valid in the context of a system but you're mixing up the purpose of Table B and Table C. I don't know your data, but I'm guessing that Unit is a type of classification?
You said in the description that different systems can use the same type of equipment so you need to add that one as well.
Try something like:
System (SystemID, SystemName, ....)
Unit (UnitID, UnitName, UnitType, ....)
Equipment (EquipmentID, EquipmentName, ...)
Any additional fields in these tables should be attributes that define one System, Unit, or Equipment rather than connections to other tables.
Those would be the three base tables that define the items. The ID fields are the PK and can be autonumber fields. You would then add a join table that connects them together as desired.
SystemEquipment(SystemID, UnitID, EquipmentID)
- You could add an SystemEquipID autonumber if you want to use this combo in other places
If Unit is invalid without a System then add SystemID to the Unit table and remove SystemID from the join table (it's already defined in Unit)
Steve, thanks for the reply, but I must be missing something. Let me explain further. I understand what you mean by a join table and have created one as you suggested. My problem is that there are other tables that are grouped by type, Type 1, Type 2, etc. Each table will have different specifications stored in each. Type 1 is different that Type 2, etc. I cannot get Access to refer back to the Join Table as you suggested without causing some sort of integrity error. Duplicates in index, etc.
What I did not mention earlier was that all equipment already has an ID assigned to it and is unique per system, but when I combine all Type 1 units together there will be duplicates in the corresponding Table. That is the reason I was trying to have a composite key by using system id and unit id. Access will not let me do so. I could be setting up my table wrong, very frustrated at this point.
How do I get Access to let me refer back to the Join Table and enforce referential integrity. There are other tables that will get there unique id from the Join Table, Table C.
I created a join table as follows:
Table A - System (01, 02, 03, ...)
System ID (PK), System Name, etc...
Table B - Units (A1, A2, A3, A4, ...)
Unit ID (PK), Unit Type, etc...
Table C - System Units (Join Table) (01 + A1, 02 + A1, 01 + A2, 02 + A2, ...)
System ID (FK), Unit ID (FK)
Table D - Type 1 Equipment
System ID (PK), Equipment ID (PK), Type, Mfg, Date, etc...
Table D will have duplicate values for A1, A2, A3. System ID should make the key unique. As it stands now I can only add 1 record to Table D. Help!!
As a follow-up I had each field/column in Table D to not allow duplicates which seems to be causing the problem. I changed each to allow duplicates and now can add more than 1 record to the table. Please give me any incite about my previous post as I want to be sure I am on the right track. Thanks in advance.
Does table C have a unique ID defined? You only mention the two FK fields, does it also have a SystemUnitID that is unique to every row in the table? That's the PK of the table and the ID you want to use when joining to other tables.
Tables A, B, and C are connected (although I'd add a PK to C), the purpose of table D isn't clear. Table C is already connecting Systems and Units so why are they both here as well?
Is Type a reference out to table B?
You said that each piece of equipment has a unique ID assigned to it, is that in another table? If so, equipId is a FK to that table and this is a join table so the equipment information doesn't belong here.
If I'm understanding this correctly, SystemID is part of the equipment definition. That makes EquipmentID the PK of table D and SystemID is just a FK reference to System as part of what defines a piece of equipment.
It seems like you are trying to connect equipment with units by using table B when you really need to be using table C (with the PK defined). Depending on your data you can either add a FK reference to table C (instead of Type) or if many connections are needed add another join table to connect equipment with type.
If table D is meant to join Systems & Equipment then you need to have an Equipment table with the EquipID and all the related fields, and a separate table that joins System & Equipment.