Setting up relationships...

[jgi]

Here's what I'm trying to do...

I'm trying to create a table in which one field, 'Company', has a relationship to another table/query that is a database for different companies and their addresses. I would like to set it up so when the user selects a company on the main table from the lookup, the subsequent fields format to the company's address. So there is one lookup that fills in a number of fields, such as address, city, state, post code, etc. Is this even possible?

Any help would be great. Thanks.

jgi

 

[nberryman]

If I am reading this right one company can have many addresses so a table for the company with a company ID with a one to many relationship to an addresses table.

Common Company data would be stored in the company table and then office addresses in the address table.

This way it won't matter if one company has 3 offices and another has 10 or more.

If that doesn't make sense re-post

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[ccburn]

Well here is a sample of how to autofill many fields by selecting a value from a combo box on your form. (copied from one of my other posts)

Just set your combo box up to select
lname, fname, phone, department from the USER table
thus you will have 4 columns display when you click the down arrow for the combo box. (You can hide these by reducing the column widths). I suggest you show the first name also since last names could possibly be duplicated. Access sees these columns as
lname -->combo.column(0)
fname -->combo.column(1)
phone -->combo.column(2)
dept -->combo.column(3)

On the combo.afterUpdate (in VBA)
put the following..

Me![firstname_field_on_yourform]=combo.column(1)
Me![phone_field_on_yourform]=combo.column(2)
Me![dept_field_on_yourform]=combo.column(3)
etc.

As soon as the combo is changed you will see the new values populate throughout your form for the current record. I use this to speed up data entry.

Hope this helps.
-ccburn

 

[WildHare]

I would like to set it up so when the user selects a company on the main table from the lookup, the subsequent fields format to the company's address. So there is one lookup that fills in a number of fields, such as address, city, state, post code, etc. Is this even possible?


As you've seen by prior reponses, it's possible to do this for "displaying on a form" purposes, but I'd have to add that this is not recommended if you're thinking of storing these values in the other table.

If you store company information in one table (good), and link the other table record to this Company table, there is NO NEED to 'copy' the non-key company info such as address, etc to the 2nd table. In fact, this kind of duplication directly negates the advantages of a table-driven relational database.

Just wanted to make sure you realized the difference...

If the tree I'm barking up is the wrong one, apologies in advance..

Jim



Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm