Report Query Difficulties

I have a query for a report. This this query, I have a sub-query. I think I
have pinpointed my overall problem to the sub-query.

The SQL for the sub-query is listed below.

The Problem:

The purpose of the sub-query is to get the concatenated name of the property
owners and the first address. It should not contain multiple rows.

I have attempted adding the DISTINCT and the DISTINCTROW clauses to no
avail, if there is more than one owner, I still get a row for each owner with
and the associated address for that owner.

SQL for the sub-query:

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, Trim(CONCATENATE("SELECT
tadefendantnames.FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.Def endantsnameID WHERE
tadefendants.PropertyID=" & [taPROPERTY].[PropertyID] & " AND
tadefendants.DefendantTypeID=20")) AS Property_Owner,
taDEFENDANTNAMES.Address1 AS OwnerAddress1, taDEFENDANTNAMES.Address2 AS
OwnerAddress2, taDEFENDANTNAMES.City AS OwnerCity, taDEFENDANTNAMES.State AS
OwnerState, taDEFENDANTNAMES.Zip AS OwnerZip
FROM taPROPERTY INNER JOIN (taDEFENDANTNAMES INNER JOIN taDEFENDANTS ON
taDEFENDANTNAMES.DefendantsNameID = taDEFENDANTS.DefendantsNameID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID
WHERE (((taDEFENDANTS.DefendantTypeID)=20));


In case it is needed, here is SQL for the main query:

SELECT DISTINCT taPROPERTY.PropertyID, taPROPERTY.CLT,
taTAXSALESTATUS.TaxSaleStatus, taCOURTDATA.TaxSaleNumber,
taPROPERTY.ParcelNumber, taPROPERTY.PropertyAddress,
taDEEDDESCRIPTION.DeedDescription, taCOURTDATA.CaseYear, [CaseYear]-1 AS
CaseYearOlder, [qryExhibitA4-Owner].Property_Owner,
[qryExhibitA4-Owner].OwnerAddress1, [qryExhibitA4-Owner].OwnerAddress2,
[qryExhibitA4-Owner].OwnerCity, [qryExhibitA4-Owner].OwnerState,
[qryExhibitA4-Owner].OwnerZip
FROM (taTAXSALESTATUS INNER JOIN ((taCOURTDATA INNER JOIN taPROPERTY ON
taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) LEFT JOIN taDEEDDESCRIPTION
ON taPROPERTY.PropertyID = taDEEDDESCRIPTION.PropertyID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
[qryExhibitA4-Owner] ON taPROPERTY.PropertyID =
[qryExhibitA4-Owner].PropertyID;

Thanks for any help and suggestions that can be made.

Ray.

If you don't want a record for every defendant, you need to remove a level of
detail records from the main query or apply a GROUP BY on the main query.

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

I have a query for a report. This this query, I have a sub-query. I think I
have pinpointed my overall problem to the sub-query.

The SQL for the sub-query is listed below.

The Problem:

The purpose of the sub-query is to get the concatenated name of the property
owners and the first address. It should not contain multiple rows.

I have attempted adding the DISTINCT and the DISTINCTROW clauses to no
avail, if there is more than one owner, I still get a row for each owner with
and the associated address for that owner.

SQL for the sub-query:

SELECT taPROPERTY.PropertyID, taPROPERTY.CLT, Trim(CONCATENATE("SELECT
tadefendantnames.FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.Def endantsnameID WHERE
tadefendants.PropertyID=" & [taPROPERTY].[PropertyID] & " AND
tadefendants.DefendantTypeID=20")) AS Property_Owner,
taDEFENDANTNAMES.Address1 AS OwnerAddress1, taDEFENDANTNAMES.Address2 AS
OwnerAddress2, taDEFENDANTNAMES.City AS OwnerCity, taDEFENDANTNAMES.State AS
OwnerState, taDEFENDANTNAMES.Zip AS OwnerZip
FROM taPROPERTY INNER JOIN (taDEFENDANTNAMES INNER JOIN taDEFENDANTS ON
taDEFENDANTNAMES.DefendantsNameID = taDEFENDANTS.DefendantsNameID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID
WHERE (((taDEFENDANTS.DefendantTypeID)=20));


In case it is needed, here is SQL for the main query:

SELECT DISTINCT taPROPERTY.PropertyID, taPROPERTY.CLT,
taTAXSALESTATUS.TaxSaleStatus, taCOURTDATA.TaxSaleNumber,
taPROPERTY.ParcelNumber, taPROPERTY.PropertyAddress,
taDEEDDESCRIPTION.DeedDescription, taCOURTDATA.CaseYear, [CaseYear]-1 AS
CaseYearOlder, [qryExhibitA4-Owner].Property_Owner,
[qryExhibitA4-Owner].OwnerAddress1, [qryExhibitA4-Owner].OwnerAddress2,
[qryExhibitA4-Owner].OwnerCity, [qryExhibitA4-Owner].OwnerState,
[qryExhibitA4-Owner].OwnerZip
FROM (taTAXSALESTATUS INNER JOIN ((taCOURTDATA INNER JOIN taPROPERTY ON
taCOURTDATA.CourtDataID = taPROPERTY.CourtDataID) LEFT JOIN taDEEDDESCRIPTION
ON taPROPERTY.PropertyID = taDEEDDESCRIPTION.PropertyID) ON
taTAXSALESTATUS.TaxSaleStatusID = taPROPERTY.TaxSaleStatusID) INNER JOIN
[qryExhibitA4-Owner] ON taPROPERTY.PropertyID =
[qryExhibitA4-Owner].PropertyID;

Thanks for any help and suggestions that can be made.

Ray.

Hello Duane and thanks for your assistance:

The report that is being created is a faily straight forward report.

1. It list the propertyID, CLT, The Concatenated Property Owner Name and the
first address that is listed for an owner (if there is more than one owner).

2. Further down in the report, a list of all of the liens that were found
in on the property (which can be numerous).

What I have found is that when a property has more than one owner, the
report will list the liens the same number of times of owners, i.e., if we
have 1 lien and 5 owners, this same lien will be listed 5 times in the detail
section of the report.

In my trying to find out the cause of this, I think that I have traced it to
this sub-query. However, I'm no expert and could be completely wrong and
looking in the wrong direction.

Any further guidance would be appreciated.

Thanks,

Ray.

"Duane Hookom" wrote:

If you don't want a record for every defendant, you need to remove a level of
detail records from the main query or apply a GROUP BY on the main query.

--
Duane Hookom
Microsoft Access MVP

On Tue, 22 Sep 2009 12:35:02 -0700, RayToddJr
wrote:

Hello Duane and thanks for your assistance:

The report that is being created is a faily straight forward report.

1. It list the propertyID, CLT, The Concatenated Property Owner Name and the
first address that is listed for an owner (if there is more than one owner).

2. Further down in the report, a list of all of the liens that were found
in on the property (which can be numerous).

What I have found is that when a property has more than one owner, the
report will list the liens the same number of times of owners, i.e., if we
have 1 lien and 5 owners, this same lien will be listed 5 times in the detail
section of the report.

In my trying to find out the cause of this, I think that I have traced it to
this sub-query. However, I'm no expert and could be completely wrong and
looking in the wrong direction.

Any further guidance would be appreciated.

Since you're trying to display data at two levels of aggregation in the
Report, I'd suggest using a Query which retrieves the "finest" level - leins;
and using the Report's Sorting and Grouping feature to get the breakdown.
Group By the PropertyID, and put the property specific information in a Group
Header (and/or Footer); put the lein-specific information in the Detail
section of the report.
--

John W. Vinson [MVP]

Does the address information depend on the Property or owner? If multiple
owners have multiple addresses, how would you want this to display?

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

Hello Duane and thanks for your assistance:

The report that is being created is a faily straight forward report.

1. It list the propertyID, CLT, The Concatenated Property Owner Name and the
first address that is listed for an owner (if there is more than one owner).

2. Further down in the report, a list of all of the liens that were found
in on the property (which can be numerous).

What I have found is that when a property has more than one owner, the
report will list the liens the same number of times of owners, i.e., if we
have 1 lien and 5 owners, this same lien will be listed 5 times in the detail
section of the report.

In my trying to find out the cause of this, I think that I have traced it to
this sub-query. However, I'm no expert and could be completely wrong and
looking in the wrong direction.

Any further guidance would be appreciated.

Thanks,

Ray.

"Duane Hookom" wrote:

If you don't want a record for every defendant, you need to remove a level of
detail records from the main query or apply a GROUP BY on the main query.

--
Duane Hookom
Microsoft Access MVP

the address information is dependant on the owner.

If there are multiple owners, we want to use the address of the first owner
entered into the database.

"Duane Hookom" wrote:

Does the address information depend on the Property or owner? If multiple
owners have multiple addresses, how would you want this to display?

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

Hello Duane and thanks for your assistance:

The report that is being created is a faily straight forward report.

1. It list the propertyID, CLT, The Concatenated Property Owner Name and the
first address that is listed for an owner (if there is more than one owner).

2. Further down in the report, a list of all of the liens that were found
in on the property (which can be numerous).

What I have found is that when a property has more than one owner, the
report will list the liens the same number of times of owners, i.e., if we
have 1 lien and 5 owners, this same lien will be listed 5 times in the detail
section of the report.

In my trying to find out the cause of this, I think that I have traced it to
this sub-query. However, I'm no expert and could be completely wrong and
looking in the wrong direction.

Any further guidance would be appreciated.

Thanks,

Ray.

"Duane Hookom" wrote:

If you don't want a record for every defendant, you need to remove a level of
detail records from the main query or apply a GROUP BY on the main query.

--
Duane Hookom
Microsoft Access MVP

Do you have a method for identifying the "address of the first owner entered
into the database"? If you do, you haven't told us.

Please try to pull together all of your specifications so someone can help.

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

the address information is dependant on the owner.

If there are multiple owners, we want to use the address of the first owner
entered into the database.

"Duane Hookom" wrote:

Does the address information depend on the Property or owner? If multiple
owners have multiple addresses, how would you want this to display?

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

Hello Duane and thanks for your assistance:

The report that is being created is a faily straight forward report.

1. It list the propertyID, CLT, The Concatenated Property Owner Name and the
first address that is listed for an owner (if there is more than one owner).

2. Further down in the report, a list of all of the liens that were found
in on the property (which can be numerous).

What I have found is that when a property has more than one owner, the
report will list the liens the same number of times of owners, i.e., if we
have 1 lien and 5 owners, this same lien will be listed 5 times in the detail
section of the report.

In my trying to find out the cause of this, I think that I have traced it to
this sub-query. However, I'm no expert and could be completely wrong and
looking in the wrong direction.

Any further guidance would be appreciated.

Thanks,

Ray.

"Duane Hookom" wrote:

If you don't want a record for every defendant, you need to remove a level of
detail records from the main query or apply a GROUP BY on the main query.

--
Duane Hookom
Microsoft Access MVP

Duane:

I will do a better job next time of getting the specifications written down
before posting. Thanks for your help.

Ray.

"Duane Hookom" wrote:

Do you have a method for identifying the "address of the first owner entered
into the database"? If you do, you haven't told us.

Please try to pull together all of your specifications so someone can help.

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

the address information is dependant on the owner.

If there are multiple owners, we want to use the address of the first owner
entered into the database.

"Duane Hookom" wrote:

Does the address information depend on the Property or owner? If multiple
owners have multiple addresses, how would you want this to display?

--
Duane Hookom
Microsoft Access MVP


"RayToddJr" wrote:

Hello Duane and thanks for your assistance:

The report that is being created is a faily straight forward report.

1. It list the propertyID, CLT, The Concatenated Property Owner Name and the
first address that is listed for an owner (if there is more than one owner).

2. Further down in the report, a list of all of the liens that were found
in on the property (which can be numerous).

What I have found is that when a property has more than one owner, the
report will list the liens the same number of times of owners, i.e., if we
have 1 lien and 5 owners, this same lien will be listed 5 times in the detail
section of the report.

In my trying to find out the cause of this, I think that I have traced it to
this sub-query. However, I'm no expert and could be completely wrong and
looking in the wrong direction.

Any further guidance would be appreciated.

Thanks,

Ray.

"Duane Hookom" wrote:

If you don't want a record for every defendant, you need to remove a level of
detail records from the main query or apply a GROUP BY on the main query.

--
Duane Hookom
Microsoft Access MVP