Expert: Geoff - 6/21/2004

Question
Database Insert Anomaly

I'm designing a database with a one to many relationship, here is the list of all the tables.

create table Contact  (
ID              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Location_ID     INT NOT NULL,
  Features_ID     INT NOT NULL,
  Cost_ID         INT NOT NULL,
  Number          VARCHAR(12),
  Contact         VARCHAR(20),
  UNIQUE     (Number,Contact)
);

create table Location  (
  Location_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Area            VARCHAR(15),
  Type            VARCHAR(15),
  Address         VARCHAR(255)
  UNIQUE          (Area)
);                      

create table Features  (
  Features_ID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Bedrooms        FLOAT(1),
  Baths           INT(1),
  Availability    VARCHAR(15),
  Features        TINYTEXT,
  Note            TINYTEXT
);

create table Cost (
  Cost_ID         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Rent            FLOAT(9),
  UNIQUE          (Rent)
);

The main table is called contacts, all the other tables are linked via an id for example contact.location_id, contact.features_id, contact.cost_id
are linked to location.location_ID, features.features_id, cost.cost_id.

The user interface consists of forms created by sql querys, all the tables and fields are accessable by one form. How would i update
the fields contact.location_ID, contact.features_ID, contact.cost_ID,  based on information from tables location, features, cost
after the user has added all the data into the form ?.

The problem is , i don't know any Visual Basic Programming , is there a way to check the availability of data in tables location, features and cost,and if data where to be found insert the ID fields of tables location, features and cost into fields contact.Location_ID, contact.Features_ID, contact.Cost_ID ?.

I'm using MySQL ODBC with Microsoft Access , mysql version 3.23.58-nt. I hope this makes sense, kind of difficult to explain.

Thanks.


Answer
Hi Arno

If you open the table Contact in design mode, then select the FK field, say, Location_ID.

At the base of the dialog, select the tab 'Look up'. In the display control select combo box.

Now under the property Row Source, select the table 'Location' . You now need to enter the number of columns in the table, in the 'Column Count' property, so here we enter '5': then we need to provide the column widths required for each of these columns in the 'Column Widths' property, so enter 0;2;2;5;0 - or whatever widths you would like (in centimeters).

Now save this and have look at the data in the table - instead of seeing the Location ID, you will see the description of the location.

You will need to 'play' around with the settings to get the best display for you.. Good luck

Kind regards

Geoff