Multiple lines in table, need one line in query returned

[talliaman64]

I am a relative newb to Access so please excuse this question which I am sure is very simple. To simplify, I have a table that contains the info below.

Number	Cert	Name
123456789	1234	Brian
123456789	1235	Brian
123123123	1236	Janell
123123123	1237	Janell
456456456	1238	Amy
123456789	1239	Brian

What I want returned is the number, name and cert all in one line, like below

123456789 Brian 1234 1235 1239
123123123 Janell 1236 1237


456456456 Amy 1238

Thank you in advance for your help!!!

[June7]

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

[talliaman64]

I guess I am just not smart enough to figure out the SQL, all my data is in one table, if that matters. here is what I wrote.
SELECT SSN, CONCATRELATED("CERT","Z2CIGNA","CERT = " & "CERT")
FROM Z2CIGNA;
I get a compile error.

[June7]

Did you put the VBA code in a general code module?

Do you have a table of unique SSN/Name records? Are these employees? Are [SSN] and [Number] fields both number type?

The query is built with [Employess table] but the function will pull data from Z2CIGNA table.

SELECT SSN, [Name], ConcatRelated("CERT", "Z2CIGNA", "[NUMBER] = " & [SSN]) FROM [Employees table];

[talliaman64]

Thank you for your help, I copied and pasted the above and now I get a compile error.
SELECT SSN, [Name], ConcatRelated("CERT", "Z2CIGNA", "[NUMBER] = " & [SSN]) FROM [Employees table];

Yes, these are employees and all number fields are the same type.
I do not have any VBA code built, this may be over my head, sorry!

[June7]

Compile error where?

This certainly won't work if you don't have any VBA code. What exactly about Allen Browne's instructions do you not understand?

Did you build a query object with the SQL statement?

The [SSN] and [Number] fields are both number data type?