I am working on an Access database (ugh) to help me in my new job that will let me keep track of what technology we have in various classrooms. I am trying to build everything based on lookup tables as much as I can. I am working on a Table called EquipmentInUse that has the following fields ID, Room, Type, Brand, and Model (with more to come). Room is a lookup field that references a Rooms table with a list of all the classrooms in the two buildings I am responsible for in it and Type is a lookup field that references an EquipmentType table with a list of the different types of equipment we use (i.e. TV, Computer, Projector, Document Camera, Etc) and Brand is another lookup field that references a Brands table. There is also an Equipment table that contains ID, Type (looked up again), Brand (also looked up), and Model.
Okay so in the EquipmentInUse table I want to populate the Model field lookup by referencing the Equipment table where Type and Brand match the current choices for Type and Brand in the current entry. Here is what the SQL currently looks like:
SELECT Equipment.ID, Equipment.Model
WHERE (((Equipment.Type)=[EquipmentInUse].[Type]) AND ((Equipment.Brand)=[EquipmentInUse].[Brand]));
This prompts for me to enter the value of EquipmentInUse.Type and EquipmentInUse.Brand and if I type in the correct numerical ID numbers it will give me the correct list.
After searching the net for a solution I haven't been able to come up with anything at all. Wondering if I should just drop the Equipment table with the Type, Brand, and Model information and just enter the models manually in the EquipmentInUse table from here even though we have many of the same model of item. I haven't gotten around to building forms yet; I am just working in Datasheet View. Any suggestions other than not using Access?