I have quite a complex database so I will just explain the relevant tables/forms.
I have an equipment table which collects information in the following fields: Equipment ID (primary key), Equipment Description (text field), Equipment Sub Sub Category (lookup from another table).
A piece of equipment can be linked to many Equipment Categories (lookup from another table) and many Equipment Sub Categories (lookup from another table). I therefore have a join table to establish these many to many relationships. Note that there are 4 equipment categories, say A, B, C & D for simplicity.
I also have 4 compatibility tables (one for each category of equipment eg. A, B, C and D) that compare each piece of equipment with every other piece of equipment within the same equipment category.
eg. Equipment E-0001 applies to equipment category A and D and is therefore compared with itself and equipment E-0002 which applies to equipment category A.
The fields that are in this equipment compatibility table are E vs E compatibility ID (Primary Key), Primary Equipment ID (lookup from another table), Secondary Equipment ID (lookup from another table), Confirmed for use (Yes/No field).
I am basically trying to develop a matrix type structure that captures whether or not a piece of equipment can be paired with another piece of equipment. Obviously you would not want to put a piece of equipment with itself because the equipment item does not exist twice and it therefore isn’t a possible combination.
When a new piece of equipment is added the user fills out the information in the equipment table and this is straight forward. However, I would like to try and automate adding the equipment item to the relevant equipment compatibility tables and comparing the newly added piece of equipment to all existing equipment items in this table. The reason for wanting to automate this process is there are already as many as 42 separate items in one compatibility table which has resulting in 903 compatibility combinations and the equipment list will continue to expand in the future. Without automating, this is a time consuming and tedious process for the user as they will have to select each equipment item in turn from a combo box.
Does anyone know if what I am trying to create is even possible with access and if so, how would you go about creating this?
Thanks in advance.
I have an equipment table which collects information in the following fields: Equipment ID (primary key), Equipment Description (text field), Equipment Sub Sub Category (lookup from another table).
A piece of equipment can be linked to many Equipment Categories (lookup from another table) and many Equipment Sub Categories (lookup from another table). I therefore have a join table to establish these many to many relationships. Note that there are 4 equipment categories, say A, B, C & D for simplicity.
I also have 4 compatibility tables (one for each category of equipment eg. A, B, C and D) that compare each piece of equipment with every other piece of equipment within the same equipment category.
eg. Equipment E-0001 applies to equipment category A and D and is therefore compared with itself and equipment E-0002 which applies to equipment category A.
The fields that are in this equipment compatibility table are E vs E compatibility ID (Primary Key), Primary Equipment ID (lookup from another table), Secondary Equipment ID (lookup from another table), Confirmed for use (Yes/No field).
I am basically trying to develop a matrix type structure that captures whether or not a piece of equipment can be paired with another piece of equipment. Obviously you would not want to put a piece of equipment with itself because the equipment item does not exist twice and it therefore isn’t a possible combination.
When a new piece of equipment is added the user fills out the information in the equipment table and this is straight forward. However, I would like to try and automate adding the equipment item to the relevant equipment compatibility tables and comparing the newly added piece of equipment to all existing equipment items in this table. The reason for wanting to automate this process is there are already as many as 42 separate items in one compatibility table which has resulting in 903 compatibility combinations and the equipment list will continue to expand in the future. Without automating, this is a time consuming and tedious process for the user as they will have to select each equipment item in turn from a combo box.
Does anyone know if what I am trying to create is even possible with access and if so, how would you go about creating this?
Thanks in advance.