About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
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
