If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Join/delete problem
I have a join problem I can't figure out. I have two back-end databases
joined on their control numbers through a front-end query (db2 on the left, db1 on the right). No other relationships exist between these tables. The control number for db1 is a primary key; the control number for db2 is not. Db2 is a very small subset of db1, and the form used to fill in db2 displays some relevant info from db1. Users have to manually enter db2 control numbers; there's no error checking in place, but records should include only numbers that are already in db1. The db2 control number is currently set to index/duplicates okay, because it was originally anticipated that there would be duplicate records in db2 for each record in db1. Now I've been asked to prevent duplicates. I thought I could simply set the db2 control number to index/no duplicates. But when I do that, deleting a record from db2 also deletes the record from db1 that has the same control number, which I don't want it to do. Is it the query relationship that's causing this? I think I'm missing something really obvious. |
#2
|
|||
|
|||
On Tue, 30 Nov 2004 14:23:02 -0800, "Carol Giannini"
wrote: But when I do that, deleting a record from db2 also deletes the record from db1 that has the same control number, which I don't want it to do. Is it the query relationship that's causing this? Check the relationship in the Relationships window: click the line and view its properties. If the "Cascade Deletes" checkbox is checked, uncheck it. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#3
|
|||
|
|||
John - thanks, but this is why I'm confused. There are no properties in the
relationships windows. These tables are joined *only* through a query, and referential integrity isn't a choice there. "John Vinson" wrote: On Tue, 30 Nov 2004 14:23:02 -0800, "Carol Giannini" wrote: But when I do that, deleting a record from db2 also deletes the record from db1 that has the same control number, which I don't want it to do. Is it the query relationship that's causing this? Check the relationship in the Relationships window: click the line and view its properties. If the "Cascade Deletes" checkbox is checked, uncheck it. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#4
|
|||
|
|||
On Wed, 1 Dec 2004 11:33:02 -0800, "Carol Giannini"
wrote: John - thanks, but this is why I'm confused. There are no properties in the relationships windows. These tables are joined *only* through a query, and referential integrity isn't a choice there. Are you sure the record is in fact being deleted from both tables? If it exists only in one of the two tables (either one), it will NOT show up in a Query joining the two tables by the default Inner Join. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#5
|
|||
|
|||
Yes. I've manually checked both tables, and when the delete is done in the
first, the corresponding record is also deleted from the second. "John Vinson" wrote: On Wed, 1 Dec 2004 11:33:02 -0800, "Carol Giannini" wrote: John - thanks, but this is why I'm confused. There are no properties in the relationships windows. These tables are joined *only* through a query, and referential integrity isn't a choice there. Are you sure the record is in fact being deleted from both tables? If it exists only in one of the two tables (either one), it will NOT show up in a Query joining the two tables by the default Inner Join. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#6
|
|||
|
|||
On Tue, 30 Nov 2004 14:23:02 -0800, "Carol Giannini"
wrote: But when I do that, deleting a record from db2 also deletes the record from db1 that has the same control number, which I don't want it to do. Is it the query relationship that's causing this? I think I'm missing something really obvious. If the FOrm is based on a query with a one-to-one relationship, it may be deleting both records. Is that the case? It might be safer to have a Form with a Subform, or to do the deletion in code. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#7
|
|||
|
|||
It is a query with a one-to-one. This is the answer. Thanks *very* much.
"John Vinson" wrote: It might be safer to .. do the deletion in code. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with Normal.dot or formatting??? | nedkelly2 | General Discussion | 11 | July 2nd, 2007 07:02 PM |
Reinstalling OE... | KAR | Outlook Express | 24 | August 21st, 2004 06:52 PM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
Fromatting Problem w/ Document Created in Works | Inky | Formatting Long Documents | 2 | May 4th, 2004 04:22 AM |
Word 2000 footnote problem: footnotes consistently too high (again) | Lori | Formatting Long Documents | 2 | May 1st, 2004 07:15 PM |