Select or "ALL" Based on a unbound form Newbie

[Chris Belcher]

In my table I have 2 fields

[AIKey] and [Assignee Short]
[AIKey] (one side) is the PK of [AI Master] and FK in [AI Detail] (Many
side}

On my form I have [cbxAssignee] (a unbound combo box)

The GOAL is to:
show each instance of [AI Key]
When [AI Detail].[Assignee Short]=[cbxAssignee]
OR 1 instance of each [AI Key]
when [cbxAssignee] = "All"

My attempts got me this far:

Using the following SQL:

SELECT [AI Detail].[AI Key], [AI Detail].[Assignee Short]
FROM [AI Detail]
WHERE ((([AI Detail].[Assignee Short])=[Forms]![Action Item
Review]![cbxassignee])) OR ((([Forms]![Action Item
Review]![cbxassignee])="All"))
GROUP BY [AI Detail].[AI Key], [AI Detail].[Assignee Short];

I get this:

-----------------------------------
| AI Key | Assignee Short |
-----------------------------------
| 04-217 | HL |
| 04-218 | HL |
| 04-222 | HL |
| 04-223 | HL |
-----------------------------------

A correct answer when "HL" is the value of [cbxassignee]. (I dont need
the [Assignee Short] I've just been using it to see whats going on in
the query.)

But...


If the value of [cbxassignee] = "ALL" I get:
-----------------------------------
| AI Key | Assignee Short |
-----------------------------------
| 04-217 | CJ |
| 04-217 | HL |
| 04-217 | RS |
| 04-218 | CJ |
| 04-218 | HL |
| 04-218 | JV |
| 04-218 | SP |
| 04-219 | PB |
| 04-219 | RS |
| 04-219 | WM |
| 04-220 | JV |
| 04-222 | HL |
| 04-223 | HL |
| 04-224 | CJ |
| 04-224 | JV |
| 04-224 | SP |
| 04-226 | CJ |
| 04-226 | SP |
| 04-227 | CJ |
| 04-227 | SP |
-----------------------------------

Where I'd really like this:

--------------
| AI Key |
--------------
| 04-217 |
| 04-218 |
| 04-219 |
| 04-220 |
| 04-222 |
| 04-223 |
| 04-224 |
| 04-226 |
| 04-227 |
--------------

I understand why the "All" value returns this but don't understand how
to get around it.
My guess is that a nested queryis involved, but it's over my head.
If you reply with the syntax a brief explanation of how you attacked the
problem would help me learn.

ANY help is appreciated!

Chris Belcher

 

[Chris Belcher]

I figured it out! It was selecting the [Assignee Short] that was doing it:
The correct SQL is:

SELECT DISTINCT [AI Detail].[AI Key]
FROM [AI Detail]
WHERE ((([AI Detail].[Assignee Short])=Forms![Action Item
Review]!cbxassignee)) Or (((Forms![Action Item Review]!cbxassignee)="All"))
GROUP BY [AI Detail].[AI Key], [AI Detail].[Assignee Short];

For what it's worth. I use this query in another to pull all the
AIMaster info onto a form. Bad design or standard fare?

In my table I have 2 fields

[AIKey] and [Assignee Short]
[AIKey] (one side) is the PK of [AI Master] and FK in [AI Detail] (Many
side}

On my form I have [cbxAssignee] (a unbound combo box)

The GOAL is to:
show each instance of [AI Key]
When [AI Detail].[Assignee Short]=[cbxAssignee]
OR 1 instance of each [AI Key]
when [cbxAssignee] = "All"

My attempts got me this far:

Using the following SQL:

SELECT [AI Detail].[AI Key], [AI Detail].[Assignee Short]
FROM [AI Detail]
WHERE ((([AI Detail].[Assignee Short])=[Forms]![Action Item
Review]![cbxassignee])) OR ((([Forms]![Action Item
Review]![cbxassignee])="All"))
GROUP BY [AI Detail].[AI Key], [AI Detail].[Assignee Short];

I get this:

-----------------------------------
| AI Key | Assignee Short |
-----------------------------------
| 04-217 | HL |
| 04-218 | HL |
| 04-222 | HL |
| 04-223 | HL |
-----------------------------------

A correct answer when "HL" is the value of [cbxassignee]. (I dont need
the [Assignee Short] I've just been using it to see whats going on in
the query.)

But...


If the value of [cbxassignee] = "ALL" I get:
-----------------------------------
| AI Key | Assignee Short |
-----------------------------------
| 04-217 | CJ |
| 04-217 | HL |
| 04-217 | RS |
| 04-218 | CJ |
| 04-218 | HL |
| 04-218 | JV |
| 04-218 | SP |
| 04-219 | PB |
| 04-219 | RS |
| 04-219 | WM |
| 04-220 | JV |
| 04-222 | HL |
| 04-223 | HL |
| 04-224 | CJ |
| 04-224 | JV |
| 04-224 | SP |
| 04-226 | CJ |
| 04-226 | SP |
| 04-227 | CJ |
| 04-227 | SP |
-----------------------------------

Where I'd really like this:

--------------
| AI Key |
--------------
| 04-217 |
| 04-218 |
| 04-219 |
| 04-220 |
| 04-222 |
| 04-223 |
| 04-224 |
| 04-226 |
| 04-227 |
--------------

I understand why the "All" value returns this but don't understand how
to get around it.
My guess is that a nested queryis involved, but it's over my head.
If you reply with the syntax a brief explanation of how you attacked the
problem would help me learn.

ANY help is appreciated!

Chris Belcher