I have a table that does not have a primary key set. Because of this, I have records with duplicate customer numbers. I can get rid of these easy enough, but I don't want to lose the data in any of the fields. What I need to do is create a query or a couple of queries that will take all the data for each record and concatenate it together when there are duplicates. Here is an example:


CUSTNO LOTNO PHASENO
123 5 150
124 14 678
124 15 700
125 32 86
After running this query I need to build, the new table would look like this:


CUSTNO LOTNO PHASENO
123 5 150
124 14/15 678/700
125 32 86

Any suggestions?

Thank you.

(By the way, I'm using Access 2007 and I'm trying to avoid resorting to using code for this)

Hi,

It is possible to do. You will need to create a function in VBA that you are gonna use as an "agregate" function in a query. It is slow to run but does the job. I'll post the code in a couple of hour if no one as a solution until then. (I can't access it from this computer...).

I think you will have to create a new table though in order for it to work, like a SELECT .... INTO ...

Simon B.

Hi,

It is possible to do. You will need to create a function in VBA that you are gonna use as an "agregate" function in a query. It is slow to run but does the job. I'll post the code in a couple of hour if no one as a solution until then. (I can't access it from this computer...).

I think you will have to create a new table though in order for it to work, like a SELECT .... INTO ...

Simon B.
Hey Simon, thanks for the reply.

I can do it in code. I know that. I just want to see if anyone knows a solution involving queries only.

Thanks