Field Relationships

[awhit22]

Hi there,

Wondering if someone here can help me out... I have a basic Database design. One Main table with multiple relationships defined. I have three of the fields that I want users to populate, but I want the options in the second two fields to be limited based on the choice of the previous fields.



For Example. I have 4 tables
Table One: Main Table
Table two: Locations (one to many relation to Room)
Table three: Location Room (one to many relation to shelf)
Table four: Room shelf

What I'd like to do is from the main table, be able to select the Location, then based on that value, only have the Room field display records that relate to the location, then select Shelf based on the room selection.

I'm trying to update the fields from the datasheet view.

I've tried a few different select statements based on what I think it can do, but am having trouble wrapping my head around it.

Thanks in advanced for any help.

-Adam

[orange]

You haven't said anything about your environment nor your experience with Access, so the most basic advice I can give you is you will be dealing with Forms. Users should NOT work directly with Tables. On your Form you will have some combo boxes. There will be a dependency on these combo boxes.
The topic you are dealing with when once one item is selected, the next set of options is limited because of the previous choice is Cascading Combo boxes.

There is a free video demo here. There are 3 videos. I recommend you watch them in the 1,2 3 order.
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html
http://www.datapigtechnologies.com/f...combobox3.html

Good luck with your project.

[awhit22]

You haven't said anything about your environment nor your experience with Access, so the most basic advice I can give you is you will be dealing with Forms. Users should NOT work directly with Tables. On your Form you will have some combo boxes. There will be a dependency on these combo boxes.
The topic you are dealing with when once one item is selected, the next set of options is limited because of the previous choice is Cascading Combo boxes.

There is a free video demo here. There are 3 videos. I recommend you watch them in the 1,2 3 order.
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html
http://www.datapigtechnologies.com/f...combobox3.html

Good luck with your project.

Thanks for your reply. The project is relatively simple. I need a basic way to track location and data about some equipment. Not quite a full blow asset DB or an inventory DB. Just a basic setup. a couple forms, etc. Right now the form I'm trying to use is a tabular form. Similar to what an excel spreadsheet would look like.

I work more on the systems side, so it's been a while since I've used access to do anything. I've only made a few small, simple DBs in the past, so I guess I'm still fairly novice at it.

I'll give the links a look. Thanks for your help.

-Adam

[awhit22]

Thanks again for your help. The videos were helpful.

I was just about there with what I had, I just didn't include enough data in my query. I had to add the one to many field in the query, then the cascading worked as expected. Before I was just getting blank results.

Appreciate the direction.

[orange]

Glad to help. I think you'll find the Datapig video tutorials a good source of help for various "Access features".