Getting Multiple Owners Names for Single Pieces of Property

R

Ray Todd Jr

I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
 
C

colin_e

I believe the standard answer to this type of question in SQL would be to use
a subquery for the "Owners" field, then use CONCATENATE() to merge the
strings into a single result string for the column. Unfortunately I am having
major trouble getting Access to execute even simple subqueries successfully,
so I can't say this is likely to work in practice. You might have to
pre-generate an "Owners by HoueID" table, maybe temporarily, to get the
result you need.
 
R

Ray Todd Jr

Duane:

I got your module and installed it this morning and have been playing with
it all day. I haven't had any success in the larger scale, i.e., dealing
with only 1 or 2 fields, I understand how it works, however, with the larger
sql statements, I am lost.

With the below statement, I get the following error:

Syntax Error (Missing Operator) In Query Expression.

Can you point me in the correct direction on how to fix this?

Thanks,

Ray.

SELECT propertyID, CONCATENTATE ("SELECT FirstName & ' ' MiddleName & ' '
LastName & ' ' Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE (((taDEFENDANTTYPE.DefendantType)="Owner"))" as Property_Owner
FROM taDEFENDANTS



Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
Click to expand...
Click to expand...
 
D

Duane Hookom

I would expect to see something like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' MiddleName & ' ' & LastName & ' '
Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE taDEFENDANTTYPE.DefendantType='Owner' AND PropertyID = " &
[PropertyID] ) as Property_Owner
FROM taDEFENDANTS;

This depends on your table and field names and data types.




--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
Duane:

I got your module and installed it this morning and have been playing with
it all day. I haven't had any success in the larger scale, i.e., dealing
with only 1 or 2 fields, I understand how it works, however, with the larger
sql statements, I am lost.

With the below statement, I get the following error:

Syntax Error (Missing Operator) In Query Expression.

Can you point me in the correct direction on how to fix this?

Thanks,

Ray.

SELECT propertyID, CONCATENTATE ("SELECT FirstName & ' ' MiddleName & ' '
LastName & ' ' Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE (((taDEFENDANTTYPE.DefendantType)="Owner"))" as Property_Owner
FROM taDEFENDANTS



Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

Hello Duane:

I copied and pasted your code and still get the same syntax error. Below
are the datatype. Is there something else that you can see that I'm just
totally missing that is making this harder than it should be.

Again, thanks for your time.

Ray.

FirstName -Txt
MiddleName -Txt
LastName -Txt
Suffix -Txt
DefendantType-txt
PropertyID -Long
DefendantTypeID -Long

Duane Hookom said:
I would expect to see something like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' MiddleName & ' ' & LastName & ' '
Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE taDEFENDANTTYPE.DefendantType='Owner' AND PropertyID = " &
[PropertyID] ) as Property_Owner
FROM taDEFENDANTS;

This depends on your table and field names and data types.




--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
Duane:

I got your module and installed it this morning and have been playing with
it all day. I haven't had any success in the larger scale, i.e., dealing
with only 1 or 2 fields, I understand how it works, however, with the larger
sql statements, I am lost.

With the below statement, I get the following error:

Syntax Error (Missing Operator) In Query Expression.

Can you point me in the correct direction on how to fix this?

Thanks,

Ray.

SELECT propertyID, CONCATENTATE ("SELECT FirstName & ' ' MiddleName & ' '
LastName & ' ' Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE (((taDEFENDANTTYPE.DefendantType)="Owner"))" as Property_Owner
FROM taDEFENDANTS



Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Try create a parent query and a child query then use SQL like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' '
& Suffix FROM [ChildTableQuery] WHERE PropertyID = " & [PropertyID] ) as
Property_Owner
FROM [ParentTableQuery];

--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
Hello Duane:

I copied and pasted your code and still get the same syntax error. Below
are the datatype. Is there something else that you can see that I'm just
totally missing that is making this harder than it should be.

Again, thanks for your time.

Ray.

FirstName -Txt
MiddleName -Txt
LastName -Txt
Suffix -Txt
DefendantType-txt
PropertyID -Long
DefendantTypeID -Long

Duane Hookom said:
I would expect to see something like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' MiddleName & ' ' & LastName & ' '
Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE taDEFENDANTTYPE.DefendantType='Owner' AND PropertyID = " &
[PropertyID] ) as Property_Owner
FROM taDEFENDANTS;

This depends on your table and field names and data types.




--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
Duane:

I got your module and installed it this morning and have been playing with
it all day. I haven't had any success in the larger scale, i.e., dealing
with only 1 or 2 fields, I understand how it works, however, with the larger
sql statements, I am lost.

With the below statement, I get the following error:

Syntax Error (Missing Operator) In Query Expression.

Can you point me in the correct direction on how to fix this?

Thanks,

Ray.

SELECT propertyID, CONCATENTATE ("SELECT FirstName & ' ' MiddleName & ' '
LastName & ' ' Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE (((taDEFENDANTTYPE.DefendantType)="Owner"))" as Property_Owner
FROM taDEFENDANTS



:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

Duane:

OK. I keep getting all kind of errors (3075, 3061, etc, etc). As a result,
I am backing out to a *much* simpler query in order to work though the
problem, however, the resulting Property_Owner field is blank. Hopfully, I
am providing all of the info to help diagnose the problem.

Here is the SQL:

SELECT taPROPERTY.PropertyID, Concatentate("SELECT FirstName
FROM taDEFENDANTS WHERE PropertyID= " & [PropertyID]) AS Property_Owner
FROM taPROPERTY;

Using this SQL, I get a column with the PropertyID and a column for
Property_Owner which is blank and contains no data.

Here is how the tables are set up:

taPROPERTY (1 side)
PropertyID-Autonumber

taDEFENDANTS (Many side)
DefendantID-Autonumber
PropertyID –Long
LastName –Text(45)
FirstName –Text(35)

Just for further info:

I DO have the basConcatenate Module installed. I have it set up for DAO and
reference is set.

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
' Dim rs As New ADODB.Recordset
' rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim strConcat As String 'build return string
'====== uncomment next line for DAO ========
Set db = Nothing

Duane Hookom said:
Try create a parent query and a child query then use SQL like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' '
& Suffix FROM [ChildTableQuery] WHERE PropertyID = " & [PropertyID] ) as
Property_Owner
FROM [ParentTableQuery];

--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
Hello Duane:

I copied and pasted your code and still get the same syntax error. Below
are the datatype. Is there something else that you can see that I'm just
totally missing that is making this harder than it should be.

Again, thanks for your time.

Ray.

FirstName -Txt
MiddleName -Txt
LastName -Txt
Suffix -Txt
DefendantType-txt
PropertyID -Long
DefendantTypeID -Long

Duane Hookom said:
I would expect to see something like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' MiddleName & ' ' & LastName & ' '
Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE taDEFENDANTTYPE.DefendantType='Owner' AND PropertyID = " &
[PropertyID] ) as Property_Owner
FROM taDEFENDANTS;

This depends on your table and field names and data types.




--
Duane Hookom
Microsoft Access MVP


:

Duane:

I got your module and installed it this morning and have been playing with
it all day. I haven't had any success in the larger scale, i.e., dealing
with only 1 or 2 fields, I understand how it works, however, with the larger
sql statements, I am lost.

With the below statement, I get the following error:

Syntax Error (Missing Operator) In Query Expression.

Can you point me in the correct direction on how to fix this?

Thanks,

Ray.

SELECT propertyID, CONCATENTATE ("SELECT FirstName & ' ' MiddleName & ' '
LastName & ' ' Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE (((taDEFENDANTTYPE.DefendantType)="Owner"))" as Property_Owner
FROM taDEFENDANTS



:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

Disregard - - -

After the previous errors, I started doing a letter by letter examination of
the (sql)code. I found that I had misspelled the function name, as a result,
it wasn't running.

Thanks for all of the help.

Ray.

Ray Todd Jr said:
Duane:

OK. I keep getting all kind of errors (3075, 3061, etc, etc). As a result,
I am backing out to a *much* simpler query in order to work though the
problem, however, the resulting Property_Owner field is blank. Hopfully, I
am providing all of the info to help diagnose the problem.

Here is the SQL:

SELECT taPROPERTY.PropertyID, Concatentate("SELECT FirstName
FROM taDEFENDANTS WHERE PropertyID= " & [PropertyID]) AS Property_Owner
FROM taPROPERTY;

Using this SQL, I get a column with the PropertyID and a column for
Property_Owner which is blank and contains no data.

Here is how the tables are set up:

taPROPERTY (1 side)
PropertyID-Autonumber

taDEFENDANTS (Many side)
DefendantID-Autonumber
PropertyID –Long
LastName –Text(45)
FirstName –Text(35)

Just for further info:

I DO have the basConcatenate Module installed. I have it set up for DAO and
reference is set.

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
' Dim rs As New ADODB.Recordset
' rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim strConcat As String 'build return string
'====== uncomment next line for DAO ========
Set db = Nothing

Duane Hookom said:
Try create a parent query and a child query then use SQL like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' '
& Suffix FROM [ChildTableQuery] WHERE PropertyID = " & [PropertyID] ) as
Property_Owner
FROM [ParentTableQuery];

--
Duane Hookom
Microsoft Access MVP


Ray Todd Jr said:
Hello Duane:

I copied and pasted your code and still get the same syntax error. Below
are the datatype. Is there something else that you can see that I'm just
totally missing that is making this harder than it should be.

Again, thanks for your time.

Ray.

FirstName -Txt
MiddleName -Txt
LastName -Txt
Suffix -Txt
DefendantType-txt
PropertyID -Long
DefendantTypeID -Long

:

I would expect to see something like:
SELECT propertyID,
CONCATENTATE("SELECT FirstName & ' ' MiddleName & ' ' & LastName & ' '
Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE taDEFENDANTTYPE.DefendantType='Owner' AND PropertyID = " &
[PropertyID] ) as Property_Owner
FROM taDEFENDANTS;

This depends on your table and field names and data types.




--
Duane Hookom
Microsoft Access MVP


:

Duane:

I got your module and installed it this morning and have been playing with
it all day. I haven't had any success in the larger scale, i.e., dealing
with only 1 or 2 fields, I understand how it works, however, with the larger
sql statements, I am lost.

With the below statement, I get the following error:

Syntax Error (Missing Operator) In Query Expression.

Can you point me in the correct direction on how to fix this?

Thanks,

Ray.

SELECT propertyID, CONCATENTATE ("SELECT FirstName & ' ' MiddleName & ' '
LastName & ' ' Suffix
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID=taDEFENDANTS.DefendantTypeID
WHERE (((taDEFENDANTTYPE.DefendantType)="Owner"))" as Property_Owner
FROM taDEFENDANTS



:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP


:

I have the below SQL that obtains the names of property owners from the data
stored in tables. The SQL adds the firstname, middlename, lastname and the
suffix together into a field called Property_Owner. The question I have is
this, what if the property has two owners (most do)? How do I write the SQL
to add the second name to the property owners name? I use this SQL to create
the merge data for form letters in Word. Thanks, Ray.

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, taPROPERTY.PropertyAddress,
taPROPERTY.PropertyClass, taPROPERTY.ParcelNumber,
taPROPERTY.PropertyZipCode, taPROPERTY.CourtDataID,
taPROPERTY.TaxSaleStatusID, taCOURTDATA.TaxSaleNumber,
taCOURTDATA.CaseNumber, taCOURTDATA.CaseYear, taTAXSALESTATUS.TaxSaleStatus,
taDEFENDANTS.DefendantID, taDEFENDANTS.LastName, taDEFENDANTS.FirstName,
taDEFENDANTS.MiddleName, taDEFENDANTS.Suffix, taDEFENDANTTYPE.DefendantType,
taDEFENDANTS.Address1, taDEFENDANTS.Address2, taDEFENDANTS.City,
taDEFENDANTS.State, taDEFENDANTS.Zip, [FirstName] & " " & [MiddleName] & " "
& [LastName] & " " & [Suffix] AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN ((taTAXSALESTATUS INNER JOIN (taCOURTDATA
INNER JOIN taPROPERTY ON taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
taDEFENDANTS ON taPROPERTY.PropertyID = taDEFENDANTS.PropertyID) ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
WHERE (([DefendantType]="Owner"));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Query Difficulties 11
SQL Help 8

Top