Question Merging Fields in lookup

[Neo-fite]

Hello, I'm VERY new to working in Access 2007, so this might be a stupid question.

Is it possible to merge the FirstName and LastName fields from 1 table when using a multi-value lookup in a different table? If so, how?

 

[boblarson]

This is important so listen carefully -

Do NOT use lookups directly at table level. You can use them on FORMS but not within tables. See here for why using them at table level is considered by most of us to be evil...
http://www.mvps.org/access/lookupfields.htm

 

[Neo-fite]

I'm sure there's programming/theories that I will learn, in addition to this. However, I am just learning (via a book I am reading). I am only in the early stages learning about tables and relationships, and haven't gotten into Forms, Queries, etc...

In my playing outside the lines and quest to learn how this thing works, I encountered that scenario. That being said, and known to be problematic...is it possible?

 

[Galaxiom]

Above and beyond Bob's sound advice to avoid using table level lookups, I would further advise that you DO NOT use MultiValue Fields.

Firstly, they are far, far harder to manipulate and query than the conventional alternative of using related tables.

Secondly, they are an Access only feature that has to be completely redesigned if you want to upsize to a server based backend.

Also avoid using the Caption property of the field in tables. Keep tables simple. They just store data.

Don't waste your time with this kind of stuff and get on with what matters. Make sure you thoroughly understand normalization.

 

[Neo-fite]

OK, so I'm trying to accomplish a similar situation on a query.

I have a table that each row contains 10 different customer ID's. I then have a single table that has the customer ID's and associated name. How can I create a query that will pull in the name for each of the 10 customers based on the ID?

 

[Galaxiom]

I have a table that each row contains 10 different customer ID's.

That sounds very much like a denormalized data structure.

 

[Neo-fite]

That sounds very much like a denormalized data structure.

Yeah, I assume it's not normal, but it's just the Top 10 Customers for that Quarter.

 

[speakers_86]

Yeah, I assume it's not normal, but it's just the Top 10 Customers for that Quarter.

Thats still not normalized. The top customers of the quarter probably wont be stored in a table. It should be computed using a set of queries. Queries have a top % or top n feature.

But to answer your question, use the query design, add both tables in question, make sure a relationship exists between customer id in one table and customer name in the other table. All fields from the top ten customers field, then add the customer last name from the other table. That will give you the name for one of your top ten customers. Perform these steps again only this time use the customer table and the query as the inputs and proceed to find the name of the second top customer. So on and so forth.

 

[Neo-fite]

Thats still not normalized. The top customers of the quarter probably wont be stored in a table. It should be computed using a set of queries. Queries have a top % or top n feature.

But to answer your question, use the query design, add both tables in question, make sure a relationship exists between customer id in one table and customer name in the other table. All fields from the top ten customers field, then add the customer last name from the other table. That will give you the name for one of your top ten customers. Perform these steps again only this time use the customer table and the query as the inputs and proceed to find the name of the second top customer. So on and so forth.

Thanks, I'll give this a try.

 

[Neo-fite]

OK, so I'm working with Forms. I have a Customer table and a Form that I am using to fill Volume table.

I have created a Combo box to "lookup" and select the Customer Name from the associated table. However, when I look at the datasheet of the Volume table, it is filled with the Customer ID rather than the Name.

How do I change that so the Name fills the datasheet?

 

[boblarson]

Are you talking about the datasheet FORM or just when you open the table to look at it? It SHOULD be storing the ID and NOT the name in the table and when you open the table it should show the ID and not the name of the customer. Now, if we're talking about the form, then you need to set the combo's row source to select both the ID an the name from the customers table and then you set the column count of the combo to 2 and the column widths property of the combo to 0";2" in order to hide the ID but display the name.