Update Query

[SITCFanTN]

I have a database that I've built for a mission trip for numerous churches.
i have all the travelers in one table. What I need to do is update the table
quickly when we assign each traveler their hotel, bus number and leader's
name. I'm thinking of doing a select query first by asking "What Church" and
bring in all the travelers from that church. Then I want to update those
travelers with their bus number, hotel name and leader's name. Each traveler
from each church will ride the same bus, stay at the same hotel and have the
same Leader. The exact field names are "HotelID", "BusNoID" and
"ChaperoneID". All of these fields are dropdown boxes and I think this is
what is causing my query from not working. Am I updating these records the
most efficient way. There are over 200 churches and 2000 travelers. I
appreciate your help, thank you.

 

[Evi]

It looks like you are hitting the problem of having been seduced into using
those cute little Lookup fields. Repent immediately and start using Forms
with combo boxes

What is your database structure?
I am expecting to see

TblChurch
ChurchID (primary key, autonumber)
ChurchName
ChurchAddress

TblRegion
RegID
MissionRegion
CountryID

TblCountry
CountryID
Country

TblHotel
HotelID
HotelName
DefaultRoomRates (these may not be what you get charged on the day however)


TblPerson (this includes the
PersonID (PK)
PFirstName
PSurName
ChurchID (linked from ChurchID in TblChurch - this assumes that a person can
only belong to one church at a time)
Leader (a yes no box which can be ticked if that person is a leader of a
group)

TblBus
BusID
BusDetail

TblMission
MissID
PersonID
BusID
LeaderID (this is also linked from TblPerson which has been added a second
time to the Relationships window and query grid - I don't like using these
double links but you will need it in this case)
HotelID
HotelRate
RegionID
MissionDate

You can add records to TblMission using an unbound form. It contains one
combo based on the Churches table, one based on TblBus and one on TblHotel,
, one on TblRegions, one based on the query which is based on TblPeople but
filtered to show only those people where the Leader box has been ticked (ie
True). This will be filtered by the Churches combo

You can choose your church which will filter the Leader combos. Choose the
correct leader, bus and Hotel from the choice available. You can then press
a button which will build your Appendquery using the data in your boxes and
append those details onto TblMission

Is this the idea of what you want to do?

Evi