updating a join table

here is my dilema

i have a database with four main tables

properties
property locations
tenants
leases

properties are linked directly to locations - no issue there

i have a join table with just the primary keys for linking locations to
tenants to leases

properties never change, locations can change depending on how they are
leased, tenants change all the time and leases change with them.

so when we get a new tenant they have to be created, then the lease is
created then they have to be linked to an existing location (usually) or a
new location has to be created and linked to an exising property

so i have a form for new properties with a sub form for locations - all
good, no problems. i have a form for new tenants and a form for new leases

essentially you have to create them in order, property then location then
tenant then lease

what my problem is is that when i create a new tenant and i then create a
new lease i need a way to automatically update the join table - at present i
take note of the primary key number for each of them and i go an manually
update the table, thats fine for me but i'm pretty nervouse about letting the
17yo junior take over the database updating - i need a foolproof method to
update that join table

i have a pretty indepth form that brings all the data together beautifully
but it doesnt lend itself to entering new records and thats my big mistake

does this all sound too complicated? i've been trying to wrap my mind around
it for 2 days!!
--
deb

Deb -

It sounds like a lease is always for only one tenant and one location.
Would a lease ever be in the join table more than once? If not, then the
fields in the join table (tenant and location) should be moved to the lease
table, as the lease is what joins a tenant to the location. Then you only
need to add drop-downs on your lease form to populate the location and
tenant.

If you can't fix your table structure, you can still update your form to
include the two drop-down lists so the proper tenant and location can be
chosen for this lease. Then when you save your lease, you can append a new
record into your join table using the key values for the lease and the chosen
locations and tenants.

--
Daryl S


"deb" wrote:

here is my dilema

i have a database with four main tables

properties
property locations
tenants
leases

properties are linked directly to locations - no issue there

i have a join table with just the primary keys for linking locations to
tenants to leases

properties never change, locations can change depending on how they are
leased, tenants change all the time and leases change with them.

so when we get a new tenant they have to be created, then the lease is
created then they have to be linked to an existing location (usually) or a
new location has to be created and linked to an exising property

so i have a form for new properties with a sub form for locations - all
good, no problems. i have a form for new tenants and a form for new leases

essentially you have to create them in order, property then location then
tenant then lease

what my problem is is that when i create a new tenant and i then create a
new lease i need a way to automatically update the join table - at present i
take note of the primary key number for each of them and i go an manually
update the table, thats fine for me but i'm pretty nervouse about letting the
17yo junior take over the database updating - i need a foolproof method to
update that join table

i have a pretty indepth form that brings all the data together beautifully
but it doesnt lend itself to entering new records and thats my big mistake

does this all sound too complicated? i've been trying to wrap my mind around
it for 2 days!!
--
deb

the problem is that one lease can have several locations - they may lease 4
floors of a building for instance, so i cant do it that way

i've kinda fixed it accidently - i ran the form wizard and it all just
dropped into place! I only had to tweek a little. just goes to show, if you
get the structure right in the first place the rest just happens

thanks for the help
--
deb


"Daryl S" wrote:

Deb -

It sounds like a lease is always for only one tenant and one location.
Would a lease ever be in the join table more than once? If not, then the
fields in the join table (tenant and location) should be moved to the lease
table, as the lease is what joins a tenant to the location. Then you only
need to add drop-downs on your lease form to populate the location and
tenant.

If you can't fix your table structure, you can still update your form to
include the two drop-down lists so the proper tenant and location can be
chosen for this lease. Then when you save your lease, you can append a new
record into your join table using the key values for the lease and the chosen
locations and tenants.

--
Daryl S


"deb" wrote:

here is my dilema

i have a database with four main tables

properties
property locations
tenants
leases

properties are linked directly to locations - no issue there

i have a join table with just the primary keys for linking locations to
tenants to leases

properties never change, locations can change depending on how they are
leased, tenants change all the time and leases change with them.

so when we get a new tenant they have to be created, then the lease is
created then they have to be linked to an existing location (usually) or a
new location has to be created and linked to an exising property

so i have a form for new properties with a sub form for locations - all
good, no problems. i have a form for new tenants and a form for new leases

essentially you have to create them in order, property then location then
tenant then lease

what my problem is is that when i create a new tenant and i then create a
new lease i need a way to automatically update the join table - at present i
take note of the primary key number for each of them and i go an manually
update the table, thats fine for me but i'm pretty nervouse about letting the
17yo junior take over the database updating - i need a foolproof method to
update that join table

i have a pretty indepth form that brings all the data together beautifully
but it doesnt lend itself to entering new records and thats my big mistake

does this all sound too complicated? i've been trying to wrap my mind around
it for 2 days!!
--
deb

Great! Glad it is working.
--
Daryl S


"deb" wrote:

the problem is that one lease can have several locations - they may lease 4
floors of a building for instance, so i cant do it that way

i've kinda fixed it accidently - i ran the form wizard and it all just
dropped into place! I only had to tweek a little. just goes to show, if you
get the structure right in the first place the rest just happens

thanks for the help
--
deb


"Daryl S" wrote:

Deb -

It sounds like a lease is always for only one tenant and one location.
Would a lease ever be in the join table more than once? If not, then the
fields in the join table (tenant and location) should be moved to the lease
table, as the lease is what joins a tenant to the location. Then you only
need to add drop-downs on your lease form to populate the location and
tenant.

If you can't fix your table structure, you can still update your form to
include the two drop-down lists so the proper tenant and location can be
chosen for this lease. Then when you save your lease, you can append a new
record into your join table using the key values for the lease and the chosen
locations and tenants.

--
Daryl S


"deb" wrote:

here is my dilema

i have a database with four main tables

properties
property locations
tenants
leases

properties are linked directly to locations - no issue there

i have a join table with just the primary keys for linking locations to
tenants to leases

properties never change, locations can change depending on how they are
leased, tenants change all the time and leases change with them.

so when we get a new tenant they have to be created, then the lease is
created then they have to be linked to an existing location (usually) or a
new location has to be created and linked to an exising property

so i have a form for new properties with a sub form for locations - all
good, no problems. i have a form for new tenants and a form for new leases

essentially you have to create them in order, property then location then
tenant then lease

what my problem is is that when i create a new tenant and i then create a
new lease i need a way to automatically update the join table - at present i
take note of the primary key number for each of them and i go an manually
update the table, thats fine for me but i'm pretty nervouse about letting the
17yo junior take over the database updating - i need a foolproof method to
update that join table

i have a pretty indepth form that brings all the data together beautifully
but it doesnt lend itself to entering new records and thats my big mistake

does this all sound too complicated? i've been trying to wrap my mind around
it for 2 days!!
--
deb