First, create a ranking query so you have names ranked within the account.
== qrnkHurrikane ======
SELECT tblHurrikane.AccountNumber,
tblHurrikane.FirstName,
tblHurrikane.LastName,
Count(tblHurrikane.AccountNumber) AS Rank
FROM tblHurrikane AS tblHurrikane_1
INNER JOIN tblHurrikane
ON tblHurrikane_1.AccountNumber = tblHurrikane.AccountNumber
WHERE ((([tblhurrikane_1].[FirstName] & [tblhurrikane_1].[LastName])
<=[tblhurrikane].[FirstName] & [tblhurrikane].[LastName]))
GROUP BY tblHurrikane.AccountNumber,
tblHurrikane.FirstName,
tblHurrikane.LastName;
Create a table [tblNums] with a single numeric field [num] and values
1,2,3,4,...
Then create a crosstab with SQL of:
TRANSFORM First(IIf([Num]=1,[FirstName],[LastName])) AS Expr2
SELECT qrnkHurrikane.AccountNumber
FROM qrnkHurrikane, tblNums
WHERE (((tblNums.Num) Between 1 And 2))
GROUP BY qrnkHurrikane.AccountNumber
PIVOT IIf([Num]=1,"fn","ln") & [Rank] In ("fn1","ln1","fn2","ln2");
Results should be
AccountNumber fn1 ln1 fn2 ln2
123 Ann Jones Hank Jones
231 Mike Smith
321 Jim John
--
Duane Hookom
Microsoft Access MVP
Hurrikane4 said:
Sorry for the delay, I was out a couple of days.
The maximum per account is 2 names.
I've tried using the code below but don't seem to have any success.
When I put the source table in design view, the name appears as "table1" and
when I put in the same table again, the name appears as "table1_1".
I prefer having the names in separate columns, so I'm testing this on just
the first name only.
KARL DEWEY said:
What is your maximum per account? I know of one way without creating a temp
table and crosstab but it gets messy if lots of names per account.
In query design view put the table in the space above the grid as many time
as you have possible names per account. Left join all from the first one on
account number.
You need to combine the First and Last names like this -- MyNames_1:
tblName. & " " & tblName.[C] to form single field from them.
Use criteria like this --
<>tblName_1. & " " & tblName_1.[C] AND <>tblName_2. & " " &
tblName_2.[C] AND <>tblName_3. & " " & tblName_3.[C]
The above is for maximum of 4 names per account. The second name like this --
MyNames_2: tblName_1. & " " & tblName_1.[C]
Use criteria like this --
<>tblName. & " " & tblName.[C] AND <>tblName_2. & " " &
tblName_2.[C] AND <>tblName_3. & " " & tblName_3.[C]
--
Build a little, test a little.
:
My table has 3 columns, A is account numbers, B is first name, C is last name.
If an account has 2 owners, the account number is listed twice in C1, like
this:
A B C
123 Hank Jones
123 Ann Jones
231 Mike Smith
321 Jim John
I want to query this table to display the names side by side if there is
more than one owner, so that my data appears like this:
A B C D E
123 Hal Jones Ann Jones
231 Tim Smith
321 Jim John
Help please!
