Several records in one row??

[laina222]

Is there a way to create a query where one table has several values that need to be added to a row in another table? For example, let's say I have one table with address and customer information, and another table that has items that the customer sells and their ID. So it'd be something like this:
tblCustomer:
CustomerID, CustomerName, CustomerAddress
tblItemsSold:
ID, CustomerID, ItemSold

ID would be the Access designated Primary Key. What I want in my results is something like this:

CustomerID, CustomerName, CustomerAddress, ItemSold1, ItemSold2, ItemSold3

So if tblItemsSold has more than one item per CustomerID, I want it to show up in the same row.

Thanks for any help in advance!

 

[HitechUser]

laina222,

This can be done... Except the data would be concatenated in the final column. You will not be able to have N number (variable number) of columns after the CustomerAddress.

Example.

Instead of this...

CustomerID, CustomerName, CustomerAddress, ItemSold1, ItemSold2, ItemSold3


It would be the following where ItemSoldAll would have the value of ItemSold1 & ItemSold2 & ItemSold3 & etc.

CustomerID, CustomerName, CustomerAddress, ItemSoldAll

Is this what you are after?????

 

[dhookom]

There is a sample Generic Concatenate function at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

with sample usage.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[laina222]

So HitechUser, you're saying that all the values would be in the last field? These are text values, of course, and not numbers.

I'd like to try it and see...do you have any examples of SQL for it?

 

[HitechUser]

I have used the code posted here Tek-Tips. See fneily reply. It works great!!.

Also please take note of dhookom reply. Although I have not used his cancatenate function his Query by Form is the BEST. So have have look at his example as well. Use the one which works best for you.

A note to dhookom. Thank you, Thank you, Thank you for providing EXCELLENT tool with your Query by Form.

 

[dhookom]

HitechUser, thanks for the kind words. I use the Query By Form in almost every app I build. I usually charge $500+ when implementing it in a project.

I would shy away from the fneily code since it doesn't look like it:
-handles numeric vForFldVal values
-doesn't provide for different "delimiters"
-doesn't allow for multiple output fields ie: [FirstName] & ' ' & [LastName]

There is similar but more robust code at

http://www.mvps.org/access/modules/mdl0004.htm

if you remember to add references to "DAO."

Of course, I prefer my sample since it has more functionality. Keep in mind however that none of these is very fast. There is an FAQ I believe in this forum of a mostly SQL based solution that could be quicker.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]