Take data from 'group by' that will put all the nonduplicate data into fields, help?

[offie]

Not very sure on your to title this but here is what I have

Project ControlNumber
1 30
1 31
1 32
1 33
2 45
2 46
2 47
3 56

Eventually I want to get

Project ControlNumber
1 30,31,32,33
2 45,43,47
3 56

I've look around and there only seems to be slq solutions that I've tried but haven't gotten to work, partly because I have no idea what I'm doing, What I figured would be easier to do is to get this

Project CN1 CN2 CN3 CN4 CN5 ect... (as the fields)

EX:
1 30 31 32 33 all in one row

And then use & to combine it into the format CN1,CN2,CN3... as what would be in the rows.

Now my question is how to do the first part (1 30 31 32 33), is this possible?




Sorry I'm new to all of this I've only been using access for a week :/
Thanks!

[pbaldy]

I'd use a function like this:

http://allenbrowne.com/func-concat.html

As to your thought on layout, it would normally be a big mistake to have fields like CN1 CN2 CN3 etc. The concept is called normalization:

Fundamentals of Relational Database Design -- r937.com

[offie]

See the thing is that I've tried this, it doesn't work and i have no idea why..

noob here

Edit:
So I did it all again and when I try to run the query it tells me that ConcatRelated() is an undefined funtion, even though I went into the VBA application, copied it exactly, complied and saved. What could be going wrong here?


Also, I don't think it would be normalizing because I have to take all thos individual values and make tables with them as the primary key

[pbaldy]

Make sure you copied the function into a standard module, not behind a form or report, and that the module does not have the same name as the function, which will confuse Access.

[offie]

I've gotten almost everything to work, but when I try to specify the field [ControlNumber] as the first part of the function for some reason it does not let me. I get the error message:
"You tried to execute a query that does not include the speficifed expression ___ as part of the aggregate function"
If I type in [ContractNumber] (the same as Project) then it works but obvioulsy with the wrong number, it even shows the right amount of entires.
I tried to enter ([co]![ControlNumber]) (thats the table from where it comes) but I get another error message.
Any Ideas?
Thanks so much!

Edit: Figured it out, Thanks!

[pbaldy]

No problemo!