Complicated query... Help?

[Grahammer]

I'm trying to build an Access form to select some Crews for an Area from a
list of Crews belonging to a Branch. Table layout is shown below.

I'm hoping to build a single query that I can use as the RowSource for a
listbox on one of my Access forms.

In English, what I'm looking for is:
Show me all the Crews for a specific Branch and show a "1" in the last
column if this Crew is also used by the specified Area or a "0" if this Crew
is NOT used by the specified Area. The specified branch is held in
txtBranch. The specified area is held in txtArea.

The SQL query I was hoping to use to populate my listbox would be something
like this:

SELECT (SELECT COUNT(1) FROM CrewAreaLink WHERE
AreaKeyLink=[txtArea].[value]) AS Chosen, Crews.CrewDesc,
Crews.BranchKeyLink FROM Crews WHERE
(((Crews.BranchKeyLink)=txtBranch.value));

txtArea is a textbox holding the index key of the selected Area.
txtBranch is a textbox holding the index key of the selected Branch.
The COUNT(1) should be 0 or 1 depending on if this Crew is selected for this
Area. There should only ever be one match or no matches.

I know I've probably botched the logic someplace, but I can't get my head
around the SQL for this...

Any suggestions are appreciated!

Table BRANCHES: (Defines our Branches)
BranchKey - Autonumber
BranchDesc - C25

Table CREWS: (Defines our Crews and which Branch they belong to)
CrewKey - Autonumber
CrewDesc - C25
BranchKeyLink - Number (Linked to Branches.BranchKey)

Table AREAS: (Defines our Areas and what Branch they belong to)
AreaKey - Autonumber
AreaDesc - C25
BranchKeyLink - Number (Linked to Branches.BranchKey)

Table AREACREWLINK: (Defines that a Crew is used by an Area)
AreaKeyLink - Number (Linked to Areas.AreaKey)
CrewKeyLink - Number (Linked to Crews.CrewKey)

 

[Allen Browne]

Use a subquery to get the information about whether the crew is also
available in the other area:

1. Create a query into the Crews and AreaCrewLink table.

2. Drag the fields you wish to see into the grid.

3. Set the Criteria for the Crews.BranchKeyLink field, e.g.:
[Forms].[Form1].[txtBranch]

4. Type something like this into a fresh column in the Field row:

EXISTS (SELECT AreaKeyLink FROM AreaCrewLink
WHERE (AreaCrewLink.CrewKeyLink = Crews.CrewKey)
AND (AreaCrewLink.AreaKeyLink = [Forms].[Form1].[txtArea]) )

That will yield a true (-1) / false (0) value.


If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Grahammer]

Thanks muchly!

I don't work with queries enough to retain what I use...