If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Multiple records in table to display single records
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! |
#3
|
|||
|
|||
Multiple records in table to display single records
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! |
#4
|
|||
|
|||
Multiple records in table to display single records
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! |
#5
|
|||
|
|||
Multiple records in table to display single records
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! |
#6
|
|||
|
|||
Multiple records in table to display single records
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! |
#7
|
|||
|
|||
Multiple records in table to display single records
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! |
#8
|
|||
|
|||
Multiple records in table to display single records
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! |
Thread Tools | |
Display Modes | |
|
|