Do I use a lookup in a table or a query

[Freeflow]

I have two tables that are simple lists of Company name and Company Address. Because these are large companies, multiple names can use the same address (e.g. a holding company with many 'Trading as' names).

tblCoName has fields of ID and Long-Name with ID being an autonumber allocated by access

tblCoAddress has field of ID and Address with ID being an autonumber allocated by access

I want to use information from these two tables to create a third table that has a unique name (Site-Name) for a name and address combination

tblCoFunction with fields of Site-Name, Long name, Address

What is the best way to add the Long-Name and Address information.

My current method is to use lookup fields for the Long-Name and Address data such that I can select the name and the address and then manually enter the (made up) site function.

However I have read that using lookup in tables in this way is a bad way to go in access but I'm stumped as to how to do it differently.

Any pointers are appreciated.

 

[Freeflow]

Typical. I find the solution within minutes of posting.

The reason I was asking the question is that I was getting to the point of finding numbers in tables rather than the values (names and addresses) I was expecting. This was happening where I was using a lookup to pull a value from another table where that value had been obtained via a lookup.

The relationships between my tables were correct.

It took me a long while to suss that the fields in the query needed to go back to the original tables where the value was entered and that the relationship links kept everything straight.

I'm still happy for folks to contribute any pointers.

 

[James Deckert]

>>I want to use information from these two tables to create a third table that has a unique name (Site-Name) for a name and address combination
For what purpose? Usually a calculated field is not to be stored in a table. If you can calculate it there is no need to store it.
This can be easily done with an append query. Is this a one-time thing or something you want to do repitively? This is the answer to your question, but what is the purpose is important to answer first.

 

[Freeflow]

For what purpose? Usually a calculated field is not to be stored in a table.

Thanks for the response.

My newbie status to Access probably means I am not making myself clear.

The site name is not calculated, it is user defined. Here is an example from my data (Edit Sorry the columns don't line up in the post the same way as the do in the editor)

Site-Name Post-Name Post-Address
Bayer (DE 13342, Berlin) Bayer Pharma AG DE 13342 Berlin
Bayer(Bergkamen) Bayer Pharma AG Ernst Schering Strasse 14 D-59192 Bergkamen Germany
BAYER (D-10589, Berlin) Bayer Pharma AG Max Dohrn-Strasse 8, D-10589, Berlin, Germany

by keeping addressses seperate from the company name it is easier to spot duplicate addresses to check if such duplication is correct or if our data is out of data due to a company name change/ e.g.

Site-Name Post-Name Post-Address
Rhodia (Rousillon) Rhodia Opérations SAS Rue Gaston Monmousseau, F-38150, Rousillon, BP 66 France
Novacyl (Roussillon) Novacyl Rue Gaston Monmousseau, F-38150, Rousillon, BP 66 France

The site names provide a handy shorthand for internal discussion (email etc) but also provide clarity where sites are used in other tables and queries. Both of the above are 'VERY IMPORTANT' issues regarding the database functionality and its user friendliness.


The problem I now see as very typical of what novice users run into using their excel knowledge model in an Access environment.

When I first created the site table I used table lookups to pick the Post-Name and Post-Address which worked fine. However, at the next step, to associate site names to drug substances the lookup combo box was set to use all three field from the site table, the Post-Name and Post-Address columns just appeared as numbers. This was very confusing and initially I thought I had just done something wrong and rebuilt the database only to get the same results. After a lot of searching I found articles on why table lookups are a bad idea in Access but nowhere did I find a clear example of how to replace the lookup with a query. Once I knew that queries were the way to go I eventually found the answer by trial and error. So I'm now going back and replacing the Table based lookups with query based lookups.

I'm now wrestling with relationships as I'm trying to define a query but keep getting an error message that there is no relationship or that the table is open, but I'll post a separate question on this when I get to slightly better understanding of what relationships do.

 

[gemma-the-husky]

if you set a number field in a table to use a combo box as a default, instead of seeing the number in the table you see the lookup value.

whenever you then add the field to a form you automatically get the combo box.

Personally I prefer to see the "real" value in the table, and to make my own combo box if I need one. Often I want a different query to the automatic one anyway.

Even worse than the lookup is the "caption" property though!!!