Selecting from a master table based on info in a detail table

[Chris Belcher]

While I'm sure this is simple I just can't figure it out.

Table A (assignments) is on the One side of a One to Many relationship
With Table B (assignees)Ther e are many assignees assigned the one task.

I have the relationship set to Join all records in Table and only the
records in Table B that Match.

Currently I have a form that displays all of Table a's data. Using the
the OnEnter property of the AIKey's field I display Table B's data. Now
I want to display Table A's data for each AIKey that contains an given
assignee. In other words, If an assignee isn't included in the scope of
the assignment then I don't want that assignment included in table A's
records in the form.
Maybe a picture is better:
Table A Table B
AIKey (Primary Key) AIKey
Assignment Assignee

Let's assume The data looks like this:

Table A Table B
AIKey Task AI Key Assignee
_______________ _______________ _______________ ______
04-1 Sweep Floors 04-2 God
04-2 Create World Peace 04-1 God
04-3 Photo Op 04-2 George Bush
04-3 George Bush
04-3 Mortal
If the Variable is "Mortal" the Output would be:
04-3 Photo Op

If the Variable is "God" the Output would be:
04-2 Create World Peace
04-1 Sweep Floors

If the Variable is "George Bush" the Output would be:
04-2 Create World Peace
04-3 Photo Op

Any Help would be appreciated!
Chris

[Larry Daugherty]

Hi Chris,

It looks like you want the named Assignee to be in the criteria for field
Assignee:

Where Assignee like "Rusty Jones"

HTH
--
-Larry-
--

"Chris Belcher" <ch******@bells outh.net> wrote in message
news:Q5******** **********@bign ews2.bellsouth. net...

While I'm sure this is simple I just can't figure it out.

Table A (assignments) is on the One side of a One to Many relationship
With Table B (assignees)Ther e are many assignees assigned the one task.

I have the relationship set to Join all records in Table and only the
records in Table B that Match.

Currently I have a form that displays all of Table a's data. Using the
the OnEnter property of the AIKey's field I display Table B's data. Now
I want to display Table A's data for each AIKey that contains an given
assignee. In other words, If an assignee isn't included in the scope of
the assignment then I don't want that assignment included in table A's
records in the form.
Maybe a picture is better:
Table A Table B
AIKey (Primary Key) AIKey
Assignment Assignee

Let's assume The data looks like this:

Table A Table B
AIKey Task AI Key Assignee
_______________ _______________ _______________ ______
04-1 Sweep Floors 04-2 God
04-2 Create World Peace 04-1 God
04-3 Photo Op 04-2 George Bush
04-3 George Bush
04-3 Mortal
If the Variable is "Mortal" the Output would be:
04-3 Photo Op

If the Variable is "God" the Output would be:
04-2 Create World Peace
04-1 Sweep Floors

If the Variable is "George Bush" the Output would be:
04-2 Create World Peace
04-3 Photo Op

Any Help would be appreciated!
Chris

[Chris Belcher]

Larry Daugherty wrote:

Hi Chris,

It looks like you want the named Assignee to be in the criteria for field
Assignee:

Where Assignee like "Rusty Jones"

HTH

Larry... I had tried WHERE, but not in combination with a DISTINCT...
Sometimes ya just need a Kick to get your attention. Thanks!
Chris

This Works!
SELECT DISTINCT [AI Master].[AI Key], [AI Master].[AI Title]
FROM [AI Master] LEFT JOIN [AI Detail] ON [AI Master].[AI Key] = [AI
Detail].[AI Key]
WHERE ((([AI Detail].[Assignee Short])="RS"));