Query to concatenate records in a table with no key

[DanWallace]

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)

 

[SimonB1978]

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.

 

[DanWallace]

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