Re: deleting data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Okay the solution will be to build a relational design.

I'm not clear what Special Customers are, what they have in common, why they are different to other customers, or why they are in a different table. It seems to me that it would be better to have a single table of customers, with a check box (yes/no field) to indicate if this is a special customer or not. This solves all the problems:
- It avoids the need to copy data from one table to another.
- You no longer have duplicated data (2 places where you must update the same information.)
- The CustomerID foreign key in your orders table relates to one field in one table, and you can use referential integrity.

If there are lots of other fields to keep for special customers that you don't keep for ordinary customers, you might need to create another table for those, linked one-to-one to your Customer table. SpecialCustomers is the *related* table here (i.e. you can have a customer who is not a special customer, but you can't have a special customer who is not a customer.) I doubt you really need this, but I can't be sure from here.

Once the right relational structure is in place, the problem of bad data disappears, along with the need to delete duplicated data across tables leaving some in place while the other is not.

Hope that's clear enough. As always, you have more in mind than is explained in a newsgroup posting, but I hope the principle is clear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Roy Goldhammer" <roy@xxxxxxxxxxx> wrote in message
news:%23Nb3F%23VaHHA.4948@xxxxxxxxxxxxxxxxxxxxxxx
Whell allen.

I don't have any relationships in my database. The only problem is in the query

Here is example of what i'm talking about.

I have customers table and order table like northwind.

I also have another table of Special customers i keep for diffrent purpose

I have automatic procedure that lodes all the data from customers table to SpecialCustomer table. and on the form i present it i can change data on SpecialCustomer table.

To make the form be fast i join to SpecialCustomerTable the customers and orders table.

The problem happen when the user deleted in the form one of the records in the query boud the the form that should be deleted from the SpecialCustomers table.

but the record from the Customers table and orders table has been deleted as whell. And this is what i need to be blocked. Can you help me on it?
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message news:OZET3oVaHHA.208@xxxxxxxxxxxxxxxxxxxxxxx
No. That would violate the integrity of the database.

For example, Northwind has:
- an Orders table (the header record for the customer order),
- an OrderDetails table (the line items of the orders.)
The OrderID number is the common field.

If you deleted OrderID 99 from the Orders table (the master table), but left all the line items for order 99 in OrderDetails (the related table), you would now have orphan records that don't belong to any order. You would not know whose order this was, when it was ordered, where it was to be shipped etc. All you would have is meaningless, orphaned records, for an invalid order number.

If you do have a scenario where you need to keep the child records when the parent is deleted, you should at least set the foreign key field to Null so they do not relate to an invalid number. Programmatically, you can set up the relationship as cascade-to-null, but I can't promise the inner join query will work as you expect.

For details on how to set up such a relationship, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html

"Roy Goldhammer" <roy@xxxxxxxxxxx> wrote in message
news:OV4ONlTaHHA.4396@xxxxxxxxxxxxxxxxxxxxxxx
Hello there

I've build query in access that bound for more then one table.

If i delete record from the query. it it not only delete the master query but all the related tables.

This thing is catasfrofy. is there a way to keep the joins in the query and on the detete action to delete only the master table and not the other related tables?

.

Relevant Pages

  • Re: Scripting printing
    ... Create a checkbox-field for your special customers. ... this is possible using scripting in FMPro Advanced 8 or not: ... For some customers I have to make a different printout on invoices. ...
    (comp.databases.filemaker)
  • Re: Scripting printing
    ... Create a checkbox-field for your special customers. ... layout when a customer is special and to another when he/she is normal. ... search, where you find all normal customers, print them then you find all ...
    (comp.databases.filemaker)
  • Re: Re: Need some input on an Event Procedure for a search
    ... could try it with just needed a slight alteration to the SQL query. ... specific Features which some Customers would be looking for. ... The Customers table includes contact details, ... and the specific command for a mailout which is the event ...
    (microsoft.public.access.gettingstarted)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • RE: Store Added Value List Items
    ... You could even union that query with the actual list from the form ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)