Help on Grouping Query to find all rows that only have the same value

[mdProgrammer]

I have a query that I'm trying to create for Access, with limited success (data's not always correct, and can be very slow). Anyway, the query works in a similar way to this (only I was trying to change the Having clause to count if the item didn't say "close", and ignore those IDs):

http://stackoverflow.com/questions/...rows-that-have-the-same-values-as-another-row

Here's what the query is supposed to do -
1. For each UserID, find only records where ALL items are closed (anything other than close, ignore).
2. From another table, get the statuses that are open.

This is a watered down example of the actual query, which contains 7 tables. My first attempt was using subqueries (selecting rows from the table containing items, then matching with auths. 2nd attempt was to have a query that creates a temporary table, and require the user running this query to run two queries. This may confuse them eventually...), which promptly caused Access to lock up. The record ID that should display in this example is ID 1.

Name_ID	Item_Status	Auth_Status
1	Closed		Open
1	Closed		Closed

2	Closed		Closed
2	Closed		Closed
2	Closed		Closed

3	Closed		Open
3	Open		Closed

4	Open		Open
4	Open		Open

 

[Andrzejek]

> This may confuse them eventually...
If you are talking about people reading your post - Yes, your explanation IS confusing (or is it just me...?)

1. For each UserID, find only records where ALL items are closed (anything other than close, ignore).
Well, you do not have 'UserID', you have 'Name_ID'

The record ID that should display in this example is ID 1. [blue]And 2?[/blue]

[pre]
Name_ID Item_Status Auth_Status
1 Closed Open[blue]
1 Closed Closed

2 Closed Closed
2 Closed Closed
2 Closed Closed[/blue]

3 Closed Open
3 Open Closed

4 Open Open
4 Open Open
[/pre]

Have fun.

---- Andy

 

[mdProgrammer]

You're not the only one that finds it confusing. It's common to get these types of requests from a few staff members where I work... (fortunately, it's just a few)

User_ID and Name_ID are the same thing - just didn't change them.

ID#1 - Would show as all 'items' are closed', and at least one 'auth' is open.
ID#2 - Would not show, since all 'auths' are closed, even though all 'items are closed'
The rest have at least one item that is 'open', and wouldn't show in the query.

 

[mdProgrammer]

Just to add (there doesn't seem to be a way to edit posts here - unless that section above Homer > Forums > Programmers, etc. is the edit commands and our network decided that it's spam content and blocked it...), that query is only the half of it. There's another where a specific category is involved, but I think if I get this thing solved, the other one might be a bit easier.

 

[mdProgrammer]

Just to update, I've did the above query using a crosstab, and then another query to get the required records. That query took 1 1/2 minutes. The second part was where the item was "closed", except for one particular category of items. That query is taking well over 5 minutes to run, but the crosstab works and can be manually sorted. Not perfect, but it's a work-around. (Last option is to use the production SQL server, which all of the tables link to, but that's not a favorable option).

 

[BigRed1212]

Yes, I agree with Andy. Confusing, not enough info, and the final product is unclear.

This is probably useless because the example provided is probably way too simplified.

From the example data if you query for all records where auth_status is 'open' you will get {1,3,4}.

Then if you query that subset for all records where item_status <> 'closed' you will get {3,4}.

Querying for id's not in both sets (unmatched query) results in {1}, which appears to be the desired result.

This may be similiar to the "first attempt was using subqueries" but that us unknown.