Deleting Table Information

[Becks]

Hi, we have a database which is used to track Purchase Order Alerts. Once
the products have gone out they are put under a shipped status and no longer
needed in the database. When we go to delete the row/rows from the table it
deletes fine. When we go back into the system, the record is there, but with
only half the information. We are not the ones who created this database,
and were just wondering why this is happening and what could be done to solve
this problem. We use access 2002. Thanks for the help.

 

[Dirk Goldgar]

Hi, we have a database which is used to track Purchase Order Alerts.
Once the products have gone out they are put under a shipped status
and no longer needed in the database. When we go to delete the
row/rows from the table it deletes fine. When we go back into the
system, the record is there, but with only half the information. We
are not the ones who created this database, and were just wondering
why this is happening and what could be done to solve this problem.
We use access 2002. Thanks for the help.

It sounds like you're looking at records derived from a query that joins
two tables, in a relationship where a single record in one table relates
to many records in another table -- as for example, a PurchaseOrder
table with one record per order, related to many PurchaseOrderDetail
records in another table; or a Product record in one table related to
many ProductOrder records in another table.

When a query joins tables in a one-to-many relationship like that, you
get multiple records out, one for each record on the "many" side of the
relationship. If you delete records from the results of the query, you
only delete the "many-side", or child, records; not the "one-side", or
parent, record. On the other hand, if you go to the one-side table and
delete the parent record, one of two things might happen, depending on
how the relationship was set up in the database. If the relationship
was defined with "cascading deletes", all child records related to the
parent record will be deleted when the parent record is deleted. If
not, then deleting the parent record will leave the child records on
file as "orphans", with no parent record to provide the information that
would come from the parent table.

It's my guess that this is what is happening. Does that sound
plausible, in the context of your database?

 

[Becks]

Thanks for the help Dirk.

It does sound like that is what is going on with our database. Is there a
way for us to determine if the relationship was defined with 'cascading
deletes'? Is it as simple to tell as just going into the database and
looking in the relationships section? So I would imagine that if the
database wasn't set up this way there isn't really a way for us to delete out
the records which are no longer valid in the system. Maybe we should create
something to help filter them out at least. Thanks again for all the help.

 

[Becks]

Compacting sounds like it is the idea for us. Orr purchaser uses the program
in order to track special orders in which we have to go to another source for
the product before we can complete our product to ship. So once it has been
shipped she no longer needs any of the information. We have other programs
set up which documents things like shipping date, etc. So archiving could be
used, but wouldn't be used again due to the process here. Thanks for the
information and we are going to look into compacting the information to
delete it.

 

[Becks]

We have actually decided that archiving would be better, just in the off case
that a sales executive would need to look up the data. So I guess if you
could help me out with how to go about doing this it would be great. It
looks from a few other responses that in order to archive we have to set up a
database just to hold the archived information...not too sure on if I was
understanding things correctly. My other question is that once we archive
this information and say we printed out a yearly report of shipped purchase
orders for sales would we then be able to delete the archived information?
Sorry for any confusion and thanks for all the help.

 

[Dirk Goldgar]

Thanks for the help Dirk.

It does sound like that is what is going on with our database. Is
there a way for us to determine if the relationship was defined with
'cascading deletes'? Is it as simple to tell as just going into the
database and looking in the relationships section?

That's basically right. Open the Relationships window, make sure that
the tables in question are displayed in the diagram, and see if ...

(a) there's no join line between the tables, connecting the key fields
that are related. No join line = no relationship defined at all.

(b) if there's a join line, double-click it (be careful to double-click
right on the line, not just near it). That should display the Edit
Relationships dialog for those two tables. If the check box "Enforce
Referential Integrity" isn't checked, then the relationship is not
enforced, so cascading deletes aren't in effect.

(c) if "Enforce Referential Integrity" is checked, but "Cascade Delete
Related Records" is not, then cascading deletes are not in effect, but
the database won't let you delete a parent record that has any child
records in the related table. I don't think that can be the case here,
though.

So I would
imagine that if the database wasn't set up this way there isn't
really a way for us to delete out the records which are no longer
valid in the system.

Sure there is, but you have to set up a query to do it. First, use the
Find Unmatched Query Wizard (Insert -> Query -> Find Unmatched Query
Wizard) to create a query that shows all records in the child table (the
"many side" table) that have no match in the parent table (the "one
side" table). When you've got that query created and can see the
results -- and confirm that you want to delete them -- you can either
just select them all and delete them, or else go back into design view
for the query and change its type from Select Query to Delete Query, and
then run it.

Maybe we should create something to help filter
them out at least. Thanks again for all the help.

That would be another alternative. I can't really advise about that, as
I don't know all the details of your database.