Thanks lbass! I am still working on this one and am getting really close to what I want.... I need help with one other thing though.... Here is the Command I have created so far:
SELECT DISTINCT
'Parcel' "Type",
"LND_ACQUISITION_DISPOSAL"."ACQDISTYPE", "LND_ACQUISITION_DISPOSAL"."ACQDISDATE", "LND_ACQUISITION_DISPOSAL"."ACQ_PURP", "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET", "LND_ACQUISITION_DISPOSAL"."ACQDISP_ID",
"LND_HRM_PARCEL"."ASSET_TYPE",
"LND_ACQUISITION_DISPOSAL"."TRANS_TYPE",
"LND_CIVIC_ADDRESS"."CIV_ID",
"LND_CIVIC_ADDRESS"."STR_NAME",
"LND_CIVIC_ADDRESS"."GSA_NAME",
"LND_PR_REALESTATE_COMMENTS"."RE_LOCATE",
"LND_HRM_PARCEL"."PID",
"LND_ACQUISITION_DISPOSAL"."ACQDISCOST",
"LND_PR_CLIENT"."CLIENTCODE",
"LND_PR_CLIENT"."CLIENTNAME",
"LND_HRM_PARCEL"."MAIN_CLASS",
"LND_HRM_PARCEL"."HECTARES",
"PID_DIST"."DIST_ID",
"LND_HRM_PARCEL"."ASSET_ID",
"LND_CIVIC_ADDRESS"."CIV_NUM",
"LND_HRM_PARCEL"."MOD_TYPE",
"LND_ASSET_XREF"."MODASSETID",
"LND_ASSET_XREF"."HRMASSETID"
FROM ((((("SDEADM"."LND_ACQUISITION_DISPOSAL" "LND_ACQUISITION_DISPOSAL" LEFT OUTER JOIN "SDEADM"."LND_HRM_PARCEL" "LND_HRM_PARCEL" ON "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET"="LND_HRM_PARCEL"."ASSET_ID")
LEFT OUTER JOIN "SDEADM"."LND_CIVIC_ADDRESS" "LND_CIVIC_ADDRESS" ON "LND_HRM_PARCEL"."PID"="LND_CIVIC_ADDRESS"."PID")
LEFT OUTER JOIN "SDEADM"."LND_ASSET_XREF" "LND_ASSET_XREF" ON "LND_HRM_PARCEL"."ASSET_ID"="LND_ASSET_XREF"."HRMASSETID")
LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON ("LND_HRM_PARCEL"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID") AND ("LND_HRM_PARCEL"."PID"="LND_PR_CLIENT"."PID"))
LEFT OUTER JOIN "SDEADM"."LND_PR_REALESTATE_COMMENTS" "LND_PR_REALESTATE_COMMENTS" ON ("LND_HRM_PARCEL"."ASSET_ID"="LND_PR_REALESTATE_COMMENTS"."ASSET_ID") AND ("LND_HRM_PARCEL"."PID"="LND_PR_REALESTATE_COMMENTS"."PID"))
LEFT OUTER JOIN "SDEADM"."PID_DIST" "PID_DIST" ON "LND_HRM_PARCEL"."PID"="PID_DIST"."PID"
WHERE (("LND_ACQUISITION_DISPOSAL"."TRANS_TYPE" = 1)
AND NOT ("LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR "LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION'))
UNION
SELECT DISTINCT
'Parcel_Modified' "Type",
"LND_ACQUISITION_DISPOSAL"."ACQDISTYPE", "LND_ACQUISITION_DISPOSAL"."ACQDISDATE", "LND_ACQUISITION_DISPOSAL"."ACQ_PURP", "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET", "LND_ACQUISITION_DISPOSAL"."ACQDISP_ID",
"LND_HRM_PARCEL_MODIFIED"."ASSET_TYPE",
"LND_ACQUISITION_DISPOSAL"."TRANS_TYPE",
"LND_CIVIC_ADDRESS"."CIV_ID",
"LND_CIVIC_ADDRESS"."STR_NAME",
"LND_CIVIC_ADDRESS"."GSA_NAME",
"LND_PR_REALESTATE_COMMENTS"."RE_LOCATE",
"LND_HRM_PARCEL_MODIFIED"."PID",
"LND_ACQUISITION_DISPOSAL"."ACQDISCOST",
"LND_PR_CLIENT"."CLIENTCODE",
"LND_PR_CLIENT"."CLIENTNAME",
"LND_HRM_PARCEL_MODIFIED"."MAIN_CLASS",
"LND_HRM_PARCEL_MODIFIED"."HECTARES",
"PID_DIST"."DIST_ID",
"LND_HRM_PARCEL_MODIFIED"."ASSET_ID",
"LND_CIVIC_ADDRESS"."CIV_NUM",
"LND_HRM_PARCEL_MODIFIED"."MODIFY_TYPE",
"LND_ASSET_XREF"."MODASSETID",
"LND_ASSET_XREF"."HRMASSETID"
FROM ((((("SDEADM"."LND_ACQUISITION_DISPOSAL" "LND_ACQUISITION_DISPOSAL" LEFT OUTER JOIN "SDEADM"."LND_HRM_PARCEL_MODIFIED" "LND_HRM_PARCEL_MODIFIED" ON "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET"="LND_HRM_PARCEL_MODIFIED"."ASSET_ID")
LEFT OUTER JOIN "SDEADM"."LND_CIVIC_ADDRESS" "LND_CIVIC_ADDRESS" ON "LND_HRM_PARCEL_MODIFIED"."PID"="LND_CIVIC_ADDRESS"."PID")
LEFT OUTER JOIN "SDEADM"."LND_ASSET_XREF" "LND_ASSET_XREF" ON "LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_ASSET_XREF"."MODASSETID")
LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON ("LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID") AND ("LND_HRM_PARCEL_MODIFIED"."PID"="LND_PR_CLIENT"."PID"))
LEFT OUTER JOIN "SDEADM"."LND_PR_REALESTATE_COMMENTS" "LND_PR_REALESTATE_COMMENTS" ON ("LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_PR_REALESTATE_COMMENTS"."ASSET_ID") AND ("LND_HRM_PARCEL_MODIFIED"."PID"="LND_PR_REALESTATE_COMMENTS"."PID"))
LEFT OUTER JOIN "SDEADM"."PID_DIST" "PID_DIST" ON "LND_HRM_PARCEL_MODIFIED"."PID"="PID_DIST"."PID"
WHERE (("LND_ACQUISITION_DISPOSAL"."TRANS_TYPE" = 1)
AND NOT ("LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR "LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION'))
In the report, I have created two sections for details. Section A is to display the records from the Type = parcel and only those from the parcel items that have the Asset_ID equal to the Orig_Asset…. So, in the section expert, I have the supress set up to suppress Section A for the following:
{Command.Type} = "Parcel_Modified"
or {Command.ASSET_ID} <> {Command.ORIG_ASSET}
This works… it displays what I want……..in a way… I will explain below….
Section B of the details is the opposite… I want the parcel_modified records only, and only where the Asset_ID is equal to the Orig_Asset. So, again, I set up the following supression for Section B:
{Command.Type} = "Parcel"
or {Command.ASSET_ID} <> {Command.ORIG_ASSET}
This also works…. In a way….
The only problem with the two sections, is that because there can be more than one address for each Asset_ID, I am getting multiple records…… for example, in Section B, I have an Asset_ID 14652 showing up 5 times because there are 5 different Civ_IDs related to it… so that would so up as 5 different unique records in the union. What I tried to do, was create a MAXCIV formula like this:
if not isnull ({Command.CIV_ID}) then
Cstr (maximum ({Command.CIV_NUM}, {Command.ORIG_ASSET}), 0 , "")
else "no location entered - click globe for map"
thinking that would solve my problem by displaying just the maximum civic address for the Asset_ID. But it just displays the 5 records, all with the maximun civic……… so, I obviously haven’t adjusted the “display” of information with this formula….
Any ideas on how to just DISPLAY the maximum record??
Thanks!!