Field in table dictates table's properties? Possible?

  • Thread starter Thread starter steve c matco
  • Start date Start date
S

steve c matco

I have a table where I manage information for hot air
balloon flights at a local balloon festival. I want to
be able to have a field on that table (the "Passenger
Capacity",) to dictate the number of slots on that record
for passengers.

So for example, one balloon may hold four, so when I tell
access that, I want it to create that record with four
passenger name slots "PassengerName1, Pass2, Pass3, etc."
But then I want it to create Pass1-6 for a flight with a
capacity of 6.

Is this possible?
 
Steve,

I'm afraid what you're asking to do is not possible. The number of fields in
an Access table is determined at table design, is constant for all records
and can only be changed through a design change (again, any change will
apply to all fields).
What you are after requires a different database design, actually;
specifically, two tables (with a one-to-many relationship), one for the
ballons, and a second one for the passengers, joined on a common field that
uniquely identifies a balloon (primary key in the balloons table). So, your
tables would be something like (with some imagination on my part):

[tblBalloons]
BalID (PK)
Bal_Name
Type
Length
....
....
....

[tblPassengers]
PassID (PK, autonumber, optional)
BalID
LastName
FirstName
Sex
Age
....
....
....

For instance, for a ballon for 4 passengers, you would have 1 record in
tblBalloons and 4 records in tblPassengers (with the same BalID as the
matching record in tblBalloons).
It is a good idea to create a permanent relationship between the two tables
(Tools > Relationships) and enforce referencial integrity on it.

HTH,
Nikos
 
Back
Top