Mar 8 2012, 01:44 PM
This should be an easy query, but I've probably killed too many brain cells. (can't even think up a decent topic title).
Have AcctNumber, OwnerName, Legaldesc, Acres (number), Group (number)
Group can be a number from 0 to 8
I need to select the above fields where Group = 1 and if an owner has land in Group 1 then select all land in the other groups for that owner also. But do not select any for any owners that might have land in groups other than 1 if they do not also have land in group 1. I thought would be simple, but I have no idea how to do this.
Mar 8 2012, 01:53 PM
Please show us what you have tried.
Also, GROUP is a special term in SQL, and may be a contributing factor to your Query issue
Mar 8 2012, 02:41 PM
I haven't tried anything yet, because I don't know what to try. I changed Group to LandGroup.
I did try making 8 subquerys for the different LandGroups, but trying to combine them into one query didn't work at all.
Mar 8 2012, 03:00 PM
If you wanted to return results for one group at a time... my unexpert opinion is that you could do this in two stages. The first query would take an operator input for LandGroup (number), and return a single LandGroup (number) and OwnerName(s). Then the second query, based on the first... would return all records from your table for each OwnerName record... showing all fields if desired.
Mar 8 2012, 03:24 PM
I guess that's what I was fumbling around trying to do when I made the separate subquerys. I'll try to explore that a little further.
Mar 8 2012, 03:43 PM
I'm not sure I understand the need for subqueries...
Let me try paraphrasing -- you want to see all land, by Owner, by LandGroup, sorted by LandGroup.
If that's a fair description, take a look at Access HELP for "Totals queries".
Mar 8 2012, 03:59 PM
I don't necessarily want all land..........
only if owner owns land in LandGroup1. If so, then yes, I want all his/her lands returned. But if owner only owns land in, say LandGroups 5 & 7, then I don't want any of his/her records returned.
Owner owns land in LandGroup 1 & 3 & 4, then return all records for this owner.
Owner owns land in any LandGroup, or multiple LandGroups - but none in LandGroup1, then don't return (select) any records for this owner.
Mar 8 2012, 05:04 PM
I just tried my method. It may not be the most elegant, but it worked like a million bucks. I made both queries in about 5 minutes...
Mar 8 2012, 06:48 PM
Did you get a chance to take a look at the "Totals" query information? I suspect you could use that LandGroup = 1 as a selection criternion.
Mar 9 2012, 12:30 PM
Using LandGroup = 1 does indeed return all LandGroup 1 records, but then I need to use that as the basis for then selecting the other LandGroup records.
In any case, got it done now, thanks everyone for your help.
Mar 9 2012, 12:34 PM
briangriffey; I ended up doing what I think you did, somewhat like that anyway. I made two queries, one for LandGroup1, then ran the the second query 7 times using LandGroup2, then LandGroup3, etc through LandGroup8, relating the AcctNumber to AcctNumber in first query. Appended all to a temp table. Not sure if thats what you did or not, but it ended up working out.
All done now, Thanks everyone for your help.
Mar 9 2012, 12:48 PM
"... need to use it as a basis ..."
OK, first build the query that gets the LandGroup=1 customers.
Then build a second query that uses the first and finds all other properties <> LandGroup=1 for those customers.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here