A comparison

[Guest]

I'm just getting into access and I need help getting something set up. I
have a table called Last_Week with fields LW_Rec_Ident and LW_Rec_Detail. I
have another table called Clean_Export with fields Rec_Ident and Rec_Detail.

The Ident fields contain a code that will be the same in each table. If the
Ident fields match in both tables than the Detail fields in both tables could
also match. If they DO match I want them deleted from Clean_Export only.

 

[OfficeDev18 via AccessMonster.com]

julialatte,

You want a delete query. Read about them, including examples, in the Help
file.

Sam

 

[Guest]

Can you offer me a little bit of direction, also? I sorta need this quick.

 

[Guest]

This is my attempt but I get an error that states "Specify the table with the
records you want to delete."

Can someone help me with this:

DELETE Clean_Export
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

 

[OfficeDev18 via AccessMonster.com]

julialatte,

If you think it'll be more helpful.

Open the query designer to a new query.

In the table list, click first on the summary table (Last_Week) and then on
the detail table(Clean_Export). Close the list.

Select and drag a line from the Ident field in the summary table (thumbnail)
to the Ident field in the detail table. Do the same for the Detail fields of
the tables.This creates the INNER JOIN.

In the query design toolbar at the top of the display, find the query type
icon, and change it to Delete.

In the Clean_Export thumbnail, double-click on the star above the first field
name (the "All fields" selector). Make sure the words "Clean_Export.*" appear
below in the QBE (query by design) grid. Your design is complete.

To test your query, click on the left-most icon of the query design toolbar
(it looks like a cracker). This will display all records in the table with
both fields matching. This tells you which records will be deleted when you
run the query for real. To get back to design mode, click on the same icon
(it changed to a sideways green triangle). To run the query (and actually
perform the delete) click on the big red bang (lingo for the exclamation mark)
in the middle of the same toolbar. You'll get a confirmation message; just
select 'yes' and watch Access do the rest. If you want, you can save the
query; just exit and give it a name. It'll be saved in the query tab in your
database.

Have fun,

Sam

Can you offer me a little bit of direction, also? I sorta need this quick.

julialatte,

[quoted text clipped - 10 lines]

 

[Guest]

Here is the SQL view for this one I created following your instructions:

DELETE Clean_Export.*
FROM Last_Week INNER JOIN Clean_Export ON (Last_Week.LW_Rec_Ident =
Clean_Export.Rec_Ident) AND (Last_Week.LW_Rec_Detail =
Clean_Export.Rec_Detail);

It errors to "Can not delete records form specified table." What could
cause that? It's a db on my machine that I created.

julialatte,

If you think it'll be more helpful.

Open the query designer to a new query.

In the table list, click first on the summary table (Last_Week) and then on
the detail table(Clean_Export). Close the list.

Select and drag a line from the Ident field in the summary table (thumbnail)
to the Ident field in the detail table. Do the same for the Detail fields of
the tables.This creates the INNER JOIN.

In the query design toolbar at the top of the display, find the query type
icon, and change it to Delete.

In the Clean_Export thumbnail, double-click on the star above the first field
name (the "All fields" selector). Make sure the words "Clean_Export.*" appear
below in the QBE (query by design) grid. Your design is complete.

To test your query, click on the left-most icon of the query design toolbar
(it looks like a cracker). This will display all records in the table with
both fields matching. This tells you which records will be deleted when you
run the query for real. To get back to design mode, click on the same icon
(it changed to a sideways green triangle). To run the query (and actually
perform the delete) click on the big red bang (lingo for the exclamation mark)
in the middle of the same toolbar. You'll get a confirmation message; just
select 'yes' and watch Access do the rest. If you want, you can save the
query; just exit and give it a name. It'll be saved in the query tab in your
database.

Have fun,

Sam

Can you offer me a little bit of direction, also? I sorta need this quick.

julialatte,

[quoted text clipped - 10 lines]

Ident fields match in both tables than the Detail fields in both tables could
also match. If they DO match I want them deleted from Clean_Export only.

 

[OfficeDev18 via AccessMonster.com]

You know what, julialatte,

Just clean up the first line of this query. Change the first line of the
query

DELETE Clean_Export
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

to

DELETE Clean_Export.* (add .*, nothing more)

and try it again. Maybe your way will work.

Sam

This is my attempt but I get an error that states "Specify the table with the
records you want to delete."

Can someone help me with this:

DELETE Clean_Export
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

julialatte,

[quoted text clipped - 10 lines]

 

[Guest]

I still got the error, "Could not delete from specified tables."

This was the view:
DELETE Clean_Export.*
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

You know what, julialatte,

Just clean up the first line of this query. Change the first line of the
query

DELETE Clean_Export
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

to

DELETE Clean_Export.* (add .*, nothing more)

and try it again. Maybe your way will work.

Sam

This is my attempt but I get an error that states "Specify the table with the
records you want to delete."

Can someone help me with this:

DELETE Clean_Export
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

julialatte,

[quoted text clipped - 10 lines]

Ident fields match in both tables than the Detail fields in both tables could
also match. If they DO match I want them deleted from Clean_Export only.

 

[John Vinson]

I still got the error, "Could not delete from specified tables."

This was the view:
DELETE Clean_Export.*
FROM Clean_Export INNER JOIN Last_Week ON
Clean_Export.Rec_Ident=Last_Week.LW_Rec_Ident
WHERE Clean_Export.Rec_Detail=Last_Week.LW_Rec_Detail;

Is Rec_Ident the Primary Key of Clean_Export, or otherwise does it
have a unique Index? If not, you'll get this error.

John W. Vinson[MVP]