Solved

indirect relationships

Posted on 1998-07-21
12
234 Views
Last Modified: 2006-11-17
null
0
Comment
Question by:azul
  • 7
  • 4
12 Comments
 
LVL 4

Accepted Solution

by:
tomook earned 200 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.
0
 

Expert Comment

by:jmjackson
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.
0
 

Author Comment

by:azul
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.
0
 

Author Comment

by:azul
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.
0
 

Author Comment

by:azul
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.
0
 

Expert Comment

by:jmjackson
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:azul
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.
0
 

Expert Comment

by:jmjackson
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.
0
 

Author Comment

by:azul
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.
0
 

Expert Comment

by:jmjackson
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.  

EXAMPLE:
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.
0
 

Author Comment

by:azul
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.
0
 

Author Comment

by:azul
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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now