Query to return available items 1

[huggybear]

Hello

I have two combo boxes on a form. cboResource is based on tblResources and has a ResourceKey column and a Name Column.
cboTeam is based on tblTeam and has TeamKey and TeamName. When a user chooses a Resource and a Team, the Resource is added to tblTeamResource which has all the necessary fields (I think).

My desire is to requery cboTeam when cboResource is updated to only show teams that the selected Resource is not already on, and likewise, when cboTeam is updated, show only those Resources not already on the chosen Team.

Forming the SQL for this is proving to be beyond my limited experience and I've been hacking away most of the day. Can anyone please give me some suggestions?

Thanks, Bear

 

[GingerR]

ok i'm not much of a SQL girl. but here's answer using queries.
the gist to make sure i'm understanding right: i made a query which creates all possible combinations of Resources and Teams (qryTeamResourceCombo). I compare this to the tblTeamResources to figure out what Teams and Resources are still available to pick from and make these the underlying queries for the combo boxes when opening the form. so, when opening the form (and when moving to a new record) the combo boxes are not populated with tblTeam and tblResources, but of the results of the above queries (TEAMS which still can be chosen and Resources which still can be chosen). Then, once either combo box is changed (OnChange event) i change the RowSource of the opposite combo box to only show items which are not yet selected for that team or resource. right?

i know this is a lot, but it works right. i put all queries into SQL so you can build them.

i made three tables as you described above.
then:

1) qryTeamResourceCombo
SQL =

SELECT tblTeam.TeamKey, tblResource.ResourceKey
FROM tblTeam, tblResource;

2a) qryAvailableTeamsAll Teams that are available to choose from
SQL =

SELECT DISTINCT qryTeamResourceCombo.TeamKey, tblTeam.TeamName
FROM (qryTeamResourceCombo LEFT JOIN tblTeamResource ON (qryTeamResourceCombo.TeamKey = tblTeamResource.TeamID) AND (qryTeamResourceCombo.ResourceKey = tblTeamResource.ResourceID)) INNER JOIN tblTeam ON qryTeamResourceCombo.TeamKey = tblTeam.TeamKey
WHERE (((tblTeamResource.ResourceID) Is Null));

2b) qryAvailableResourcesAll Resources that are available to choose from
SQL =

SELECT DISTINCT qryTeamResourceCombo.ResourceKey, tblResource.ResourceName
FROM (qryTeamResourceCombo LEFT JOIN tblTeamResource ON (qryTeamResourceCombo.ResourceKey = tblTeamResource.ResourceID) AND (qryTeamResourceCombo.TeamKey = tblTeamResource.TeamID)) INNER JOIN tblResource ON qryTeamResourceCombo.ResourceKey = tblResource.ResourceKey
WHERE (((tblTeamResource.TeamID) Is Null));

3) frmTeamResource
cbo box 1: name = TeamID
RowSource = qryAvailableTeams
Column Count = 2; ColumnWidths = 0";1"

in TeamID OnChange Event (set ResourceID combo box to only show what Resources are available for the just-chosen Team)

    Me.ResourceID.RowSource = "qryResourceWOTeam"
    Me.ResourceID.Requery

cbo box 2: name = ResourceID
RowSource = qryAvailableResources
Column Count = 2; ColumnWidths = 0";1"

in ResourceID OnChange Event (set Team combo box to only show what Teams are available for the newly-chosen Resource)

    Me.TeamID.RowSource = "qryTeamWOResource"
    Me.TeamID.Requery

4a) qryChosenResource SQL =

SELECT tblTeamResource.TeamID, tblTeamResource.ResourceID
FROM tblTeamResource
WHERE (((tblTeamResource.ResourceID)=[Forms]![frmTeamResource]![ResourceID]));

4b) qryChosenTeam SQL =

SELECT tblTeamResource.TeamID, tblTeamResource.ResourceID
FROM tblTeamResource
WHERE (((tblTeamResource.TeamID)=[Forms]![frmTeamResource]![TeamID]));

5a) qryResourceWOTeam SQL =

SELECT tblResource.ResourceKey, tblResource.ResourceName
FROM tblResource LEFT JOIN qryChosenTeam ON tblResource.ResourceKey = qryChosenTeam.ResourceID
WHERE (((qryChosenTeam.ResourceID) Is Null));

5b) qryTeamWOResource SQL =

SELECT tblTeam.TeamKey, tblTeam.TeamName
FROM tblTeam LEFT JOIN qryChosenResource ON tblTeam.TeamKey = qryChosenResource.TeamID
WHERE (((qryChosenResource.TeamID) Is Null));

6) in the form's OnCurrent event (re-setting combo boxes to same as orig RowSource whenever go to a diff record):

    Me.TeamID.RowSource = "qryAvailableTeams"
    Me.ResourceID.RowSource = "qryAvailableResources"
    Me.TeamID.Requery
    Me.ResourceID.Requery

whew. ok, so i know it's long but from what i understand, it's what you want. just make the stuff like i listed and you'll see. maybe you can improve on it or whatever, but this will get you there nonetheless.

good luck--g

 

[rac2]

This may help with the SQL part of this problem. I don't know how you will keep the name selected from cboResource but assuming you can do that in a variable named @vResourceName then -

This query can be used to make a view, it will have all of the teams that you don't want, call it qResourceUsedBy

SELECT tblTeamResource.TeamName, tblResource.Name
FROM tblTeamResource a
JOIN tblResource b ON a.Name = b.Name
WHERE tblTeamResource.Name = @vResourceName

Then the list of Teams remaining is -

SELECT * FROM tblTeam WHERE TeamName NOT IN
   (SELECT TeamName FROM qResourceUsedBy)

 

[huggybear]

Thanks to you both. I haven't tried these yet as I just returned from a meeting but will be trying in the morning.

Be forewarned: I may be back!

Thanks again, Bear

 

[huggybear]

Hello again,

I took both your suggestions under advisement and decided to try rac2's suggestion first because it looked a bit easier. By substituting the actual fields and tweaking it a bit I got it to work. Thanks!

Here is the code I used, with line continuation characters removed (you'll notice that I forgot to mention tblProject in my original post, but it really needed to be there in case more than one project had a team with the same name):

Private Sub cboResourceName_Change()

Dim strSQL As String
strSQL = ""
strSQL = "SELECT t.TeamKey, t.TeamName FROM tblTeam t"
"WHERE t.TeamKey IN (SELECT p.TeamKey FROM "
"qryProjectTeams p WHERE p.ProjectKey = "
Forms("frmProjectInformation&quot![ProjectKey] & &quot "
"AND t.TeamKey NOT IN (SELECT DISTINCTROW TeamFK
FROM qryResourceTeams WHERE ResourceKey = " &
cboResourceName & &quot"


cboTeamName.RowSource = strSQL

End Sub