MS Access Self Referencing Query

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
FROM Equipment
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?

It's been a while since I had to do something like this, but it looks like you need a cross join:

SELECT Equipment.ID, Equipment.Model FROM Equipment CROSS JOIN EquipmentInUse WHERE (((Equipment.Type)=[EquipmentInUse].[Type]) AND ((Equipment.Brand)=[EquipmentInUse].[Brand]));

Since a cross join gives a result set that includes all combinations of records from table1 and table2, make sure your where clause guarantees uniqueness. If you don't you will end up with duplicates.

I've been an Oracle and SQL DBA for 17 years, and I can tell you you never want to store the same information in more than one place - eventually the data will get messed up. Here's what I would do - maintain all your equipment information in only the [Equipment] table. Make the [EquipmentInUse] table a simple reference to both the [Equipment] table and the [Room] table, maybe adding a column or two to the [EquipmentInUse] table that is appropriate, like the install date.

Something like this design:

IMAGE(http://i.imgur.com/MRmynKx.png)

Then you can build your query to show all equipment that's currently in use like so:

SELECT Rooms.BuildingNumber, Rooms.FloorNumber, Rooms.RoomNumber, Equipment.Description, Equipment.Type, Equipment.Brand, Equipment.Model, EquipmentInUse.InstallDate FROM Rooms INNER JOIN ( Equipment INNER JOIN EquipmentInUse ON Equipment.EquipmentID = EquipmentInUse.EquipmentID ) ON Rooms.RoomID = EquipmentInUse.RoomID;

And this query would list any equipment that's not in use:

SELECT Equipment.EquipmentID, Equipment.Description, Equipment.Type, Equipment.Brand, Equipment.Model FROM Equipment LEFT JOIN EquipmentInUse ON Equipment.[EquipmentID] = EquipmentInUse.[EquipmentID] WHERE EquipmentInUse.EquipmentID Is Null;
Rykin wrote:

Any suggestions other than not using Access? ;)

Access is fine for the front end, but if you have the ability to use SQL Server for the data storage then please do so. The main issue with data storage in Access is it has a tendency to not be properly backed up. It generally sits on a workstation or network location and nobody on the IT support teams know about it until one day it's accidentally deleted or corrupted and suddenly they're asked to recover it. Generally the answer is no, the data is gone. Your IT department may already have a SQL Server instance you can use that's properly maintained and backed up, and it's pretty simple to link from Access to SQL Server.

Azure or Amazon RDS are also perfectly good options.

Serengeti makes good points. His solution is the one to go for!

Your model doesn't quite fit with what I am trying to do Serengeti.

The Equipment table as I was designing it was going to basically just be Type (pulling from a type table), Brand (pulling from a brand table), and then Model with information about specific instances of equipment being stored in the EquipmentInUse (not the best name maybe). For instance I have 30 something Dell Precision T1700s in my classrooms in one build. Idea being I could select Type and then Brand in the EquipmentInUse table and then when I go to select Model it would limit me to models that have the matching Type and Brand pulling from the Equipment table. I am trying to limit input errors should anyone other than me start using this database as well. I know my data entry skills are very good but I have worked with plenty of others who can't do it for crap. Right now I am trying to build the functionality into the data sheet view but I think I might have to do it in a Form instead.

The Access file is being saved in a location that gets backed up. Right now I am just playing around. I'm starting fresh and don't have a lot of work on my plate so I am trying to build one of the tools I always wished I had at my last job.

It's been a long time since I've done any database design, so take these comments with a grain of salt.

If I understand your problem correctly, you're probably not representing the physical reality of things quite correctly with your table design. Any time you're repeating data, chances are pretty good that you haven't normalized enough. Sometimes this is necessary, but it should be a giant red flag. For example, if the word "Panasonic" exists more than once in your database, you probably needed another table.

In this case, it sounds like you need separate tables for Brands and Models. This is a one-to-many relationship; any Brand can have many Models, but any given Model will have only one Brand. So these tables would look like this: (I'm using projectors here.)

Brands: Brand_ID Brand_Name 1 Panasonic 2 Sanyo 3 Canon Models: Model_ID Brand_ID Model_Name 1 2 PDG-DXL100 2 3 LX-MW500 3 1 PT-CW330E

You'd also have a Type field, for Laptops or Projectors or whatever. Same deal; that's another easy one-to-many, another column in the Models table.

So then you'd have a Rooms table; what's in it is mostly irrelevant, just that there's a Room_ID plus other ancillary information.

At first glance, you'd think Rooms to Models would be a one-to-many, because any equipment can only be in one room. But..... they may get moved, over time. Over its life, a projector may be in many rooms, and any room may have multiple projectors. This is a classic many-to-many relationship.

Anytime you have a many-to-many, you don't link the tables directly. Instead, you link through a join table. In this case, you might call it Deployments. Deployments would link to Rooms and to Models. But it wouldn't link to Brands, because Brand is a property of the Model. That gets hauled in with the Model link; if you're linking to an LX MW500, it has to be a Canon. (well, barring buying two different projectors from two different manufacturers that use the same model name... don't do that!)

Actually, now that I think about it, you might prefer to put a unique asset tag on everything, and link to THAT. Instead of linking Models and Rooms through Deployments... instead, make another table called Asset_Tags. Any given Asset_Tag will link to exactly one Model, and thus Brand. You can put date of purchase and date of retirement in the Asset_Tags table, that would be the right spot for that info.... anything that's linked to this specific instance of a Brand and Model. Oh, serial number, too. When you deploy something, you're creating a link in Deployments between an Asset_Tag and a Room_ID, automatically hauling in a ton of other information about the equipment through the links it already has to other tables. (An asset tag is a physical unique ID, one with a guaranteed format that's wholly under your control. It can be used as a unique key, just like standard ID autoincrement values.)

So, when you're doing your deployment entry, you'd have some nice cosmetic dropdowns, for Type, Brand, and Model. Each time you updated one of those, it would update the dropdowns to the right (that is, changing Type would update Brand and Model, changing Brand would update Model.) Then, once all three had a value, you'd populate an Asset_ID dropdown, constrained by the other values. Then the user would choose an Asset ID, a Room_ID, and would click some kind of Deploy button, which would generate a record in the Deployments table.

Note that these three dropdowns don't store data directly. All they're doing on your form is limiting the query that populates the Asset_Tag dropdown. The Asset_Tag is the data that's actually being chosen and put into the Deployments table. You're not storing the the query limiters, they were just there to be helpful for the user.

Now, the logic in this could get pretty snarly. It will take some real thought. Example: in a Deployments table, each row might link a Room_ID with an Asset_Tag, listing a start date, and an (initially empty) end date. Your Asset_Tag table might have an 'Available' Boolean field, and you'd have to be very careful to ensure that it was set to False when a new record was created for a new deployment, and then set back to True when an end-date was entered. Keeping those states in sync could be a little troublesome, and you might want to write maintenance code that looks for Available projectors that have any Deployments with a null end date, and for Unavailable projectors that have only complete records. (if all their end dates are filled in, they should be Available now.) This will help catch logic or data entry errors early.

I think this will mostly fix your problem. I tend to be over-wordy when I'm explaining things, so this might seem like total gibberish. I also may have misunderstood the underlying problem. But I think this would probably do what you need. (Brands -> Models -> Asset_Tags, all one-to-many, and then a many-to-many join between Rooms and Asset Tags using a Deployments table.)

edit: Oh, and then Types -> Models as well, so it would be:

Brands -> Models -> Asset_Tags -> Deployments <- Rooms
Types ->

This would be easier if I used a tool like Serengeti's.

I am normalizing quite a bit actually. Here is what my current DB looks like:

IMAGE(http://i.imgur.com/qULPpK4.png)

Access is more the foreign beast for me than database design. Asset Tags is not the worst idea to add though.

Yeah, I'd pull Models into its own table, and then maybe rename Equipment to be Asset_Tags. (or just treat it that way.)

Your problem there is all those links in your EquipmentUse table. There shouldn't be so many. A many-to-many table should really have only two foreign keys, the two sides of the many-to-many relationship. You'll automatically get all the other info just by linking to Equipment... by doing that, you're already pulling in Brands and EquipmentTypes. (and Models, once you break that out.)

Adding in the explicit links as well is just messing stuff up, because EquipmentTypes doesn't relate to EquipmentInUse.

Further, you're putting a lot of data in the wrong place. Your EquipmentInUse (what I call Deployments) should have:

Deployment_ID
Room_ID
Equipment_ID
Deployment Date
Removal Date

And that's *all*. Many-to-many tables should have very little in them.

Pull Serial, PurchaseDate, WarrantyExpiration, and Notes into the Equipment/Asset_Tags table, that's where it belongs. That relates to a specific physical inventory item, not the deployment. It's not like the PurchaseDate will change when you move equipment to a new room.

A general suggestion: you've got a million fields there, all with the same name, "ID". This can cause you all kinds of nasty issues, later. Put the table name in as well.... EquipmentType_ID, Brand_ID, Room_ID, like that. Ideally, every field in every table should have a unique name, but at least make sure your ID fields are uniquely named. That caused me so much grief, once upon a time.

Oh, and you can get away without using a many-to-many join on your Deployments table, if you don't care about history. If it doesn't matter to you that it was in Room 202 for awhile, and then moved to 303, then you can do a one-to-many join, and keep the logic simpler. But I like to know about trouble spots, and being able to ask the database who requested the most equipment moves this year might be a productive query. And spotting where things get broken the most is another easy question to ask when you've got a history table.

This does, however, increase the logic complexity more than a little.