Special Crosstab

  • Thread starter Thread starter mj
  • Start date Start date
M

mj

Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those accounts. The
table is just two columns: AccountNumber, Name. There are
more than one person at some accounts but, when that's the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!
 
I don't know of any way to do this with just a query (but I'd be interested
to know if it can be done!). However you can do it by using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS [CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will have to write as a
public function in a VBA module. The code would look something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table names with the
correct ones for your database. I have assumed that the account number is a
long integer, and this will have to be changed too if it's a different data
type.
 
Thanks for the reply. I'm getting an compile error when I
run the SQL. Any thoughts?

My account number is actually in text format. The SQL
looks like this:

SELECT AccountNumber, CombineField([AccountNumber]) AS
[CombinedNames]
FROM tblAccountNames
GROUP BY tblAccountNames.AccountNumber;

And my module looks like this:

Public Function CombineField(txtAccount As txt) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select Name From tblAccountNames " & _
"Where AccountNumber =" & txtAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !Name & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function
-----Original Message-----
I don't know of any way to do this with just a query (but I'd be interested
to know if it can be done!). However you can do it by using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS [CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will have to write as a
public function in a VBA module. The code would look something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table names with the
correct ones for your database. I have assumed that the account number is a
long integer, and this will have to be changed too if it's a different data
type.

Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those accounts. The
table is just two columns: AccountNumber, Name. There are
more than one person at some accounts but, when that's the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!
Click to expand...


.
Click to expand...
 
There is a generic Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that can
work for all instances where you want to do this. I expect if you get a
compile error you should be more specific with the line that causes the
error. You may have trouble since your field name is the same as an object
property (Name). It may also be a case of not having a reference set to the
DAO object library.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


mj said:
Thanks for the reply. I'm getting an compile error when I
run the SQL. Any thoughts?

My account number is actually in text format. The SQL
looks like this:

SELECT AccountNumber, CombineField([AccountNumber]) AS
[CombinedNames]
FROM tblAccountNames
GROUP BY tblAccountNames.AccountNumber;

And my module looks like this:

Public Function CombineField(txtAccount As txt) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select Name From tblAccountNames " & _
"Where AccountNumber =" & txtAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !Name & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function
-----Original Message-----
I don't know of any way to do this with just a query (but I'd be interested
to know if it can be done!). However you can do it by using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS [CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will have to write as a
public function in a VBA module. The code would look something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table names with the
correct ones for your database. I have assumed that the account number is a
long integer, and this will have to be changed too if it's a different data
type.

Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those accounts. The
table is just two columns: AccountNumber, Name. There are
more than one person at some accounts but, when that's the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks. I think I'm almost there. I imported your module
and have set things up just like the example. The only
problem is that I'm getting a data type mismatch, probably
because my ID numbers are actually text (don't ask) and
somewhere in your module it must be doing something with
integers since you use ID numbers, but I'm not very good
with code yet so I can't quite figure out why I'm getting
the mismatch. Any thoughts?
-----Original Message-----
There is a generic Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane that can
work for all instances where you want to do this. I expect if you get a
compile error you should be more specific with the line that causes the
error. You may have trouble since your field name is the same as an object
property (Name). It may also be a case of not having a reference set to the
DAO object library.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thanks for the reply. I'm getting an compile error when I
run the SQL. Any thoughts?

My account number is actually in text format. The SQL
looks like this:

SELECT AccountNumber, CombineField([AccountNumber]) AS
[CombinedNames]
FROM tblAccountNames
GROUP BY tblAccountNames.AccountNumber;

And my module looks like this:

Public Function CombineField(txtAccount As txt) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select Name From tblAccountNames " & _
"Where AccountNumber =" & txtAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !Name & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function
-----Original Message-----
I don't know of any way to do this with just a query
Click to expand...
(but
I'd be interested
to know if it can be done!). However you can do it by using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS [CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will have to write as a
public function in a VBA module. The code would look something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table names with the
correct ones for your database. I have assumed that the account number is a
long integer, and this will have to be changed too if it's a different data
type.

Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those
Click to expand...
accounts.
The
table is just two columns: AccountNumber, Name. There are
more than one person at some accounts but, when
Click to expand...
that's
the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
WOW!! This worked GREAT when I changed my Account Numbers
to actual numbers. Unfortunately I could only do that as a
test because so many of my DBs have links to my account
list as numbers and there would be lots of maintenance to
do. If you have time to suggest how I can manipulate your
code to handle a text formatted ID, I'd be much
appreciative. Thanks!
-----Original Message-----
There is a generic Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane that can
work for all instances where you want to do this. I expect if you get a
compile error you should be more specific with the line that causes the
error. You may have trouble since your field name is the same as an object
property (Name). It may also be a case of not having a reference set to the
DAO object library.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thanks for the reply. I'm getting an compile error when I
run the SQL. Any thoughts?

My account number is actually in text format. The SQL
looks like this:

SELECT AccountNumber, CombineField([AccountNumber]) AS
[CombinedNames]
FROM tblAccountNames
GROUP BY tblAccountNames.AccountNumber;

And my module looks like this:

Public Function CombineField(txtAccount As txt) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select Name From tblAccountNames " & _
"Where AccountNumber =" & txtAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !Name & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function
-----Original Message-----
I don't know of any way to do this with just a query
Click to expand...
(but
I'd be interested
to know if it can be done!). However you can do it by using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS [CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will have to write as a
public function in a VBA module. The code would look something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table names with the
correct ones for your database. I have assumed that the account number is a
long integer, and this will have to be changed too if it's a different data
type.

Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those
Click to expand...
accounts.
The
table is just two columns: AccountNumber, Name. There are
more than one person at some accounts but, when
Click to expand...
that's
the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Your SQL in the Concatenate function might need to look something like:
Concatenate("SELECT FieldA FROM tblA WHERE textfield = """ & [textfield] &
"""")
note all the double-quotes.

--
Duane Hookom
MS Access MVP


mj said:
WOW!! This worked GREAT when I changed my Account Numbers
to actual numbers. Unfortunately I could only do that as a
test because so many of my DBs have links to my account
list as numbers and there would be lots of maintenance to
do. If you have time to suggest how I can manipulate your
code to handle a text formatted ID, I'd be much
appreciative. Thanks!
-----Original Message-----
There is a generic Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane that can
work for all instances where you want to do this. I expect if you get a
compile error you should be more specific with the line that causes the
error. You may have trouble since your field name is the same as an object
property (Name). It may also be a case of not having a reference set to the
DAO object library.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thanks for the reply. I'm getting an compile error when I
run the SQL. Any thoughts?

My account number is actually in text format. The SQL
looks like this:

SELECT AccountNumber, CombineField([AccountNumber]) AS
[CombinedNames]
FROM tblAccountNames
GROUP BY tblAccountNames.AccountNumber;

And my module looks like this:

Public Function CombineField(txtAccount As txt) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select Name From tblAccountNames " & _
"Where AccountNumber =" & txtAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !Name & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function
-----Original Message-----
I don't know of any way to do this with just a query (but
I'd be interested
to know if it can be done!). However you can do it by
using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS
[CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will
have to write as a
public function in a VBA module. The code would look
something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table
names with the
correct ones for your database. I have assumed that the
account number is a
long integer, and this will have to be changed too if
it's a different data
type.

message
Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those accounts.
The
table is just two columns: AccountNumber, Name. There
are
more than one person at some accounts but, when that's
the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!


.
Click to expand...


.
Click to expand...
Click to expand...
 
That worked PERFECTLY. Thanks for taking the time. I
really need to dive into this a little deeper. Thanks
again!
-----Original Message-----
Your SQL in the Concatenate function might need to look something like:
Concatenate("SELECT FieldA FROM tblA WHERE textfield = """ & [textfield] &
"""")
note all the double-quotes.

--
Duane Hookom
MS Access MVP


WOW!! This worked GREAT when I changed my Account Numbers
to actual numbers. Unfortunately I could only do that as a
test because so many of my DBs have links to my account
list as numbers and there would be lots of maintenance to
do. If you have time to suggest how I can manipulate your
code to handle a text formatted ID, I'd be much
appreciative. Thanks!
-----Original Message-----
There is a generic Concatenate function at
Click to expand...
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook
om,Duane that can
work for all instances where you want to do this. I expect if you get a
compile error you should be more specific with the line that causes the
error. You may have trouble since your field name is
Click to expand...
the
same as an object
property (Name). It may also be a case of not having a reference set to the
DAO object library.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thanks for the reply. I'm getting an compile error
Click to expand...
when
I
run the SQL. Any thoughts?

My account number is actually in text format. The SQL
looks like this:

SELECT AccountNumber, CombineField([AccountNumber]) AS
[CombinedNames]
FROM tblAccountNames
GROUP BY tblAccountNames.AccountNumber;

And my module looks like this:

Public Function CombineField(txtAccount As txt) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select Name From tblAccountNames " & _
"Where AccountNumber =" & txtAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !Name & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function
-----Original Message-----
I don't know of any way to do this with just a query (but
I'd be interested
to know if it can be done!). However you can do it by
using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS
[CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will
have to write as a
public function in a VBA module. The code would look
something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table
names with the
correct ones for your database. I have assumed that the
account number is a
long integer, and this will have to be changed too if
it's a different data
type.

message
Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those accounts.
The
table is just two columns: AccountNumber, Name. There
are
more than one person at some accounts but, when that's
the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi,


Since you declared you were interested...

To do it with just a query you can use an update query over a join.
Sure, an update query means you already have the "table" that would be
updated, it is made out of the table myInitialData:

SELECT DISTINCT AccountNo, iif( false, ' ', NULL) AS Concat INTO
myResult FROM myInitialData



The update query to run is then:

UPDATE myResult INNER JOIN myINitialData
ON myResult.AccountNo = myInitialData.AccountNo
SET myResult.Concat = (myResult.Concat + ', ' ) &
myInitialData.TheFieldOfNamesToConcat



and, as mentioned, the result is in the table myResult.

Sure, having a LIST aggregate would make it really easier.



Hoping it may help,
Vanderghast, Access MVP




Andrew Smith said:
I don't know of any way to do this with just a query (but I'd be interested
to know if it can be done!). However you can do it by using a custom
function in a query.

The SQL for the query would be something like this:

SELECT AccountNo, CombineField([AccountNo]) AS [CombinedNames]
FROM tblAccounts
GROUP BY tblAccounts.AccountNo;

This uses a function called CombineField that you will have to write as a
public function in a VBA module. The code would look something like this:

Public Function CombineField(lngAccount as Long) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strText As String
Dim strSQL as String

Set db = CurrentDb
strSQL = "Select AccountName From tblAccounts " & _
"Where AccountNo =" & lngAccount
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strText = strText & !AccountName & ", "
.MoveNext
Loop
End With

CombineField = Left(strText, Len(strText) - 2)

End Function

Obviously you will need to replace the field and table names with the
correct ones for your database. I have assumed that the account number is a
long integer, and this will have to be changed too if it's a different data
type.

mj said:
Hi. Thanks for any help on this. I have a table with
account numbers and names of people at those accounts. The
table is just two columns: AccountNumber, Name. There are
more than one person at some accounts but, when that's the
case, the account number is duplicated and the other
person's name is listed like this:

12345 John Smith
12345 Sandy Jones
45676 Wil Scott

I'm trying to create a query that will do this:

12345 John Smith, Sandy Jones
45676 Wil Scott

Any thoughts? Thanks!
Click to expand...
Click to expand...
 
Back
Top