By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,153 Members | 2,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,153 IT Pros & Developers. It's quick & easy.

Combining values in queries

P: n/a
MS Access 97 query:

Given 2 simple tables

Person
PersonRef (text) (primary key)
Surname (text)

e.g
PersonRef Surname
P1 SMITH
P2 JONES

Forename
Primarykey (autogenerated)
PersonRef (text)
Forename (text)

e.g
Primarykey PersonRef Forename
1 P1 ANDREW
2 P1 PETER
3 P2 ALICE
4 P2 SARAH

I want to construct a query using a single SQL statement that will
return the following:

PersonRef Surname Forename(s)
P1 SMITH ANDREW PETER
P2 JONES ALICE SARAH

i.e. to have an expression that combines the forename values into a
single column.
Is this possible? Help please.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"badbetty" <bc*@genie.co.uk> wrote in message
news:5d*************************@posting.google.co m...
MS Access 97 query:

Given 2 simple tables

Person
PersonRef (text) (primary key)
Surname (text)

e.g
PersonRef Surname
P1 SMITH
P2 JONES

Forename
Primarykey (autogenerated)
PersonRef (text)
Forename (text)

e.g
Primarykey PersonRef Forename
1 P1 ANDREW
2 P1 PETER
3 P2 ALICE
4 P2 SARAH

I want to construct a query using a single SQL statement that will
return the following:

PersonRef Surname Forename(s)
P1 SMITH ANDREW PETER
P2 JONES ALICE SARAH

i.e. to have an expression that combines the forename values into a
single column.
Is this possible? Help please.

A flexible and easy-to-understand way to do this is to create a function in
a module - then you can call the function in a query.

The thing that crosses my mind first, though, is there seems to be no
specified order for the forenames in the forname table. Unless, of course,
you use the primary key which is what my example does.

**Query**
SELECT PersonRef, Surname,
GetForenames([PersonRef]) AS Forenames
FROM Person

**Function**
Public Function GetForenames(strPsnRef As String) As String

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTmp As String

strSQL = "SELECT Forename FROM Forename WHERE " & _
"PersonRef=""" & strPsnRef & """ " & _
"ORDER BY Primarykey"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

While Not rst.EOF
strTmp = strTmp & Trim(Nz(rst!Forename, "")) & " "
rst.MoveNext
Wend

GetForenames = Trim(strTmp)

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
GetForenames = "** Error **"
Resume Exit_Handler

End Function
Nov 12 '05 #2

P: n/a
Hi Bad Betty (best kind of Betty there is!)

Without actually trying it out myself, it strikes me that a crosstab query
might handle this if it's just for display.

Otherwise, Fletcher function looks like it would do the job just fine.

Andrew

"badbetty" <bc*@genie.co.uk> wrote in message
news:5d*************************@posting.google.co m...
MS Access 97 query:

Given 2 simple tables

Person
PersonRef (text) (primary key)
Surname (text)

e.g
PersonRef Surname
P1 SMITH
P2 JONES

Forename
Primarykey (autogenerated)
PersonRef (text)
Forename (text)

e.g
Primarykey PersonRef Forename
1 P1 ANDREW
2 P1 PETER
3 P2 ALICE
4 P2 SARAH

I want to construct a query using a single SQL statement that will
return the following:

PersonRef Surname Forename(s)
P1 SMITH ANDREW PETER
P2 JONES ALICE SARAH

i.e. to have an expression that combines the forename values into a
single column.
Is this possible? Help please.

Nov 12 '05 #3

P: n/a
"Fletcher Arnold"
Many thanks for your solution. It's now in use!
Nov 12 '05 #4

P: n/a
I'm not sure if this will help but...you can concantenate three fields into
one "concantenated field. Eg. the separate fields of surname and G1 and G2
("Smith" and "John" and "Peter" can be combined in a query to show "Smith,
John Peter". To do this...open a query in design view and add the table that
contains the fields you want to combine. Let's say you've got a table called
"Names". In one field of your query type sql for ex. [Names].[Surname]&",
"&[Names].[G1]&" "&[Names].[G2]. I'm not sure how advanced you are but if
not very...this is an easy way to do it. Some of the other replies used VB
as a solution and that's beyond me.

"badbetty" <bc*@genie.co.uk> wrote in message
news:5d*************************@posting.google.co m...
MS Access 97 query:

Given 2 simple tables

Person
PersonRef (text) (primary key)
Surname (text)

e.g
PersonRef Surname
P1 SMITH
P2 JONES

Forename
Primarykey (autogenerated)
PersonRef (text)
Forename (text)

e.g
Primarykey PersonRef Forename
1 P1 ANDREW
2 P1 PETER
3 P2 ALICE
4 P2 SARAH

I want to construct a query using a single SQL statement that will
return the following:

PersonRef Surname Forename(s)
P1 SMITH ANDREW PETER
P2 JONES ALICE SARAH

i.e. to have an expression that combines the forename values into a
single column.
Is this possible? Help please.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.