Add records to a joined table

[datamerc]

Hi,

I've got two tables:

customers (Table 1):
- customer_id (auto number, PK)
- first_name
- last_name
- address
- etc...

gravesites (Table 2)
- gravesite_id (auto number, PK)
- grave
- block
- customer_id (from customers table)
- etc...

and they are joined together into a single table in a Form object and
view in the Datasheet view as this:
Joined Table
- grave
- block
- customer_id
- first_name
- last_name
- address
- etc...

With this I can view the contents of the two tables as one table.
However, I need to be able to create a customer (from this view) that
does not exist in the "customers" table. As it is now, if a grave
record has customer information in it, I can update every thing (i.e.
change the address) or I can select from an existing customer record...
but I can't create a new customer record from this view. Is there
anyway I can accomplish this?

Thanks in advance.

 

[Guest]

From your table structure it appears that a customer can be associated with
many grave sites. This is known as a one-to-many relationship. If this is
the only relationship then click on VIEW - Relationships and add both tables.
Click on the customer_id in the customer table and drag to the gravesite
customer_id and select referential integerity and cascade update. Do not
select cascade delete unless you know what you are doing.

Use a form for the customer and a subform in Datasheet view for gravesite
and set Master/Child links using customer_id.

Now you can add to either.

If a customer can have multiple gravesites and a gravesite can have
multiple customers then you would need a junction table the has a one-to-many
ewlationship from both of the other tables.

 

[datamerc]

Thanks Karl,

You're right that customers may have more than one grave and I think
that will get me going in the right direction, except I need to keep
everything in a single datasheet view.

The gravesites table contains all available graves in the graveyard and
once a gravesite has been filled, a customer_id is entered. Plus the
datasheet view allows sorting and exporting specific data much easier.
So this view combines the two tables and shows all graves (with or
without a customer) and if a grave doesn't have a customer in it, all
the customer info (name, address, etc) can be added from this view and
a customer_id automatically created (since this is an auto-number
field). Do you think this is possible?

Thanks again.

 

[John Vinson]

You're right that customers may have more than one grave and I think
that will get me going in the right direction, except I need to keep
everything in a single datasheet view.

The gravesites table contains all available graves in the graveyard and
once a gravesite has been filled, a customer_id is entered. Plus the
datasheet view allows sorting and exporting specific data much easier.
So this view combines the two tables and shows all graves (with or
without a customer) and if a grave doesn't have a customer in it, all
the customer info (name, address, etc) can be added from this view and
a customer_id automatically created (since this is an auto-number
field). Do you think this is possible?

Don't confuse data STORAGE with data PRESENTATION. They are different
tasks with different requirements!

If you need to export from a table or combination of tables (using a
query), it is *not* necessary - in fact it's a bad idea - to
denormalize the data.


John W. Vinson[MVP]