Selecting data from 2 tables for a 3rd table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I’m developing a database for a river rafting company that uses guides. I
have tables for the data about the group and individual guides respectively.
Each time a guide does a trip, he earns a certain number of points.

Here’s my table structure:

tblGroupData
GroupID (key)
GroupName

tblGuideData
GuideID (key)
GuideName

tblGuideAssignments
GuideID
GroupID
Points

What I need to do is to “assign†guides to groups along with the number of
points they (guide) got for doing the trip. That means that
tblGuideAssignments would be nothing but numbers.

When I assign the guides to the groups, I want to be able to choose from the
guides in the table by their last name (not their ID which has no meaning),
but the ID is stored.

I have one form (frmMain) that contains all the fields in tblGroupData. On
that I have a command button that would open another form where I would make
the assignment of guides to the group showing in the Main form at the time.

I must be brain dead tonight, because I can’t figure out how to do this.
Even a little hint to send me in the right direction would be appreciated.

Jerry
 
Jerry, how about a subform?

If you add a subform to frmMain, based on tblGuildeAssignments, it will
automatically show just the guides assigned to the group.

In the subform, you can use a combo for the GuideID field. Set the RowSource
of the subform so that it returns GuideID in the first column, and GuideName
in the 2nd. Then set these properties for the combo show it shows the guilde
name instead of the id:
Column Count 2
Column Widths 0
Bound Column 1
 
Thanks, Alan. I knew a subform would work, but my main form is already
almost a full screen, with no room for a subform. I may have to go to a
tabbed main form. Your other details should get me going in the right
direction.

Jerry
 
Back
Top