Multiple records in table to display single records

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!

Sorry, got the names mixed up!

"Hurrikane4" wrote:

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 Hal Jones
123 Ann Jones
231 Tim 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!

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.[b] & " " & tblName.[C] to form single field from them.

Use criteria like this --
tblName_1.[b] & " " & tblName_1.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]

The above is for maximum of 4 names per account. The second name like this --
MyNames_2: tblName_1.[b] & " " & tblName_1.[C]

Use criteria like this --
tblName.[b] & " " & tblName.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]


--
Build a little, test a little.


"Hurrikane4" wrote:

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!

If you don't need the values in separate columns then this function might
help http://www.rogersaccesslibrary.com/f...sts.asp?TID=16.

--
Duane Hookom
Microsoft Access MVP


"Hurrikane4" wrote:

Sorry, got the names mixed up!

"Hurrikane4" wrote:

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 Hal Jones
123 Ann Jones
231 Tim 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!

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" wrote:

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.[b] & " " & tblName.[C] to form single field from them.

Use criteria like this --
tblName_1.[b] & " " & tblName_1.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]

The above is for maximum of 4 names per account. The second name like this --
MyNames_2: tblName_1.[b] & " " & tblName_1.[C]

Use criteria like this --
tblName.[b] & " " & tblName.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]


--
Build a little, test a little.


"Hurrikane4" wrote:

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!

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" wrote:

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" wrote:

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.[b] & " " & tblName.[C] to form single field from them.

Use criteria like this --
tblName_1.[b] & " " & tblName_1.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]

The above is for maximum of 4 names per account. The second name like this --
MyNames_2: tblName_1.[b] & " " & tblName_1.[C]

Use criteria like this --
tblName.[b] & " " & tblName.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]


--
Build a little, test a little.


"Hurrikane4" wrote:

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!

Mr. Hookom, that worked, THANK YOU VERY MUCH!

"Duane Hookom" wrote:

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" wrote:

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" wrote:

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.[b] & " " & tblName.[C] to form single field from them.

Use criteria like this --
tblName_1.[b] & " " & tblName_1.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]

The above is for maximum of 4 names per account. The second name like this --
MyNames_2: tblName_1.[b] & " " & tblName_1.[C]

Use criteria like this --
tblName.[b] & " " & tblName.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]


--
Build a little, test a little.


"Hurrikane4" wrote:

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!

Mr. Dewey,

I'm not sure what I was doing incorrectly, but I wasn't able to get this to
run.
Thank you for your assistance.

"KARL DEWEY" wrote:

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.[b] & " " & tblName.[C] to form single field from them.

Use criteria like this --
tblName_1.[b] & " " & tblName_1.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]

The above is for maximum of 4 names per account. The second name like this --
MyNames_2: tblName_1.[b] & " " & tblName_1.[C]

Use criteria like this --
tblName.[b] & " " & tblName.[C] AND tblName_2.[b] & " " &
tblName_2.[C] AND tblName_3.[b] & " " & tblName_3.[C]


--
Build a little, test a little.


"Hurrikane4" wrote:

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!