Report Query Difficulties

[RayToddJr]

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.DefendantsnameID 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.

 

[vanderghast]

DISTINCT return the distinct results ---as a whole---, I mean, if you select
3 fields,if you have 3 fields/expressions in the SELECT clause, then the 3
fields/expressions must all three be considered, and if any one of them
differ, that makes it a DISTINCT result.



If you want only one record per PropertyID, try:



------------------------
SELECT taPROPERTY.PropertyID,
LAST(taPROPERTY.CLT),
LAST(Trim(CONCATENATE("SELECT
tadefendantnames.FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix)

FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID 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))

GROUP BY taPROPERTY.PropertyID
-------------------------------------


If you want only one record per PropertyID, per CLT, remove the LAST in
front of CLT and add it to the GROUP BY clause.




Vanderghast, Access MVP

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.DefendantsnameID 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.

 

[Duane Hookom]

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.

 

[RayToddJr]

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.

 

[RayToddJr]

Hello Vanderghast and thanks for your assistance:

I copied and pasted your suggestion and received the following error:

You tried to execute a query that does not include the specified expression
"OwnerAddress1" as part of an aggregate function.

Thanks.

Ray.

DISTINCT return the distinct results ---as a whole---, I mean, if you select
3 fields,if you have 3 fields/expressions in the SELECT clause, then the 3
fields/expressions must all three be considered, and if any one of them
differ, that makes it a DISTINCT result.



If you want only one record per PropertyID, try:



------------------------
SELECT taPROPERTY.PropertyID,
LAST(taPROPERTY.CLT),
LAST(Trim(CONCATENATE("SELECT
tadefendantnames.FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix)

FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID 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))

GROUP BY taPROPERTY.PropertyID
-------------------------------------


If you want only one record per PropertyID, per CLT, remove the LAST in
front of CLT and add it to the GROUP BY clause.




Vanderghast, Access MVP

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.DefendantsnameID 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.

 

[John W. Vinson]

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.

 

[Duane Hookom]

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

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.

 

[vanderghast]

My bad, I assumed incorrectly that the first occurence of the keyword FROM
was starting the main FROM clause, but, in your case, seems it is part of
the user defined function CONCATENATE. So the following should work (unless
there is a typo in the table/field names):

------------------------
SELECT taPROPERTY.PropertyID,
LAST(taPROPERTY.CLT),
LAST(Trim(CONCATENATE("SELECT
tadefendantnames.FirstName & ' ' & MiddleName & ' ' & LastName & ' '
&
Suffix) FROM taDEFENDANTNAMES INNER JOIN taDefendants ON
taDEFENDANTNAMES.DefendantsnameID=taDEFENDANTS.DefendantsnameID
WHERE
tadefendants.PropertyID=" & [taPROPERTY].[PropertyID] & " AND
tadefendants.DefendantTypeID=20")) AS Property_Owner,
LAST( taDEFENDANTNAMES.Address1) AS OwnerAddress1,
LAST( taDEFENDANTNAMES.Address2 ) AS OwnerAddress2,
LAST( taDENDANTNAMES.City) AS OwnerCity,
LAST( taDEFENDANTNAMES.State) AS OwnerState,
LAST( 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))

GROUP BY taPROPERTY.PropertyID
-------------------------------------




Vanderghast, Access MVP

 

[RayToddJr]

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.

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]

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.

 

[RayToddJr]

Vanderghast:

That did it. Thanks for your help.......

Ray.

 

[RayToddJr]

Duane:

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

Ray.