Help with deleting records in a database

[DougM]

Hi, I am helping a friend reconfigure his database, and we would like to setup a way to delete records from the database once they are no longer needed. The database is currently setup with 3 tables (at least for this part).

Table 1 Items Sold
Contains fields
Autonumber (primary key)
Consignor number (linked with relationships to Consignor Information table and Volunteer table)
Item#
Description
PriceSold
DateSold
Volunteer?

Table 2 Consignor Information
Contains fields
Consignor # (primary key) (linked with relationships to Items Sold and Volunteer table)
Consignor First name
Consignor Last name

Table 3 Volunteer
Contains fields
Autonumber (primary key)
Consignor # (linked to Items sold and Consignor Information)

The 2 Relationships joining these tables are:

Relationship 1
Type: Indeterminate
From Volunteer Table To ItemsSold
Using the Consignor Number for the link

Relationship 2
Type: 1-to-Many
From Consignor Information table To ItemsSold table
Using Consigner Number for the link

I was trying to set it for Cascade delete (so that in a form only showing Consignor Information (from the Consignor Information table) you could delete ALL information related to that consigor. However when I try this I get 2 different errors, depending on the tables related:

Error 1 When trying to set Enforce Referential Integrity (cascade delete) for Relationship 1:
No unique index found for the referenced field of the primary table.

Error 2 When trying the same thing for Relationship 2:
Data in the table "ItemsSold" violates referential integrity Rules. For example, there maybe records to an employee in the related table, but no record for the employee in the primary table. Edit the database so that records in the primary table exist for all related records.

Can anyone help with this? Is there an easier way to do it? such as maybe a form where you can select the record and delete it?

Anyhelp would be great!!!


Related to this is another question dealing with the Volunteer Table.
for bookkeeping he has a form that steps you thru the process, on step 3 he has steps 3a, 3b, 3c.

3A: Open form (Volunteer Data Entry form) you enter in the consignors who are volunteering

3B: you have to open up a Query (Check Volunteer Marked Y) and change all of the Ns to Ys, which gets to be a VERY long process. Is there anyway to make them all Ys to begin with, without setting ALL people to Y, or to set it so that when you change the 1st N to a Y, it changes all of the Ns into Ys?

3C: just printing out the report.

Thanks again for any help you can give me on this

 

[Pat Hartman]

Error 1 - do what it says - define a primary key. ALL tables should have unique primary keys.
Error 2 - do what it says - fix the data. There are records in the ItemsSold table that have no parent record in the Consignor table. Either correct the ConsignorID or delete the orphan records. Use the wizard to build an unmatched query if you need help finding the orphans.

A word on naming standards. The recommended standard prohibits the use of embedded spaces or special characters such as # and ?. Although Access allows them, you will have trouble if you ever need to use VBA.

Also,
1. Use more descriptive names for your primary keys. Autonumber doesn't cut it. ItemsSoldID and VolunteerID would be better choices.
2. Use the same name for both the primary and foreign keys. It will make it easier to figure out how tables are related without having to pull up the relationship diagram.

 

[DougM]

Need some more help

Error 1 - do what it says - define a primary key. ALL tables should have unique primary keys.
Error 2 - do what it says - fix the data. There are records in the ItemsSold table that have no parent record in the Consignor table. Either correct the ConsignorID or delete the orphan records. Use the wizard to build an unmatched query if you need help finding the orphans.

A word on naming standards. The recommended standard prohibits the use of embedded spaces or special characters such as # and ?. Although Access allows them, you will have trouble if you ever need to use VBA.

Also,
1. Use more descriptive names for your primary keys. Autonumber doesn't cut it. ItemsSoldID and VolunteerID would be better choices.
2. Use the same name for both the primary and foreign keys. It will make it easier to figure out how tables are related without having to pull up the relationship diagram.

For error 1: The table has 2 fields ( I did not name them, just trying to fix the problem) the fields are:

Autonumber
Volunteer

the error I get says: there is no unique index for the referenced field of the primary table.

Since auto number is set as the primary key, is this what is causing the problem? how can I fix this? just make the Volunteer the primary key? I am really not sure what to do.

For error 2: I just cleaned out both tables, and then set it for the enforce integrity, cascade delet. one question, with this setup, if you delete a person from the primary table, will it delete all of the fields related to that person in the other table? or just the linked field?

 

[Pat Hartman]

In order for you to establish RI on the relationship between the Consignor table and the Volunteer table, the Consignor # field must be defined as the primary key of the Consignor table. There is NO relationship between the Items Sold table and the Volunteer table if that is what you are trying to do. Each of these tables has a many-to-1 relationship with Consignor but have nothing in common that would allow you to relate them to each other. Don't be confused by the fact that both tables contain Consignor as a foreign key. They are really not logically related.

The Consignor table seems to be the "parent" of two tables - Items Sold and Volunteers. When you establish RI for each of the two relationships (Consignor --> Items Sold and Consignor --> Volunteers) and select cascade delete, deleting a row from Consignor will automatically delete ALL related rows from BOTH Items Sold and Volunteers so be sure that this is the desired behaviour. If you do NOT choose cascade delete, you will be prevented from deleting any Consignor record for which there is a related record in EITHER Items Sold OR Volunteers. However, you can delete rows unhampered from Items Sold and Volunteers. Once all the related records are deleted from Items Sold and Volunteers, you will be able to delete the Consignor.