Help using a complex and/or sequence

[EricTheRed]

Okay, so I have a table structured somewhat like this:

[ID] [Category]
1 | 7
1 | 2
1 | 3
2 | 7
2 | 9
3 | 6
3 | 7
3 | 2

I'd like to be able to find all the IDs where Category = [some variable criteria statement]. For example, a query to find all the IDs having a Category entry = 7 OR (6 AND 2) should return simply 3. I've tried all the methods I could think of to no avail. Help! Thanks in advance.

- Eric

 

[GumbyD]

Eric -

Not quite sure what you want here? The Criteria statement you are asking for is when the data is equal to 7 or when the data is equal to 6 and 2. Since the data in your field will never be equal to both 6 and 2 at the same time then it will only return the values when the field is equal to 7. Is that what is happening? One way of doing this would be to create one query that gets the IDs for Category 7 and another that gets them for Category 2 or 6 and then create a third query that joins the two above by ID. Then just pull in the ID field and you will only get the values returned in both of the originals.

Not sure thats what you need. Can you provide a bit more detail?

GumbyD

 

[Tiro Cupidus]

The way I read it, he wants to know if there are any IDs that have a category of 6 in one record and also a category of 2 in another.

I'm pretty sure you could do it with code and a recordset, but I can't seem to think of a way with just an Access query. I've never needed a query like it, but surely there is a way. It seems to me that some people would use it quite often.

 

[Rich]

Article ID: Q100131

 

[Tiro Cupidus]

Ah, thanks, Rich. I think I was in the middle of doing something similar to Method 2 when I saw your post. Except my function was called fManyMany... heh. Here's a link for anyone who wants. I'll have to read the article rather than glance at it when I get time.

 

[EricTheRed]

Sorry for the somewhat laggard reply, but things have been ... hectic to say the least. I hate deadlines passionately.

As for the IN operator, unless I'm understanding things incorrectly, doesn't it work much like a series of OR statements? Whereas I need both OR as well as AND. Taking my previous example and restructuring it somewhat (just for explantion purposes, the structure is the same):

[ID] [Categories]
1 | 2, 3, 7
2 | 7, 9
3 | 2, 6, 7

I'd like to be able to select IDs having both categories 2 AND 7, OR having category 6. Hope that clears things up. If I've completely understood things, bleh. Let me know! Thanks.

- Eric

 

[Fornatian]

SELECT YourTable.ID
FROM YourTable
WHERE (((YourTable.Category)=6));
UNION 
SELECT YourTable.ID FROM YourTable
WHERE (((YourTable.ID) 
In ([i]SELECT YourTable.ID FROM YourTable WHERE (((YourTable.Category)=7));[/i])) 
AND ((YourTable.Category)=2));

This union query seems to work. It picks out those ID with a 6 then bolts those results on another query which firstly pulls the ID's of those with a 7 category and looks for a 2 in the category field.

 

[EricTheRed]

Thanks. That seems to have worked. Now all I have to do is figure out how to build the queries dynamically. Bleh!

 

[Fornatian]

Do you really need to build the query dynamically or just change the queried values, which is simpler because you can just reference form fields:

SELECT YourTable.ID
FROM YourTable
WHERE (((YourTable.Category)=[Forms]![YourForm]![YourField1]));
UNION 
SELECT YourTable.ID FROM YourTable
WHERE (((YourTable.ID) 
In (SELECT YourTable.ID FROM YourTable WHERE (((YourTable.Category)=[Forms]![YourForm]![YourField2]));)) 
AND ((YourTable.Category)=[Forms]![YourForm]![YourField3]));

If you need to change the actual tables referenced in the query then you need VBA to create a SQL string then either run it directly or assign it to a querydef.

 

[Fornatian]

PS. It is faster to create an extra query and use a join instead of a subselect statement.

 

[EricTheRed]

Unfortunately, I have to build the query myself. Both the number and position of the ANDs and ORs varies depending on user input. The query I'm trying to create is actually the SQL version of my own little simplified query-builder I've creater for my users.

For anyone who's interested, I've gone for a system that first identifies the AND groups, and creates the queries for that, and then tacks on the OR values. I haven't had much time to implement it, so I can't say for certain if it works, but I'm pretty certain it'll be alright (and it better!).