A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

delete records from one table if not in another



 
 
Thread Tools Display Modes
  #11  
Old December 22nd, 2008, 02:48 PM posted to microsoft.public.access.gettingstarted
mcnews
external usenet poster
 
Posts: 231
Default delete records from one table if not in another

On Dec 21, 5:32*pm, "Steve" wrote:
1. * *When you go to New - Unmatched Query to start the wizard, do you get a
listbox with all your tables?

2. * *You need to select the Backup table first and then the original table.
Then do you get two listboxes where the left listbox shows the fields in the
backup table and the right list box shows the fields in the original table?

Steve

"mcnewsxp" wrote in message

...



"Steve" wrote in message
om...
Are you talking about the wizard and the two listboxes for selecting
fields to match?


yes


"mcnewsxp" wrote in message
...


"Steve" wrote in message
news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink .com...
If you make a backup of the original table and then import new records
into the backup table, the new records will not be in the original
table. Your unmatched query should look at the backup table to find
records that are not in the original table. If you do not return any
records, are you specifying an appropriate field in the unmatched
query?


I am a bit puzzeled by your response "....no fields show up in the
list...." By "fields" do you mean records or do you truly mean fields?
Please clarify.


when i go to create the unmatched query after selecting the two tables -
on the form where you select the fields to match theres nothing in the
lists...?


"mcnewsxp" wrote in message
.. .


"Steve" wrote in message
...
Try creating an unmatched query and changing it to a delete query.
There's a wixard for creating an unmatched query by going to queries
in the database window and clicking new.


good idea.
except when i do this no fields show up in the list....???


"mcnewsxp" wrote in message
.. .
how can i delete records from one table if the records do not exist
in a matching table? *i am making a backup of a table before an
import is performed. *the users may need to undo the backup so i
need to be able to delete only the new records. this is not working,
but you get the idea:


DELETE *tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON
tblPatient.PatientID = tblPatientBackup.PatientID
WHERE tblPatient.PatientID tblPatientBackup.PatientID;


tia,
mcnewxp


i tried it on my work PC and it works fine.....
  #12  
Old December 22nd, 2008, 02:51 PM posted to microsoft.public.access.gettingstarted
mcnews
external usenet poster
 
Posts: 231
Default delete records from one table if not in another

On Dec 18, 1:14*pm, John Spencer wrote:
Well, if you have imported then into tblPatient then they are in the table.
So an unmatched query is not going to show you anything.

STEP 1: *BACKUP your data before attempting the following.
STEP 2: *BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect. *Here are four options - choose your favorite

Option 1:
DELETE
FROM TblPatient
WHERE tblPatient.PatientID in
(SELECT PatientID FROM ImportSourceTable)

Option 2: Optionally if you want to use the backup table instead of the import
table.
DELETE
FROM tblPatient
WHERE tblPatient.PatientID IN
* *(SELECT Temp.PatientID
* * FROM tblPatient as TEMP LEFT JOIN tblPatient_BU
* * ON Temp.PatientID = tblPatient_BU.PatientID
* * WHERE TblPatient_BU.PatientID is NULL)

Option 3:
On the other hand, you might just be able to replace TblPatient with
TblPatient_BU. Unless other changes have been made to the records you want to
retain, this would seem to be the easiest solution.

Option 4:
Another option would be to add a date field (DateCreated) to tblPatient and
set its default to NOW(). *Then all you have to do is identify records that
were created at a certain date and time and delete them.

DELETE
FROM tblPatient
WHERE DateCreated Between #2008-12-18 13:59:00# and #2008-12-18 14:00:00#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

mcnewsxp wrote:
"Steve" wrote in message
...
Try creating an unmatched query and changing it to a delete query. There's
a wixard for creating an unmatched query by going to queries in the
database window and clicking new.


good idea.
except when i do this no fields show up in the list....???


"mcnewsxp" wrote in message
...
how can i delete records from one table if the records do not exist in a
matching table? *i am making a backup of a table before an import is
performed. *the users may need to undo the backup so i need to be able to
delete only the new records. this is not working, but you get the idea:


DELETE *tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON
tblPatient.PatientID = tblPatientBackup.PatientID
WHERE tblPatient.PatientID tblPatientBackup.PatientID;


tia,
mcnewxp


your 3rd option is one that i tried, but i couldn't delete the
tblPatient records because of foreignkey violations.
  #13  
Old December 22nd, 2008, 03:38 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default delete records from one table if not in another

Well, if the data in tblPatient is linked to other tables then you will have to
go into the relationships window
, delete the relationship links involving tblPatient
, save the changes
, then replace (or rename) tblPatient with tblPatient_BU. (I suggest you
rename tblPatient to tblPatient_Original and tblPatient_BU to tblPatient)
, and then re-establish the relationships.

Test things and if all seems to be working correctly, you can then rename
tblPatient_Original to tblPatient_BU or delete tblPatient_Original.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

mcnews wrote:
On Dec 18, 1:14 pm, John Spencer wrote:
Well, if you have imported then into tblPatient then they are in the table.
So an unmatched query is not going to show you anything.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect. Here are four options - choose your favorite

Option 1:
DELETE
FROM TblPatient
WHERE tblPatient.PatientID in
(SELECT PatientID FROM ImportSourceTable)

Option 2: Optionally if you want to use the backup table instead of the import
table.
DELETE
FROM tblPatient
WHERE tblPatient.PatientID IN
(SELECT Temp.PatientID
FROM tblPatient as TEMP LEFT JOIN tblPatient_BU
ON Temp.PatientID = tblPatient_BU.PatientID
WHERE TblPatient_BU.PatientID is NULL)

Option 3:
On the other hand, you might just be able to replace TblPatient with
TblPatient_BU. Unless other changes have been made to the records you want to
retain, this would seem to be the easiest solution.

Option 4:
Another option would be to add a date field (DateCreated) to tblPatient and
set its default to NOW(). Then all you have to do is identify records that
were created at a certain date and time and delete them.

DELETE
FROM tblPatient
WHERE DateCreated Between #2008-12-18 13:59:00# and #2008-12-18 14:00:00#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

mcnewsxp wrote:
"Steve" wrote in message
...
Try creating an unmatched query and changing it to a delete query. There's
a wixard for creating an unmatched query by going to queries in the
database window and clicking new.
good idea.
except when i do this no fields show up in the list....???
"mcnewsxp" wrote in message
...
how can i delete records from one table if the records do not exist in a
matching table? i am making a backup of a table before an import is
performed. the users may need to undo the backup so i need to be able to
delete only the new records. this is not working, but you get the idea:
DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON
tblPatient.PatientID = tblPatientBackup.PatientID
WHERE tblPatient.PatientID tblPatientBackup.PatientID;
tia,
mcnewxp


your 3rd option is one that i tried, but i couldn't delete the
tblPatient records because of foreignkey violations.

  #14  
Old December 22nd, 2008, 04:11 PM posted to microsoft.public.access.gettingstarted
Steve[_57_]
external usenet poster
 
Posts: 598
Default delete records from one table if not in another

Is your problem solved then?

Steve


"mcnews" wrote in message
...
On Dec 21, 5:32 pm, "Steve" wrote:
1. When you go to New - Unmatched Query to start the wizard, do you get a
listbox with all your tables?

2. You need to select the Backup table first and then the original table.
Then do you get two listboxes where the left listbox shows the fields in
the
backup table and the right list box shows the fields in the original
table?

Steve

"mcnewsxp" wrote in message

...



"Steve" wrote in message
om...
Are you talking about the wizard and the two listboxes for selecting
fields to match?


yes


"mcnewsxp" wrote in message
...


"Steve" wrote in message
news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink .com...
If you make a backup of the original table and then import new
records
into the backup table, the new records will not be in the original
table. Your unmatched query should look at the backup table to find
records that are not in the original table. If you do not return any
records, are you specifying an appropriate field in the unmatched
query?


I am a bit puzzeled by your response "....no fields show up in the
list...." By "fields" do you mean records or do you truly mean
fields?
Please clarify.


when i go to create the unmatched query after selecting the two
tables -
on the form where you select the fields to match theres nothing in the
lists...?


"mcnewsxp" wrote in message
.. .


"Steve" wrote in message
...
Try creating an unmatched query and changing it to a delete query.
There's a wixard for creating an unmatched query by going to
queries
in the database window and clicking new.


good idea.
except when i do this no fields show up in the list....???


"mcnewsxp" wrote in message
.. .
how can i delete records from one table if the records do not
exist
in a matching table? i am making a backup of a table before an
import is performed. the users may need to undo the backup so i
need to be able to delete only the new records. this is not
working,
but you get the idea:


DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON
tblPatient.PatientID = tblPatientBackup.PatientID
WHERE tblPatient.PatientID tblPatientBackup.PatientID;


tia,
mcnewxp


i tried it on my work PC and it works fine.....


  #15  
Old December 22nd, 2008, 05:58 PM posted to microsoft.public.access.gettingstarted
mcnews
external usenet poster
 
Posts: 231
Default delete records from one table if not in another

On Dec 22, 10:11*am, "Steve" wrote:
Is your problem solved then?

Steve

"mcnews" wrote in message

...
On Dec 21, 5:32 pm, "Steve" wrote:



1. When you go to New - Unmatched Query to start the wizard, do you get a
listbox with all your tables?


2. You need to select the Backup table first and then the original table.
Then do you get two listboxes where the left listbox shows the fields in
the
backup table and the right list box shows the fields in the original
table?


Steve


"mcnewsxp" wrote in message


...


"Steve" wrote in message
om...
Are you talking about the wizard and the two listboxes for selecting
fields to match?


yes


"mcnewsxp" wrote in message
...


"Steve" wrote in message
news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink .com...
If you make a backup of the original table and then import new
records
into the backup table, the new records will not be in the original
table. Your unmatched query should look at the backup table to find
records that are not in the original table. If you do not return any
records, are you specifying an appropriate field in the unmatched
query?


I am a bit puzzeled by your response "....no fields show up in the
list...." By "fields" do you mean records or do you truly mean
fields?
Please clarify.


when i go to create the unmatched query after selecting the two
tables -
on the form where you select the fields to match theres nothing in the
lists...?


"mcnewsxp" wrote in message
.. .


"Steve" wrote in message
...
Try creating an unmatched query and changing it to a delete query.
There's a wixard for creating an unmatched query by going to
queries
in the database window and clicking new.


good idea.
except when i do this no fields show up in the list....???


"mcnewsxp" wrote in message
.. .
how can i delete records from one table if the records do not
exist
in a matching table? i am making a backup of a table before an
import is performed. the users may need to undo the backup so i
need to be able to delete only the new records. this is not
working,
but you get the idea:


DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON
tblPatient.PatientID = tblPatientBackup.PatientID
WHERE tblPatient.PatientID tblPatientBackup.PatientID;


tia,
mcnewxp


i tried it on my work PC and it works fine.....


i'll have to try it again at home this evening, but i can move on with
my work PC.
thanks.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.