Combine Records(ROWS) together in Access

[ryanvmcgee]

Hello,
I am fairly new at doing advanced databases outside of excel.

I am trying to combine records in a database I will be using, none of the, CONCAT Queries or scripts I have found seem to work quite right.

What I need is something to combine records like this:

ID Machine
AAG99999 CANON 225
ADL900221 OLIVETTI F231
ADL900221 OLIVETTI F232

Into this:

ID Machine:
AAG99999 CANON 225
ADL900221 OLIVETTI F231, OLIVETTI F232

I know it seems like it would be pretty simple, but I have yet to figure out how to do it?

Can Anyone Help?

[ryanvmcgee]

I meant to mention that I have already tried a few of the examples given elsewhere on this site and others. I am using access 2000.

Hello,
I am fairly new at doing advanced databases outside of excel.

I am trying to combine records in a database I will be using, none of the, CONCAT Queries or scripts I have found seem to work quite right.

What I need is something to combine records like this:

ID Machine
AAG99999 CANON 225
ADL900221 OLIVETTI F231
ADL900221 OLIVETTI F232

Into this:

ID Machine:
AAG99999 CANON 225
ADL900221 OLIVETTI F231, OLIVETTI F232

I know it seems like it would be pretty simple, but I have yet to figure out how to do it?

Can Anyone Help?

[MMcCarthy]

Hello,
I am fairly new at doing advanced databases outside of excel.

I am trying to combine records in a database I will be using, none of the, CONCAT Queries or scripts I have found seem to work quite right.

What I need is something to combine records like this:

ID Machine
AAG99999 CANON 225
ADL900221 OLIVETTI F231
ADL900221 OLIVETTI F232

Into this:

ID Machine:
AAG99999 CANON 225
ADL900221 OLIVETTI F231, OLIVETTI F232

I know it seems like it would be pretty simple, but I have yet to figure out how to do it?

Can Anyone Help?

Hi Ryan

databases are not designed to work this way. The first example is the correct way of storing this information.

For what purpose do you need the data displaid the other way.

Mary

[NeoPa]

You'll see from some of my posts in these threads that I'm very much with Mary on that point.
However, if you absolutely do need to do it, there are a couple of threads which deal with this issue.
(Combining Multiple Rows of one Field into One Result) & (Combining Rows-Opposite of Union ).

[maxamis4]

My only recommendation is to either add a query or do it programmatically through forms. But I don't see the need in this unless you are trying to generate a unique id. For that I have always used vb. If you are intrested I can shoot you a couple ideas. let me know.

[ryanvmcgee]

Hi Ryan

databases are not designed to work this way. The first example is the correct way of storing this information.

For what purpose do you need the data displaid the other way.

Mary

I need it this way in order to upload products to a website.

I don't think I absolutly have to use access, that is what I know, but I am willing to attempt to learn something else. I have just started to Learn VB.

[ryanvmcgee]

Neopa,
I read your "Combining Rows-Opposite of Union" Posts.
Any Idea why it would say " Unidentified function Concat in Expression"?
I entered the code into a new module in VB and named it Concat(after changing the field names to the ones in my tables). Concat is showing under my Functions list.

Any Ideas?

You'll see from some of my posts in these threads that I'm very much with Mary on that point.
However, if you absolutely do need to do it, there are a couple of threads which deal with this issue.
(Combining Multiple Rows of one Field into One Result) & (Combining Rows-Opposite of Union ).

[ryanvmcgee]

I have enclosed five screen-shots of exactly what I typed in the module and in the sql. There is a shot of the error message as well.

Picture 3
Picture 4

You may have to zoom in to see the text

[NeoPa]

I have enclosed five screen-shots of exactly what I typed in the module and in the sql. There is a shot of the error message as well.
Picture 1
Picture 2
Picture 3
Picture 4
Picture 5

You may have to zoom in to see the text

The only thing I can think of (I checked out your pics and everything seems right) is that you hadn't compiled and saved the new module Concat before testing it. Running from SQL is not the same as invoking it directly - it needs the module to be saved first.

[NeoPa]

BTW When creating pictures to post - .Jpg is a good format to use, but come out of full screen mode and use Alt-PrintScreen to copy just the current window. This keeps the pictures smaller ;)

[NeoPa]

Another point to notice :
In your Picture 2 there is a staus in your watch window saying <Can't compile module> - You will need to sort out any compilation errors first of course (Use Alt-D, L to compile the project).