AbstractGofer said:
The reason for a many-to-many is because (unless I have it all wrong?) I need
input data in all the fields from the cancel table and that, that particular
dispatch booking gets cancel out along with its entirety.
"Many-to-many" doesn't refer to how many fields you have, but rather to
how many records in one table link up to a record in another table. For
example, one [student] may be enrolled in several [class]es, and one
[class] may have several [student]s enrolled in it. That would be many
to many.
For the record's "entirety", you're just referring to all the fields in
the record, I think. And when you refer to a record in an Access Table,
you are automatically referring to all of its fields. So I think you
are worrying unnecessarily.
To decide how to link them, you need to determine how many [cancel]
records you want to allow to match one [dispatch] record, and vice versa.
If you will never have more than one of each kind of record attached to
the corresponding record in the other table, then that is a one-to-one
relationship, and in that case you might want to combine all the fields
into each record in just one table. Or, if several of the fields have
similar types, such as [dispatch].[date] and [cancel].[date], you could
use just one Table for all of them and add a new field that determines
what kind of record it is: one value for "dispatch" records and a
different value for "cancel" records. If you did that, the
[dispatch].[date] field would identify the date of "dispatch" event if
it's a "dispatch" record, and the same [dispatch].[date] field would
contain the date of the "cancel" event if it's a "cancel" record. If
"dispatch" records have a field that "cancel" records don't have, you
could just leave it empty (=Null value) in a "cancel" record.
If you did combine two Tables linked as 1:1 into a single Table, though,
I think you'd want to rename the combined Table to something like
[T_Appointments] to give an idea of what kind of information is inside it.
I think any of the choices I mentioned (1:1 Tables, Table with separate
fields, or Table with shared fields and a new field identifying type)
might work. But I see no way for a many-to-many relationship to work,
unless the records mean something different from what they seem to mean.
The goal is to make the cancellation procedure automated with a push of a
button through a query.
You'll be able to add that feature *after* you have a table design that
reflects what you're trying to do. It's not difficult to add Queries,
nor to set up buttons to run them.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
