Delete Query -- QUESTION

[kealaz]

I want to delete from tblBUY those parts that I just cut a purchase
order for.

tblBUY does not have P_O_NO because it is before the purchase order is
cut.

I want to build a query that filters tblBUYHIST and then take ALL of
the
part numbers [PART_NO] on the resulting records and delete all records
with
matching part numbers [PART_NO] on tblBUY.

I only want to delete records from tblBUY, not tblBUYHIST.

*************************************************

Here's what I'm trying.


I have created a SELECT Query [qryDeleteBUY_a]

Field: P_O_NO
Table: tblBUYHIST
Criteria: [Enter PO No:] I'm want to change this so that it gets the
info
from strCriteria from my form [frmPO_ISSUE].

Field: PART_NO
Table: tblBUYHIST


This isolates the P.O. I enter and is working how I want it to work.



I'm also want to create a DELETE Query [qryDeleteBUY_b]

I think this will work

DELETE tblBUY.PART_NO
FROM tblBUY
WHERE PART_NO IN (SELECT PART_NO FROM qryDeleteBUY_a)


How do I translate that into the required fields in the design view of
the
DELETE query?

Field:
Table:
Delete:
Criteria:


*************************************************


Thank you for any help you can provide!!!

 

[DStegon via AccessMonster.com]

DELETE tblBUY .* FROM tblBUY INNER JOIN tblBUYHIST ON tblBUY.PART_NO=
tblBUYHIST.PART_NO;

I want to delete from tblBUY those parts that I just cut a purchase
order for.

tblBUY does not have P_O_NO because it is before the purchase order is
cut.

I want to build a query that filters tblBUYHIST and then take ALL of
the
part numbers [PART_NO] on the resulting records and delete all records
with
matching part numbers [PART_NO] on tblBUY.

I only want to delete records from tblBUY, not tblBUYHIST.

*************************************************

Here's what I'm trying.

I have created a SELECT Query [qryDeleteBUY_a]

Field: P_O_NO
Table: tblBUYHIST
Criteria: [Enter PO No:] I'm want to change this so that it gets the
info
from strCriteria from my form [frmPO_ISSUE].

Field: PART_NO
Table: tblBUYHIST

This isolates the P.O. I enter and is working how I want it to work.

I'm also want to create a DELETE Query [qryDeleteBUY_b]

I think this will work

DELETE tblBUY.PART_NO
FROM tblBUY
WHERE PART_NO IN (SELECT PART_NO FROM qryDeleteBUY_a)

How do I translate that into the required fields in the design view of
the
DELETE query?

Field:
Table:
Delete:
Criteria:

*************************************************

Thank you for any help you can provide!!!

 

[kealaz]

How do I translate that into the required fields in the design view of
the DELETE query?

Field:
Table:
Delete:
Criteria:

 

[John W. Vinson]

How do I translate that into the required fields in the design view of
the DELETE query?

The SQL *IS* the query.

The query design grid is not the query. It's a tool to help you build SQL.

Open a new query; don't select any tables. Select View... SQL. Copy and paste
DS's query in place of the default SELECT; which is all that you will see in
the query window.

You can then go back to design view to see how it looks in the grid.

 

[kealaz]

The SQL *IS* the query.

THANKS JOHN! That's exactly what I needed to know. I knew that was
the SQL query, I just didn't know how to get it into the query. I
didn't realize I needed to change the view. Thanks for posting!!! I
got it working.