By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

Combine Records(ROWS) together in Access

ryanvmcgee
P: 5
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?
Feb 5 '07 #1
Share this Question
Share on Google+
10 Replies


ryanvmcgee
P: 5
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?
Feb 5 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 5 '07 #3

NeoPa
Expert Mod 15k+
P: 31,492
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 ).
Feb 6 '07 #4

maxamis4
Expert 100+
P: 295
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.
Feb 6 '07 #5

ryanvmcgee
P: 5
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.
Feb 7 '07 #6

ryanvmcgee
P: 5
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 ).
Feb 7 '07 #7

ryanvmcgee
P: 5
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
Feb 7 '07 #8

NeoPa
Expert Mod 15k+
P: 31,492
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.
Feb 8 '07 #9

NeoPa
Expert Mod 15k+
P: 31,492
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 ;)
Feb 8 '07 #10

NeoPa
Expert Mod 15k+
P: 31,492
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).
Feb 8 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.