Separate records by column

[Vassago]

I have a table set up as the following:

ssn account
01234567 1234567890
01234567 9876543210
01234567 7539514562
98765432 8765432109
98765432 9876541230

and so on...

I would like to create a query that will split the records up like this:

ssn account1 account2 account3
01234567 1234567890 9876543210 7539514562
98765432 8765432109 9876541230

and so on...

It can be in any format, either query or report, I just have to be able to export it into a text file in the same format. Does anyone have any ideas?

Thanks!

Vassago

 

[WayneRyan]

Hi Vassago,

You aren't going to get your query to produce:

ssn      account1   account2   account3   account4
01234567 1234567890 9876543210 7539514562
98765432 8765432109 9876541230
65432123 8787878787
01234444 1234567890 9876543210 7539514562 7539514562

Querys can't have a variable number of fields.

If you use a recordset, you can produce the ssn followed
by a "special string".

Your query has two fields:

ssn
Accounts: fnGetAccounts([ssn])

Public Function fnGetAccounts(TheSSN As Long) As String
Dim Temp As String
Dim rst As DAO.Recordset

Temp = ""

Set rst = CurrentDb.OpenRecordset("Select * From YourTable where [SSN] = " & TheSSN)
While Not rst.EOF And Not rst.BOF
   Temp = Temp & rst!Account & " "
   rst.MoveNext
   Wend
fnGetAccounts = Temp
End Function

That will produce the above output - minus the headings.
Hope that gets you started.

Wayne

 

[Ron_dK]

That's a pretty clever piece of code Wayne. I was looking for something like this for years , thanks for that.
The only thing I changed is the Query :

SELECT TbAccount.SSN, fnGetAccounts([SSN]) AS Accounts
FROM TbAccount
GROUP BY TbAccount.SSN, fnGetAccounts([SSN]);

Group by to see only one SSN with many account numbers.

Cheers, Ron

 

[Vassago]

Very nice coding Wayne. You always manage to come through.

I appreciate everyone's help.

 

[WayneRyan]

Vassago,

Glad to help. Good to see you again.

Wayne

 

[Bee]

That's a pretty clever piece of code Wayne. I was looking for something like this for years , thanks for that.
The only thing I changed is the Query :

SELECT TbAccount.SSN, fnGetAccounts([SSN]) AS Accounts
FROM TbAccount
GROUP BY TbAccount.SSN, fnGetAccounts([SSN]);

Group by to see only one SSN with many account numbers.

Cheers, Ron

I tried this code; however, it did not know how to get it to work. I wonder if anyone can help please?

What is this: rst!Account.
I don't understand the use of !

Also, what does this line do please?
fnGetAccounts = Temp

 

[lala]

Vassago,

Glad to help. Good to see you again.

Wayne

this is brilliant!!!!

only thing, is there a way to put commas there instead of spaces?
i did, but then it puts a comma at the end as well
how can i avoid that?

it might be petty to you, guys, but i'm a perfectionist and i'd appreciate it if someone can help

thank you!!

 

[Starman]

Change

Temp = Temp & rst!Account & " "

to

Temp = Temp & rst!Account & ", "

 

[lala]

I did
But then I get a comma at the end, that's what I said the problem was

 

[boblarson]

I did
But then I get a comma at the end, that's what I said the problem was

Just use this then:

Temp = Temp & rst!Account & ", "

and then at the end of where you are assigning the value:

fnGetAccounts = Temp

Change it to this:

fnGetAccounts = Left(Temp, Len(Temp)-2)

 

[lala]

Of course, how didn't I think of that
Thank you, you are so smart, answer half of my questions here

 

[boblarson]

...you are so smart

No, just lots of experience acquired