Please help with relationships within tables

[gt11990]

Hi there i am stuck on the relationships section of access basically

i have a few tables which id like to link with each other and have one main table where each of these tables obtain their data for example

main table is customers with customer id name surname address

the rest are orders and messages and so on with the same fields

i want these same fields such as name surname to get its data from customers table but with the corresponding customer id

so once a new customer is entered into customers table it will have its customer id

and from this id when i type it into orders table id like the name and surname address to come up automatically within orders and so on



thank you

 

[plog]

That's not how a relational database works. You don't store redundant data. The name fields should be in customers, and the id of customers should be in the other tables. Then when you need any data in customers, you create a query, linking the tables appropriately to the id field in customers and then bring in the data you want to display.

From an input perspective, you should create a form based on customers with sub-forms for all those other tables. This will allow you to input data to the appropriate customer.

First though, get your tables correct. Also, 'name' is a poor name for a field because it is a reserved word in Access. A better name for that field would be 'CustomerFirstName'

 

[gt11990]

oh ok i understand however what i am trying to do is make forms inorder to do all the operations in a GUI format instead of going into query's and tables

so what i want to do is as soon as i type a customers id into relevant form or table i want the rest of the fields in that row to be filled in without me having to go and do extra operations

i want to make it as easy as possible for the user

 

[plog]

If you want to display data from another table based on an id entered on a form based on another table, you would make the source of those Dlookups (http://www.techonthenet.com/access/functions/domain/dlookup.php)

I still have a feeling your going about this wrong. The tables you mentioned prior make it seem like a Form/SubForm would be the best method for inputting data. Generally with that type of setup, all the data from the main table would appear in the top of the form, then you would have rows of data from the second table in the sub-form while the information in the top form is still visible making displaying it alongside the subform's data moot.

 

[gt11990]

ok am confused now haha am fairly new to this

main table has customer details with primary key id for each customer i want each table to get the customer details same fields from the main table onces the id is entered into a new row so that once one information is changed in main table all the other tables are updated

thus it also changing in the forms

so main table information changes, all other tables changes also and updates accordingly

do you reckon this is possible

 

[plog]

so main table information changes, all other tables changes also and updates accordingly

This is exactly what I said shouldn't happen in my first post. Reread it. Then google 'normalization'

 

[gt11990]

ok thank you i will look into that but like i said i am very new to this its very confusing

 

[Pat Hartman]

i want each table to get the customer details same fields from the main table

As plog already said. This is not how it works. With the exeption of foreign keys (which is what we call the columns used to join tables), data is not stored in multiple places. You have a table that defines customer demographic data. Then you have other tables such as orders. In the order table you have a column named CustomerID and that points back to the customerID on the customer table. Creating a query to join these two tables on CustomerID gives you access to all the columns for the customer info from the customer table and all the columns for the order info from the order table.

On your order form you will have a combo that selects a CustomerID. Because of the way Access works, Access will automagically populate the customer fields on the order form as soon as you select the customer. One word of warning though. When you display "lookup" fields, you should lock them to prevent accidental updates. For example, you don't want the user changing the customer name on the order form because he thinks he's picking a different customer.

Queries are used to join tables and they allow you to pull fields from multiple tables. This is how you turn raw data back into information. You then use queries as the RecordSource for forms and reports.

 

[gt11990]

hi there Pat Hartman now i understand how it works

may ask you an additional question the look up option which does the combo box how would i implement it in such way for example

gets customer ids from the customer details table, the combo box is in orders

so when am in orders when i select the correct id the customers name and address gets displayed in the name, address fields in orders table

how can i do that ?

 

[Pat Hartman]

Go to the samples section and search on my name. The many-to-many sample database has a subform with a combo that auto fills. There is no code required. Just make the query that joins the two tables. Then make a form based on that query. Add the combo for customer (let the wizard build it). When you choose a customer, the fields from the customer table will populate automatically.