Getting Multiple Owners Names for Single Pieces of Property

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray Todd Jr - 17 May 2008 20:08 GMT

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"));

Reply to this Message

colin_e - 18 May 2008 09:58 GMT

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.

Signature

Regards: Colin

> 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
[quoted text clipped - 19 lines]
> taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> WHERE (([DefendantType]="Owner"));

Reply to this Message

Duane Hookom - 19 May 2008 02:11 GMT

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

Signature

Duane Hookom
Microsoft Access MVP

Reply to this Message

Ray Todd Jr - 19 May 2008 21:17 GMT

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.
[quoted text clipped - 22 lines]
> > taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> > WHERE (([DefendantType]="Owner"));

Reply to this Message

Duane Hookom - 19 May 2008 21:47 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> Duane:
>
[quoted text clipped - 46 lines]
> > > taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> > > WHERE (([DefendantType]="Owner"));

Reply to this Message

Ray Todd Jr - 20 May 2008 01:46 GMT

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,
[quoted text clipped - 58 lines]
> > > > taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> > > > WHERE (([DefendantType]="Owner"));

Reply to this Message

Duane Hookom - 20 May 2008 03:40 GMT

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];

Signature

Duane Hookom
Microsoft Access MVP

> Hello Duane:
>
[quoted text clipped - 76 lines]
> > > > > taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> > > > > WHERE (([DefendantType]="Owner"));

Reply to this Message

Ray Todd Jr - 20 May 2008 17:25 GMT

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

> Try create a parent query and a child query then use SQL like:
> SELECT propertyID,
[quoted text clipped - 83 lines]
> > > > > > taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> > > > > > WHERE (([DefendantType]="Owner"));

Reply to this Message

Ray Todd Jr - 20 May 2008 18:21 GMT

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.

> Duane:
>
[quoted text clipped - 132 lines]
> > > > > > > taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID
> > > > > > > WHERE (([DefendantType]="Owner"));

Reply to this Message