Question about Cascade in Relationships

[Joe Cilinceon]

I have several tables in a relationship and was wondering if you do a
Cascade Delete how it would work.

The table layout is like below:

TENANT.CustNo (to several other tables such as Address, Contacts etc.)

LEASES.LedgerID ( links to the LEDGER )
LEASES.CustNo ( links to the TENANT.CustNo )
LEASES.UnitNo ( links to the UNIT.UnitNo )

LEDGER.Transaction (1 record per transaction and links to both Payments and
Charges below)
PAYMENTS.Transaction (1 or more items with same transaction)
CHARGES.Transaction (same as payments)

If I want to delete a LEDGER.Transaction along with the related PAYMENT &
CHARGES but nothing else how to I setup these relationships. I played with
it a bit but also took out the Tenant and Lease info as well which I don't
want.

 

[Joe Cilinceon]

One other thing I noticed when setting up the Relationships. Charges and
Payments are same as to the Transaction, Number, Long and Indexed to allow
for more than one. Where LEDGER is one record and the Transaction number is
AutoNum and all require a transaction number at least 1. I can set the
switches with the Charges but not the Payments. What is that about?

 

[Vincent Johns]

I have several tables in a relationship and was wondering if you do a
Cascade Delete how it would work.

The table layout is like below:

TENANT.CustNo (to several other tables such as Address, Contacts etc.)

LEASES.LedgerID ( links to the LEDGER )
LEASES.CustNo ( links to the TENANT.CustNo )
LEASES.UnitNo ( links to the UNIT.UnitNo )

LEDGER.Transaction (1 record per transaction and links to both Payments and
Charges below)
PAYMENTS.Transaction (1 or more items with same transaction)
CHARGES.Transaction (same as payments)

If I want to delete a LEDGER.Transaction along with the related PAYMENT &
CHARGES but nothing else how to I setup these relationships. I played with
it a bit but also took out the Tenant and Lease info as well which I don't
want.

What it's designed to do is to obliterate any records depending on a
primary record that you delete. For example, if you have a mailing list
for the Garden Club and delete the name of a member who moves away, you
might want Access to also delete the address record for that member --
the address does you little good if there's nobody there who cares about
gardening.

I'm not sure it's a great idea to CASCADE DELETE records of financial
transactions. Yes, you have backups of your database file, but there
might be times when you'd want to look at details of a former account
without having to leave your current database. What would you do if
someone asked you for a credit reference for one of last year's
customers? It may not be a great idea even to delete a [LEDGER] record
without deleting other records; maybe you would prefer to add a later
record that reverses its effect (similarly to redepositing a check that
you wrote but never used -- you might still want to account for the
check number by keeping a record in your Table of checks).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Joe Cilinceon]

Here is an example of the situation that I was looking at. Say a person
comes in to pay and we post it to the wrong unit. This does happen from time
to time. We always ask, when a person walks in to pay and just give us a
space number, if the name is correct when we pull up the account. You would
be surprise how many say yes then look at the receipt to say hay this isn't
me. Now I have to repost the payment to the correct account and dump that
whole incorrect transaction. I was just asking how safe it is as I wouldn't
want to delete all the tenant's records just the Ledger.Transaction,
Payment.Transaction and Charges.Transaction for that single transaction. I
working on coding a reversal method just for this situation.

Thanks again Vincent I complete understood your answer and will give it some
thought.

--

Joe Cilinceon

I have several tables in a relationship and was wondering if you do a
Cascade Delete how it would work.

The table layout is like below:

TENANT.CustNo (to several other tables such as Address, Contacts
etc.) LEASES.LedgerID ( links to the LEDGER )
LEASES.CustNo ( links to the TENANT.CustNo )
LEASES.UnitNo ( links to the UNIT.UnitNo )

LEDGER.Transaction (1 record per transaction and links to both
Payments and Charges below)
PAYMENTS.Transaction (1 or more items with same transaction)
CHARGES.Transaction (same as payments)

If I want to delete a LEDGER.Transaction along with the related
PAYMENT & CHARGES but nothing else how to I setup these
relationships. I played with it a bit but also took out the Tenant
and Lease info as well which I don't want.

What it's designed to do is to obliterate any records depending on a
primary record that you delete. For example, if you have a mailing
list for the Garden Club and delete the name of a member who moves
away, you might want Access to also delete the address record for
that member -- the address does you little good if there's nobody
there who cares about gardening.

I'm not sure it's a great idea to CASCADE DELETE records of financial
transactions. Yes, you have backups of your database file, but there
might be times when you'd want to look at details of a former account
without having to leave your current database. What would you do if
someone asked you for a credit reference for one of last year's
customers? It may not be a great idea even to delete a [LEDGER]
record without deleting other records; maybe you would prefer to add
a later record that reverses its effect (similarly to redepositing a
check that you wrote but never used -- you might still want to
account for the check number by keeping a record in your Table of
checks).
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Here is an example of the situation that I was looking at. Say a person
comes in to pay and we post it to the wrong unit. This does happen from time
to time. We always ask, when a person walks in to pay and just give us a
space number, if the name is correct when we pull up the account. You would
be surprise how many say yes then look at the receipt to say hay this isn't
me. Now I have to repost the payment to the correct account and dump that
whole incorrect transaction. I was just asking how safe it is as I wouldn't
want to delete all the tenant's records just the Ledger.Transaction,
Payment.Transaction and Charges.Transaction for that single transaction. I
working on coding a reversal method just for this situation.

Thanks again Vincent I complete understood your answer and will give it some
thought.

Well, you are deleting a transaction, but it isn't the primary Table for
the tenant. Whatever depends on the transaction will also be deleted,
if you set Cascade Delete on the relationships that depend on it, but
other Tables shouldn't be affected.

Do you even have any other Tables that depend on [Ledger]? If so, think
about what it might mean to keep alive a record in such other Table for
which the [Ledger] record that once gave it legitimacy has vanished.
Orphan records like that would likely just give you headaches, I think.
(However, Access will allow you to keep them around, and you can write
a Query that will list them for your perusal.)

What you describe here sounds safer than I at first thought it was, but
if you're concerned about it, why don't you just add a yes/no field
called [To Be Deleted?] to Ledger, and use that instead of deleting the
record. You could delete mistakes on a periodic basis, perhaps, when
you're in the mood. You'd need to modify Queries depending on [Ledger]
to ignore records having [To Be Deleted?] = True.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Joe Cilinceon]

Well, you are deleting a transaction, but it isn't the primary Table
for the tenant. Whatever depends on the transaction will also be
deleted, if you set Cascade Delete on the relationships that depend
on it, but other Tables shouldn't be affected.

Oh and the Delect cascade would be from the Ledger > Payments and Ledger >
Charges Not from Leases > Ledger so if I'm understanding you correctly it
wouldn't effect any other records. Just to make sure I'm understanding this
and example would be as follows:

I'm not sure the proper wording for this but I will call the LEDGER the
master here with it having a single record with a unique # called
transaction. The Payments (can be many) and Charges (can be many) tables are
sub tables to Ledger and handle the Payment method and amount as well as the
charge type and amount for that transaction. So if I delete
LEDGER.Transaction = 4100 it will delete all the PAYMENTS.transaction = 4100
and CHARGES.transaction = 4100 only.

Do you even have any other Tables that depend on [Ledger]? If so,
think about what it might mean to keep alive a record in such other
Table for which the [Ledger] record that once gave it legitimacy has
vanished. Orphan records like that would likely just give you
headaches, I think. (However, Access will allow you to keep them
around, and you can write a Query that will list them for your
perusal.)
What you describe here sounds safer than I at first thought it was,
but if you're concerned about it, why don't you just add a yes/no
field called [To Be Deleted?] to Ledger, and use that instead of
deleting the record. You could delete mistakes on a periodic basis,
perhaps, when you're in the mood. You'd need to modify Queries
depending on [Ledger] to ignore records having [To Be Deleted?] =
True.

What you suggested would be fine as it would give us a chance to perhaps
undo a reversal. However at the end of the day when we dump them wouldn't
the query below take care of any Payments and Charges with only that
transaction #?

DELETE LEDGER.Transaction
FROM LEDGER
WHERE (((LEDGER.Transaction)=4110));

 

[Vincent Johns]

Joe Cilinceon wrote:

[...]

Oh and the Delect cascade would be from the Ledger > Payments and Ledger >
Charges Not from Leases > Ledger so if I'm understanding you correctly it
wouldn't effect any other records. Just to make sure I'm understanding this
and example would be as follows:

I'm not sure the proper wording for this but I will call the LEDGER the
master here with it having a single record with a unique # called
transaction. The Payments (can be many) and Charges (can be many) tables are
sub tables to Ledger and handle the Payment method and amount as well as the
charge type and amount for that transaction. So if I delete
LEDGER.Transaction = 4100 it will delete all the PAYMENTS.transaction = 4100
and CHARGES.transaction = 4100 only.

As long as you have no further need for the linked records in [Payments]
and [Charges], and no need for the record in [Ledger], I see no problem.
(I assume that the [Payments] record, for example, has no records in
other Tables that depend on it that you might want to preserve.) Yes,
only the records with the linking values should be affected.

What you suggested would be fine as it would give us a chance to perhaps
undo a reversal. However at the end of the day when we dump them wouldn't
the query below take care of any Payments and Charges with only that
transaction #?

DELETE LEDGER.Transaction
FROM LEDGER
WHERE (((LEDGER.Transaction)=4110));

Yes, if you can remember what the transaction number was. I think you'd
need to have some way to store it. If you're storing it, I think doing
that via a yes/no field in the affected [Ledger] record might be easier
than setting up a [Mistakes] Table, but maybe not, since you would need
to adjust your Queries to ignore the flagged records.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Joe Cilinceon]

As long as you have no further need for the linked records in
[Payments] and [Charges], and no need for the record in [Ledger], I
see no problem. (I assume that the [Payments] record, for example,
has no records in other Tables that depend on it that you might want
to preserve.) Yes, only the records with the linking values should
be affected.

Yes, if you can remember what the transaction number was. I think
you'd need to have some way to store it. If you're storing it, I
think doing that via a yes/no field in the affected [Ledger] record
might be easier than setting up a [Mistakes] Table, but maybe not,
since you would need to adjust your Queries to ignore the flagged
records.
-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Yes I'll think about this awhile before I add any means in the forms to do
it. I sure don't want my partner (wife) doing this without being very sure.
Now I'm the only one that can do it and I'm simply deleting them manually
from the tables subdatasheets in the back end bottom up.

 

[Vincent Johns]

Yes I'll think about this awhile before I add any means in the forms to do
it. I sure don't want my partner (wife) doing this without being very sure.
Now I'm the only one that can do it and I'm simply deleting them manually
from the tables subdatasheets in the back end bottom up.

That sounds like a good idea, especially if there aren't many that you
have to deal with.

I suppose I ought to mention that, when I describe how Access behaves,
that's no guarantee that it will actually behave that way. Even
Microsoft doesn't guarantee its behavior (see the EULA). So I recommend
that you continue to make lots of backups, especially when you are about
to try something that might be a bit risky! :-o

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Joe Cilinceon]

That sounds like a good idea, especially if there aren't many that you
have to deal with.

I suppose I ought to mention that, when I describe how Access behaves,
that's no guarantee that it will actually behave that way. Even
Microsoft doesn't guarantee its behavior (see the EULA). So I
recommend that you continue to make lots of backups, especially when
you are about to try something that might be a bit risky! :-o

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

LOL, I backup the backups, not to worry there.