Re: Select or "ALL" Based on a unbound form Newbie
From: Chris Belcher (chrisbnp_at_bellsouth.net)
Date: 07/31/04
- Next message: korgman: "Re: Word cannot "see" all the queries"
- Previous message: Chris Belcher: "Select or "ALL" Based on a unbound form Newbie"
- In reply to: Chris Belcher: "Select or "ALL" Based on a unbound form Newbie"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 31 Jul 2004 08:29:29 -0400
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?
Chris Belcher wrote:
>
> 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
- Next message: korgman: "Re: Word cannot "see" all the queries"
- Previous message: Chris Belcher: "Select or "ALL" Based on a unbound form Newbie"
- In reply to: Chris Belcher: "Select or "ALL" Based on a unbound form Newbie"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
