Putting multiple values in a single field

[Timbo]

Hello all,
I'm writing an access database that holds our job details. (At the moment
we're using an excel spreadsheet). The work we do involves modifying
aircraft. Some of the mods we do may only apply to a single aircraft, or
they may apply to multiple aircraft.

So far I the following (relevant to this problem) tables:


[JOB REGISTER]
Register ID (Autonumber, primary key) (I have the job number separate
from the register id because a job may need a revision or require multiple
invoices)
Job Number (text)
Description (text)
Aircraft Registration (lookup to the aircraft table)
... other stuff, customer, fiancial info, documentation raised, etc.


[Aircraft]
Aircraft Registration (text)
... other stuff

At the moment the table will only accept a single value for an aircraft,
but, ideally, I will need it to be able to accept multiple values. One of
our customers may have 40 aircraft on a single job.

So far possible solutions I have come up with are:
a) a new record in the job register for each aircraft - unwieldly
b) Putting multiple values for aircraft in the aircraft field (that
appears to only be able to accept single values). - ideal solution
c) Creating a variable where each bit represents a single aircraft.
i) from all possible aircraft - not feasible, there are 17,576 (26^3)
different combination of aircraft registration in Australia, which equates
to a field 2.14 kb per job register entry. And we have jobs for aircraft
registered outside Australia.
ii) each bit represents a record in another table - either update the
size of the variable each time the table gets updated, which will then
trickle down to the job register requiring updating; or initally set the
size of the variable large enough to handle an expanding table.
d) Add another table "FLEET AIRCRAFT",and concatenate the lookup table
for the aircraft field [JOB REGISTER] with the "Fleet Aircraft" table. -
once again I'd like the fleet aircraft table to link back to the aircraft
table, same problem.

Any thoughts or suggestions about the best way to go would be
appreciated.

Timbo

 

[John Vinson]

At the moment the table will only accept a single value for an aircraft,
but, ideally, I will need it to be able to accept multiple values. One of
our customers may have 40 aircraft on a single job.

You're using a relational database. USE IT RELATIONALLY!

You do indeed need to store a separarte record for each aircraft
involved in a job. I'd assume that a given aircraft might over time be
involved in multiple jobs, as well as each job being involved with
multiple aircraft; if so you need three tables:

[JOB REGISTER]
Register ID (Autonumber, primary key) (I have the job number
separate from the register id because a job may need a revision or
require multiple invoices)
Job Number (text)
Description (text)
... other stuff, customer, fiancial info, documentation raised,
etc.


[Aircraft]
Aircraft Registration (text) <Primary Key>
... other stuff

[JOBAircraft]
RegisterID Long Integer <link to Job Register>
Aircraft Registration <link to Aircraft>
<any information concerning *this* aircraft and *this* job>

This structure will let you link any number of aircraft to one or more
jobs.