Query to compare two tables, and delete records only IF NOT found in other table.

[vivah]

Help please,


Pls. see the attached image first.


in History table, I have students and their results.
in current table, I have some of those students (in red) and other students as well.
I want to do a select query 1st (just to see and confirm and delete).
how to write a query where it should delete students & their results all from HISTORY TABLE ONLY , after comparing, searching in current table and delete students only if not found in current table.
Am I making sense??


example. ( in the attached image, there are 2 red color students are in both history & current table they should not be deleted, I want to delete other students in history table ).
this means, those red color students are still in school and actively progressing in other programs (XYZ table) and they should be able to see their history results from history table (ABC).


Thank you in advance,
Vivah

[June7]

Review http://www.techonthenet.com/sql/delete.php

Why have two tables? Why delete records? Use queries to filter records.

[vivah]

Review http://www.techonthenet.com/sql/delete.php

Why have two tables? Why delete records? Use queries to filter records.

Ok, how to filter and find out the ones to be deleted?
also two tables is not a choice, its a must.

[June7]

The last example in the referenced link is the model you need, although I believe you only need one pair of fields (student IDs) in the WHERE clause.

Really want to delete records? What if student comes back?

Deleting is usually a bad idea. Filtering is means to restrict viewing of records.

[vivah]

The last example in the referenced link is the model you need, although I believe you only need one pair of fields (student IDs) in the WHERE clause.

Really want to delete records? What if student comes back?

Deleting is usually a bad idea. Filtering is means to restrict viewing of records.

Hi, Thank you first,
I think I need to explain more of this,
HISTORY TABLE reflect school years from 1999-2012, where as CURRENT TABLE has only 2012-2013 school year. from HISTORY Table , as a part of clean up, we have to remove any entries older than year 2007. ( all these are archived separately )
why we need to delete or filter or hide is because, we dont want student history showing more than 5 years.
if I get a way to hide or filter these records, so that it does not show in the web application, that would be nice too.
any idea??

[June7]

A query could be set up to show only the history records that have matching current record. I assume the data shown on web could be a filtered query and not just table.

[vivah]

A query could be set up to show only the history records that have matching current record. I assume the data shown on web could be a filtered query and not just table.

Hi, how to write that query or SQL statment? pls. tell me.

VJ

[June7]

Use the query designer. Pull both tables into the query and INNER join on the StudentID fields. Only records where StudentID is in both tables will display.

[vivah]

exactly after that ?..I am stuck with that., I need to see other students present in history table only (means they should not be in current table)

[June7]

I thought you now wanted to know how query can show only students from history that have record in current?

Or are you still trying to delete records?

If you want to delete you have guidance for that.

Filtering is easier and safer.

[vivah]

I thought you now wanted to know how query can show only students from history that have record in current?

Or are you still trying to delete records?

If you want to delete you have guidance for that.

Filtering is easier and safer.

you got it, I know how to delete, but want to filter and see the records before i delete., how?
how do i do this in design query?
I can filter and see the records in current in history, how do i see the other records? do u understand?

thanks for helping,
vijay

[June7]

To see records in history that do not match current, use the Access query wizard to build a Find Unmatched query.

[vivah]

To see records in history that do not match current, use the Access query wizard to build a Find Unmatched query.

THANK YOU sir, that's it. SOLVED