How to set up relationship between 2 fields in same table in Acces

G

Guest

e.g.-- In my main table I have a column, Categories, with a pull down menu
with 3 selections, "Auto", "Household", "Medical". Then I have a column,
Item, in which there is also a pulldown menu which will display only items
associated with the specific category selected in the Category field. So if
I selected "Auto" in Category, then "Gas", "Insurance", "Oil Change" would be
displayed for choice in the Item column. If I selected Medical in Category
column, only "Foot Doctor", "Chiropractor", "Prozac" would be displayed for
choice in the Item field. Is it possible to design a relationship like this
in Access and if so, how do you do it?
 
M

MacDermott

This is not too hard to do if you use a form to access your table.
In the table itself, I know of no way to do it.

HTH
- Turtle
 
6

'69 Camaro

Hi.
Is it possible to design a relationship like this
in Access
Click to expand...

Yes. It's not called a relationship, though. Since you are using a
relational database, relationships are assigned between entities (tables) to
ensure that records in the tables are governed by relational integrity rules
(i.e., one table doesn't contain records with a related value that doesn't
already exist in the related table, only one of each related value exists in
the related table, et cetera).

What you need is called "business rules" and these rules can be assigned at
the application level, instead of at the table level. A form with two combo
boxes can easily provide the functionality that you need. When a selection
is made in one combo box, the items displayed in the next combo box are
"filtered" by that selection in the first combo box.

The Solutions.MDB that shipped with Access 97 contains an example "How to
Limit the contents of one list based on the value selected in another" that
would be helpful in creating these combo boxes on this form. If you don't
have a copy of this sample database, then you may want to Google the News
Group archives for instructions on how to do this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
G

Guest

Hey Gunny/69 Camaro -

I have the same question about the relationship of two fields in a table (or
specifically on a form) I can't figure out where you say to go for the
Solutions.mdb

Can you help some more?

Marc
 
G

Guest

I also have the same question. I no longer have Access 97, so I cannot
reference that sample database, and I'm not sure where the News Group is that
I'm supposed to Google. Do you think you could post the website addess so I
know where to look for the answer to this?

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

store multiple selection of list box in table field 3
table w/ multi relationships frozen to view 1 relationship only? 2
how to display data from Table 1 ("one") in Form for Table 2 ("man 4
How do I concatenates two fields into one field in the same table? 3
How to relate fields in the same table 4
How to I combine 2 columns from 1 table in a query? 2
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in C 3
Help! How can I set up a relation between 2 tables in memory? 4

Top