Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


indirect relationships

Posted on 1998-07-21
Medium Priority
Last Modified: 2006-11-17
Question by:azul
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4

Accepted Solution

tomook earned 600 total points
ID: 1976977
You need a relationship. Using the table structures supplied, without modification, you would set up a "One to Many" relationship with the "Type" table on the "One" side and the "Description" table on the "Many" side. This will have the effect of forcing the "Type" field in the "Description" table to only have values which appear in the "Type" table.

Since linking on long strings can waste a lot of space (since the data is in both tables, as well as at least two indices), consider setting adding an AutoNumber field to the "Type" table and using it as the primary key. Set a unique index on the "Type" field in the "Type" table, to make sure there are no duplicates. Now, in the "Description" table, change the "Type" field to Long Integer. Make a "One to Many" relationship between the fields, as in the first paragraph.

Assuming you set up the AutoNumber field in the "Type" table, to get the form behaviour you want,
1. Create a query containing only the "Description" table.
2. Make a form based on this query.
3. Unless you need to see the autonumber field in the Description table, you need only two fields: Type and Description
4. For the Description field, use a text box.

Now comes the tricky bit: showing the description instead of the number for the Type.

5. For the Type field, use a Combo Box.
6. Bind it to the Type field, and set the RowSource property to the Type table. Use both fields (2 columns), and set the bound column to the one with the autonumber.
7. Set the "column width" of the autonumber field to 0. The Combo Box wizard in Access 97 will help you do this automatically.

The form will now show what you wish. Note that the combo box is really storing a number, but is displaying a description.

Expert Comment

ID: 1976978
You can assign a type code in your TYPE table and link it to your DESCRIPTION table in a one-to -many relationship.  However, your problem is yourr approach is kind of backwards.  It is easy for the user to select a type (MEAT) and have the database return available descriptions (HAMBURGER, STEAK, HAM, ETC.) but unless you already have a complete list of your descriptions, and present them using a List Box Control (or Combo-box) their is know way of telling what the user will enter for description.

Author Comment

ID: 1976979
Ok, I finished trying out what you told me and it is great in the sense that it displays the correct information (it shows the right info in the type field ie Meat, Fruit, Beverages and it will show Hamburger if I select Meat) However, I need it to work so that if I type in Hamburger, Meat gets filled in to the Type field.  I need this for numerous reasons. The first is that there are many more choices of descriptions (it is more detailed than Hamburger, ie 1/4 lb burger, grilled chicken, etc) Also, the Descriptions change, so I might add 1/2 lb burger to the choices at any time.  It still falls into the meat category, and I would have to specify that by adding it to the table, but for the most part, the Types will not ever be added to while the Descriptions are numerous and changing.  Is there any way to have "Meat" pop up in the Type field when I type Hamburger assuming that in the descriptions table I have a row that says Hamburger under the description column and Meat under the Type column?  Thank you for your help.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 1976980
Actually, ideally I would like to be able to enter a value in the description field and if there was not a proper match in the type field I would like a new dialogue box to pop up and allow the user to choose the correct type for that description.  I know how to do a lot of that, but that is another question, i would just like help with the part above for now and wanted to let you know where I was headed with it. Thanks again.

Author Comment

ID: 1976981
Also, if I try to create the form using the query as well as other information (I also have tables with brand names, suppliers, etc.)  In the major form, I want fields that use parts of the Brand Names Table and the Suppliers table as well as the Descriptions Query. However using the form wizard, I get the following error message:

You have chosen fields from record sources which
the wizard can't connect.  You may have chosen
fields from a table and from a query based on
that table.  If so, try choosing fields from only the
table or the query.

However, as far as I can tell, there is nothing relating the descriptions query (or table) to any of the other tables.  Did I misunderstand one of your steps? When I created the form based on the Descriptions Query I had no problems like this.

Expert Comment

ID: 1976982
You can use a query look up on the Types table in the AFTERUPDATE event of your description field.  The query should look for any descriptions LIKE (Keyword) "*DESCRIPTION*"  where '*' is a wildcard.  This query can return any types that are already assigned to a description containing the word in the DESCRIPTION Field.  If no records are returned then you can display your dialog box and have the user select a type.

Author Comment

ID: 1976983
Thank you jmjackson, that sounds like it would work except that I cannot get the field to appear on the form in the first place due to the error message I wrote above.  Once I get past that problem, I think that AFTERUPDATE event should work perfectly. Thank you.

Expert Comment

ID: 1976984
All tables used on a form (for the most part) should be tied together by an innerjoin query.  This requires a relationship between all tables.  The relationship between DESCRIPTION and TYPE is the TYPE CODE.  Based on the design of your app if you wish to display all SUPPLIERS of TYPE MEAT then the SUPPLIER table should also have a TYPE CODE.

Author Comment

ID: 1976985
I am not sure I understand that. I can include information from all of the other tables, just not then adding the query.  I have read Access 97 Visual Basic Step by Step, is there another source out there that can help me grasp both these ideas and learn to program the complicated features I am asking about above?  Could you explain more in the meantime? Thank you very much.

Expert Comment

ID: 1976986
There are several good books and sources for learning about innerjoin queries.  Any advanced Access or SQL text should help you.  Concentrate your study on the concept of a PRIMARY and FORIEGN KEY field within tables.  

PRIMARY KEYS (within Access) are non-duplicatable fields that act as the primary sort for the table, and act as links to other tables.  FORIEGN KEYS are fields within tables that contain data corisponding to PRIMARY KEYS in other tables.  Thus establishing a relationship.  

In the TYPE table, their is a field called TYPE ID (or like) which is the primary key.
In the description table there is a TYPE ID field.  This is a FOREIGN Key linking both tables.  If you select a description, you can easily return the type along with it by innerjpoining the two tables on the TYPE ID field.

Author Comment

ID: 1976987
I did not know the nomenclature, but I already have a TypeID as a primary key and another field in the Description table called TypeID.  What would I need to do to make that TypeID a foreign key? I have created a relationship between them.

Author Comment

ID: 1976988
Right now I have a form with the correct fields.  I have The Description and Type fields as Combo boxes (I only really want the Type field as a combo, but the only way I knew to check if it was working was to add the description as a combo too). I can view the correct lists by clicking the down arrow, but when I go to select one of the choices, nothing happens. The box flashes almost and then the entry is still blank.  I could really use some help getting these fields to work.  If I convert the descriptions box by clicking change to text box, and try to type anything, I get the following error message:

The value you entered isn't valid for this field.  For example, you may have entered text in a numeric field or a number that is larger thanthe FieldSize setting permits.

Is this because I am linked to a unique field?  It might be looking at an ID number and just displaying the words.  How do I fix this so that I can maintain the behavior I asked about originally?  Thank you

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question