Thank you for the UNION ALL and CAST hints. I need to do some reading on those and watch my comma and semicolon syntax. The working query needed a very slight change to run, as follows in bold underline... SELECT [file_as_name] AS OwnerName, [PROP_ID], RoundDecs(SUM([Acreage]), 2) AS Acres, RoundDecs(SUM([Acreage] * [Dry-Weight Production Lbs per Acre]), 0) AS DryPounds, RoundDecs(SUM([AU]), 2) AS AnimalUnits FROM CALL Selection([Soils Table], TRUE) GROUP BY [file_as_name], [PROP_ID] UNION ALL SELECT [file_as_name], -- OwnerName CAST(NULL AS INT32), -- PROP_ID (NB match data type) RoundDecs(SUM([Acreage]), 2), -- Acres RoundDecs(SUM([Acreage] * [Dry-Weight Production Lbs per Acre]), 0), -- DryPounds RoundDecs(SUM([AU]), 2) -- AnimalUnits FROM CALL Selection([Soils Table], TRUE) GROUP BY [file_as_name] ;
So far the boss really likes this. THEN, she threw in something unexpected. Sometimes an owner will have a ranch under several names - meaning, say, three ranch names for adjacent property which they operate as one ranch. Silver Spring Ranch, Bob and Mary Jones Living Trust Ranch, Moon Raker Ranch, for example. We're supposed to know all three have the same owner. It turns out most of the time the mailing address for the ranches are the same. With that in mind I made a couple changes to the above query... SELECT [file_as_name] AS OwnerName, [PROP_ID], RoundDecs(SUM([Acreage]), 2) AS Acres, RoundDecs(SUM([Acreage] * [Dry-Weight Production Lbs per Acre]), 0) AS DryPounds, RoundDecs(SUM([AU]), 2) AS AnimalUnits , [addr_line2] AS Address FROM [Soils Table] WHERE [addr_line2] = '1027 AUSTIN HWY SUITE 200' --FROM CALL Selection([Soils Table], TRUE) GROUP BY [file_as_name], [PROP_ID], [addr_line2] UNION ALL SELECT [file_as_name], -- OwnerName CAST(NULL AS INT32), -- PROP_ID (NB match data type) RoundDecs(SUM([Acreage]), 2), -- Acres RoundDecs(SUM([Acreage] * [Dry-Weight Production Lbs per Acre]), 0), -- DryPounds RoundDecs(SUM([AU]), 2), -- AnimalUnits [addr_line2] --AS Address FROM [Soils Table] WHERE [addr_line2] = '1027 AUSTIN HWY SUITE 200' ----FROM CALL Selection([Soils Table], TRUE) GROUP BY [file_as_name], [addr_line2] ;
This selects all the properties with an address of 1027 AUSTIN HWY SUITE 200. The problem is it's a query and subject to the introduction of typos. I would rather keep the users out of the query especially when a change needs to happen twice. Is there query wording in Manifold to bring up a user input dialog box? I didn't see it in the Fine Manual, but I might not be searching for the correct lingo. Out in the wild I saw the use of the @ and & symbol prefixes to call for input. I tried using Select in the Contents Pane and got unexpected results. I searched for Text Starts with, selected the addr_line2 field, entered 1027 AUSTIN... and got what appeared to be 5 results in the table. The map; however, showed all the results. The table apparently shows only those selected records from the original view limited to 50,000 records. Is that a correct understanding?
|