Separate records by column

V

Vassago

Former Staff Turned AWF Retiree
Local time
Yesterday, 19:37
Joined
Dec 26, 2002
Messages
4,696
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
 
Hi Vassago,

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

Code: 
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])

Code: 
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
 
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
 
Very nice coding Wayne. You always manage to come through.

I appreciate everyone's help.
 
rak said:
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
Click to expand...
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
 
WayneRyan said:
Vassago,

Glad to help. Good to see you again.

Wayne
Click to expand...

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!!
 
Change

Code: 
Temp = Temp & rst!Account & " "

to

Code: 
Temp = Temp & rst!Account & ", "
 
I did
But then I get a comma at the end, that's what I said the problem was
 
lala said:
I did
But then I get a comma at the end, that's what I said the problem was
Click to expand...

Just use this then:
Code: 
Temp = Temp & rst!Account & ", "

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

Code: 
fnGetAccounts = Temp

Change it to this:

Code: 
fnGetAccounts = Left(Temp, Len(Temp)-2)
 
Of course, how didn't I think of that
Thank you, you are so smart, answer half of my questions here
 
You must log in or register to reply here.

Similar threads

E
Output data from tables into a directory or phonebook
Replies
13
Views
521
MarkK
MarkK
S
Monaco SQL Editor Unwanted Side Effect
Replies
17
Views
2,482
isladogs
isladogs
T
Back End Read-Only Issue
2
Replies
26
Views
2,110
Gasman
Gasman
T
Solved Foxpro IDE
Replies
16
Views
1,988
arnelgp
arnelgp
T
Report in unbound Form needs dynamic Recordsource and Filter
Replies
15
Views
1,164
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom