Help with query structure? (1 Viewer)

[ttomw]

I am trying to structure a select query in Access 2010 that would allow me to create a form/list of owners and parcels that can be filtered on multiple fields.

Tables:
OWNERS
OwnerID (key)
OwnerName
OwnerAddress
Category (lookup with 7 options)

PARCELS
TLID (key)
County
Acres
PropClass
EastWest (2 options E or W)
OwnerID

Owners can own multiple parcels.

I need to be able to filter the list of parcels grouped by owner on one form by: Owner category,Parcel County, Parcel Propclass, Parcel EastWest,Parcel Acres, Owner Total Acres (Dyanamic Sum of Grouped filtered results)

The biggest challenge for me here is that the Total acres must change with each filter choice rather than be a static field in a separate linked query.
Is this doable in query structuring or does it require a big hairy VBA object?
Thanks for any input!
Tom

 

[Simon_MT]

Easy do it the bottom up rather than top down.

Try starting with Parcels linked to Owners. Then put the Parcels information in the Details and the Owners in the Header and Total the Acres.

Simon

 

[Pat Hartman]

The biggest challenge for me here is that the Total acres must change with each filter choice rather than be a static field in a separate linked query.

What? Why is acres different from county, EW, or anything else you are filtering on?

Create a form with the textboxes and combos where you will enter criteria. The query will reference thes fields. You should also add a clear button to clear out all the current values.

Select ...
From ...
Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null) AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null) AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fld3 Is Null) AND ....

Notice the pattern - a table field is compared to a form field OR the form field is null. That allows the arguments to be optional. The AND connects multiple selections. Each separate field condition MUST be enclosed in parentheses so that the AND and OR operators can be used in the same expression and be evaluated correctly.

 

[ttomw]

Simon_MT,
I tried your suggestion, but the list only shows one record at a time...yes I am new to using forms like this so I am probably missing something simple and obvious. I am looking for something that looks more like a table or query output list showing the whole resulting dataset after filters are selected.

Pat,

"What? Why is acres different from county, EW, or anything else you are filtering on?"
​

Owners can own multiple parcels in differrent counties, or across the EW division of the state so if I filter by county and that owner owns parcels in excluded counties it will change the total Acres figure for that owner.

Thanks guys for your input!

 

[ttomw]

How do I get the datasheet view of this form to display these records grouped by Owner (OwnerID)? If I add a Group by function to the underlying parcel query I am forced to add an aggregate function to all the other fields I want included. How do you get around that?

 

[ttomw]

More details Help?

Ok, I'm going to try this again and try to be more clear on intent. I'm still hitting my head against the wall on this form.

I have the two tables (Owners and Parcels), of data detailed above, linked in a one to many relationship. Owners can own multiple parcels, in multiple counties etc.

I have the following "Parcel Query", which combines the two tables:

SELECT Owners.OwnerID, Owners.Owner, Owners.Category, Parcels.County, Parcels.Acres, Parcels.PropClass, Parcels.EastWest
FROM Owners INNER JOIN Parcels ON Owners.OwnerID = Parcels.OwnerID
ORDER BY Owners.Owner;

I am trying to create a user interface form that features a datasheet view of the Owners fields plus calculated fields, Total Acres Per Owner and Total Parcels Per Owner. In the header bar I need to show filter controls that filter the underlying query data by the parcel fields: EastWest, County, and PropClass.

My questions/struggles:
1. Is it possible, with this Parcel Query, and if so how, do I get the datasheet part of the form to dislplay the records grouped by Owner?

2. Which control and properties should I use in the header part of the form to filter the Owner records that show up in the datasheet? How do you set up a combo box to filter fields in the query that are not shown on datasheet? Keeping in mind that these filters are all on Parcel fields which do not show up in the datasheet of filtered results. Also, these filters will dynamically change the Total Acres and Parcel counts calculated fields that do show up in the datasheet.
Thanks Again!
Tom

There is probably a more elegant way to go about this but it is eluding me.

 

[Pat Hartman]

How do I get the datasheet view of this form to display these records grouped by Owner (OwnerID)? If I add a Group by function to the underlying parcel query I am forced to add an aggregate function to all the other fields I want included. How do you get around that?

This is a "presentation" issue and can be handled quite well in a report. Forms are primarially for data entry and update and so you can't aggregate data if you ever expect to update it. The form will also not allow you to supress "duplicate" values as a report will.

Owners can own multiple parcels in differrent counties, or across the EW division of the state so if I filter by county and that owner owns parcels in excluded counties it will change the total Acres figure for that owner.

So exclude them from the query.

1. In a totals query, every column must be aggregated either by grouping, summing, averaging, etc. This query will not be updateable.
2. Add combos or textboxes to the form's header. Reference those in the code I posted earlier
The form does not need to show all the columns in the RecordSource but every column you want to filter on must be included in the RecordSource even if you don't want to show it. In my not so humble opinion, it confuses users to filter on fields they can't see so I never do it.
If you have sums and counts in the form's footer, they will include only rows selected by the RecordSource query.

 

[ttomw]

This is a "presentation" issue and can be handled quite well in a report. Forms are primarially for data entry and update and so you can't aggregate data if you ever expect to update it. The form will also not allow you to supress "duplicate" values as a report will.

So exclude them from the query.

Ok. So it sounds like my original vision of having a form that performs all these filters on the fly is not possible because of the aggregate issues. So kicking out a formatted report works, since that is the ultimate objective for the users. But I still have to provide a user interface form that allows them to filter by EastWest, County, PropClass, Category, and perhaps most important and problematic, Total Acres owned per Owner. After they select the filter inputs they like, they can hit a button and generate the report. So where and how would you suggest I deal with that Calculation?
Thanks Pat!

 

[Simon_MT]

Hi Tom,

This is the one area where Froms could be a little more proficient!

I reckon there maybe a way to do this with a Form and possibly a SubQuery. Pat is quite right a report allows Grouping but you could revert to a subform without a Parent Child relationship and use the criteria to match the records.

Can you upload your database so I can have a try?

Simon

 

[ttomw]

Hi Tom,

This is the one area where Froms could be a little more proficient!

I reckon there maybe a way to do this with a Form and possibly a SubQuery. Pat is quite right a report allows Grouping but you could revert to a subform without a Parent Child relationship and use the criteria to match the records.

Can you upload your database so I can have a try?

Simon

Simon,
That would be great. I would welcome any insight. I've attached an exerpt of the DB.
Thanks!
Tom

 

[Simon_MT]

With a Continuous Form you can't use a SubForm in the Detail but you can in the Header.

I have not filtered anything but these gives you an idea of how to achieve your objective using a subform. You possibly could the same thing with a Dlookup but I don't like using this technique unless I have to.

Try Frm_Owners.

Simon

 

[Pat Hartman]

As long as the form is used for filtering rather than updating, it is OK to aggregate data. I would create a query that summarizes all acres, excluded acres, and all acres - excluded acres. Then join this query to the main table. This gives you 4 acres fields to filter on.

Select ..., Sum(IIf(Type = Excluded, 0, Acres)) As NotExcluded, Sum(IIf(Type= Excluded, Acres, 0)) As Excluded, Sum(Acres) As AllAcres
From ...
Group by ...;

 

[ttomw]

Simon,
Thank you for this sample demonstration of these types of multi part forms. It definitely opens my eyes to possibilities.

Unfortunately none of these really approaches what I am shooting for yet, which is a filtering form that simply allows users to experiment with various combinations of the filters I mentioned: EastWest, County, PropClass, Category, And total acres owned per owner, and then export reports for mailings when they find the dataset they are interested in.

Pat,
You are correct that this need not be updatable. It is only to facilitate data exploration and selection for mailings. For example show me all the owners of properties in counties 1 and 2 with total acreage owned >15 acres, and of property classes 6*. Note the big bugaboo for me is that aggregated acreage which will change(Not the underlying parcel table data, just the number of parcels that are included in the aggregate) if you change the other filters. For example there are many owners which own properties in 5 or more counties. If I set the county filter to only 2 of those counties then the total acreage filter needs to reflect just those properties owned in those two counties.

I have to admit I am not following how excluded acres comes into this. I don't have any designated excluded acres in the database.

Thanks again for both of your perserverance with me on this!
Tom

 

[Simon_MT]

Because the sample was a stripped down version I didn't go that far. I will look at it again.

Simon

 

[Pat Hartman]

Owners can own multiple parcels in differrent counties, or across the EW division of the state so if I filter by county and that owner owns parcels in excluded counties it will change the total Acres figure for that owner.

They are acres in excluded counties.

 

[ttomw]

They are acres in excluded counties.

Pat,
Are you suggesting there is a way to create an "excluded counties" query that dynamically changes based on the county filter selected by a user?
Thanks,Tom

 

[Slides]

Getting the structure down can be extremely hard for me.

 

[Pat Hartman]

and that owner owns parcels in excluded counties

It sounded to me like you already had this attribute defined.

You can create your own "include/exclude" filters. "Include" is the norm but there is no reason you can't do an "exclude" option. If you just need a couple at a time, use a multi-select listbox. Have an option on the form that specifies include or exclude and run the appropriate query. If there will be more tha a few choices, you could use a temp table to hold the seletions and use those tables to restrict the queries.