Multiple field population based on one selection 1

[pretzal]

I have two tables. One has a company name and the associated company code information in it. In the other table I provide a look up box for the company name. Also in this second table is a spot for the company code. How can I populate box the company name and code portions in this second table based solely on the selection that the user makes when choosing the company name from the look up list?

 

[LittleSmudge]

Well if the one is totally dependent on the other the YOU DON'T .


You said
In the other table I provide a look up box for the company name.

I sincerely hope that you don't mean that.
On the FORM you might have a look-up box, but not in the table.

I think what you are saying is table1 ( tblComanyInfo ? )has company information in it with the table1 Primary Key being CompanyCode. ( CompanyInfoId - I hope )


table2 has a Foreign Key field in it for the CompanyCode info - ( CompanyInfoRef )

Thats where you need to stop.
If you are looking at a record in table2 and you need the company address information then you get the CompanyInfoRef value from that record and go to table1 and look it up.

You do not, under any circumstances copy the data from one table to another.

Normalisation, Codd Rules etc.




'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

If you are really using look ups in the table, you need to read this:

'The Evils of Lookup Fields in Tables'



Leslie

 

[pretzal]

What I have is tbl1 (company info) Fields include company name, company code, and company abbreviation. Company name is the primary key. The second tbl (representative contacts) has representative name, company name (this is the forgien key from tbl 1, copany code, phone, email.

In tbl 2 I have made the company name a look up field that references to the company name field column in tbl 1, limiting the possible option to only those company names available in that column in tbl1.

I want, once the user selects the company from the list, the company respective company code to be populated automatically into the 2nd table also.

Does this make it any clearer?

 

[pretzal]

Lespaul,

what other methods are there for making sure the user does not enter incorrect data? Should I be doing this with look ups on forms?

 

[lespaul]

It was fairly clear before, we were just wanting clarification on where your lookup was. So, the answer is : Yes, you are using look up fields in the table, which is what we are saying you SHOULD NOT DO!!!

You need to create a form that allows the users to do this, do not EVER allow users directly in the tables.

Secondly, you would want to store the CompanyCode as the FK, not the CompanyName (assuming that CompanyCode is the PK of the table).

Check out 'The Fundamentals of Relational Database Design' for more details on keys between tables.



Leslie

 

[pretzal]

no the company name is the primary key.

 

[lespaul]

So what happens if the company name changes? you are going to have to go to all the related tables and change the name so it still matches the tblCompanyInfo.CompanyName field, if however there was a unique identifer (CompanyInfoID) that was an autonumber as the PK and all the other tables just have a link to that company's ID number (CompanyInfoRef), then you could change the name of the company and all the FK would still be correct.



Leslie

 

[pretzal]

They are actually state agencies. If any changes occour then the respective code will also change.

 

[LittleSmudge]

WHY is the Company Name the PK ?

I can forsee many problems with this approach.

Company gets bought out and changes name >>> BIG Problems
Company just ups and decides to change name ( It does Happen ) >>> Big Problems
Same Company working from two ( many ) sites >>> Big Problems
etc ..


On the other hand - if the company name and the company code are a one-to-one match ( Ie one CompanyName matchs to only one CompanyCode and Vica-Verca ) then you are still in the realms of DUPLICATE Data Storage - DON'T DO IT.

CompanyName - if you must - or CompanyCode - JUST STORE ONE and look up the other in the table1


By the way, also take a careful look at the FAQ
faq700-2190 Avoid space characters in any 'Name' - Why ?



'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[pretzal]

I still do not know if you can pull multiple field from a record based off of one selction.

 

[LittleSmudge]

If any changes occour then the respective code will also change

No, NO, NO


Once the code is written there should be NO need for future changes.

YOU need to design your database so that all forseeable changes and updates can be done by the user - NOT requiring coding mods after the design is complete.




And yes you CAN pull multiple fields from a table based on a single criteria - thats exactly what relational database design is all about. However, the real point that Leslie and I are making is that, in this case, you should not be doing it.

( We are trying to save you from yourself my friend ! )

Read the link that lespaul put up about the fundimentals of relational design.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[pretzal]

NO NO I ment the if a company name changed the a company code would change also. I have a form so that the user can do all of that. I did not mean code as in access code.

 

[lespaul]

You really need to get a better understanding of relational databases.

Say you have tblCompanyInfo, your fields should be something like:

CompanyID (PK)
CompanyName
CompanyAddress
CompanyCity
CompanyState
etc.

Now, if you need to change any of the company information, you could EXCEPT the PK - CompanyID. The PRIMARY KEY of a table CAN NEVER be changed. You said above that you let the users change the name of the company and the name of the company is the primary key. You now have to change the name IN EVERY TABLE!!!

Again, we are trying to save you a lot of MAJOR ISSUES in the future.

Leslie

 

[LittleSmudge]

Oh Okay Pretzal. Sorry for the misunderstanding.

Clarify for me.
Does a simgle CompanyName map 1:1 to a CompanyCode ?



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[pretzal]

I appreciate all the help that you are tring to give. However, all you seem to be doing is picking apart my every response. I realize that a pk can not change without being updated in all places that it exists. The only thing that I really want to know was the original question and how to do it. If you can not provide that then please do not reply at all.


You really need a better understanding of what is being asked for.

Very Respectfully


Greg

 

[pretzal]

LittleSmudge,

Yes, One company name = one company code. I need to display the company name for the ease of use to the user. I want to pull other information using only the company code. Therefore I feel in one of my tables I need the company name and the company code in the record.

When the user makes a new entry into the table, I allow them to choose what agency is associaed with the record. I want then company code also pulled into that record but I do not want the user to have to make that choice. I only want the user thinking about the agency name.

 

[LittleSmudge]

Therefore I feel in one of my tables I need the company name and the company code in the record.

NO YOU DO NOT.


Let me show you how EASY this will be IF you use the CompanyCODE as the table1 Primary Key

On the form where you are populating the table2 data ( Probobly a form BOUND to table2 you put a combo box

The combo box is bound to the ForeignKey in table2

The Foreign Key will store the value of the CompanyCODE that is the PrimeKey in table1

You make the ROWSOURCE of the combo box as follows

"SELECT CompanyCode, CompanyName FROM table1"
combo's other properties
ColumnCount = 2
BoundColumn = 1
ColumnWidth = 0


The USER will then SEE a list of company NAMES appear in the combo box ( Because Colwidth=0 makes the first column zero wide they don't see it )

The user select the appropriate CompanyName - but it is the CODE that is stored in the table2

Any time you want the user to see the companyCODE on a form or Report you use a text box control bound to the companyCode foreign key.
Any time you want the user to see the Company Name then you use a similar combo box which will take the CODE and DISPLAY the NAME.

You have only ever stored the CODE in the second table but the NAME is available any time you want it.



'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[pretzal]

Thank you! That is what I needed to know. I appreciate you help!