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.

Any pointers?

Thanks, Steve

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

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.

Thanks, Steve

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.

This post has been edited by briangriffey: Mar 8 2012, 03:02 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.

Thanks, Steve

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

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.

Thanks, Steve

Steve,
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...

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.

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.

Steve

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.

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