Delete query help?

[bskowtexas]

I've got a check out table with three tables linked to it (items, kits, and books) in a one to many relationship. Essentially, its set up so somebody can check out multiple amounts of items, kits, and books on one reciept. Okay, so on the front end of things, I have a form which automatically fills in some of the check out info whenever you start entering items, kits, or books. But the problem i'm running into is that occassionally people save checkouts without having checked anything out. Is there a way to make a delete query to delete the checkout if there is nothing in its underlying tables. I'm sure there's a way to do this with the criteria section, but i'm having trouble. Any help would be greatly appreciated.

-bskowtexas

 

[Fuga]

I´m not sure if you can delete directly or if you have to do that seperately, but to select the records to delet (ie the first thing you need to do) you can use a missmatch query. I think you can have a missmatch as a delete query too, but I´m not sure.

Fuga.

 

[bskowtexas]

Okay, I've made the mismatch query and that works great. However, when I switch it from a select query to a delete query, it gives me a message saying, "specify the table containing the records you want to delete."

here's a copy of my sql in case that's where the problem lies.

DELETE [CheckOut].[CheckOutnum], [KitCheckOut].[CheckOutnum], [BookCheckOut].[CheckOutnum], [ItemCheckOut].[CheckOutnum]
FROM (([CheckOut] LEFT JOIN [BookCheckOut] ON [CheckOut].[CheckOutnum] = [BookCheckOut].[CheckOutnum]) LEFT JOIN [ItemCheckOut] ON [CheckOut].[CheckOutnum] = [ItemCheckOut].[CheckOutnum]) LEFT JOIN [KitCheckOut] ON [CheckOut].[CheckOutnum] = [KitCheckOut].[CheckOutnum]
WHERE ((([KitCheckOut].[CheckOutnum]) Is Null) AND (([BookCheckOut].[CheckOutnum]) Is Null) AND (([ItemCheckOut].[CheckOutnum]) Is Null));

I'm think it's just a matter of putting something in there to tell it to delete the record from the table [CheckOut]?

-bskowtexas

 

[jostrep]

Try this

For your delete query, you probably need to isolate the field which contains the NULL values , then have the criteria "is null" You only need one field for the whole query or if you need more fields with their criteria (All Nulls?) to isolate those records you wanna delete.

Hope this help you




Jos

 

[bskowtexas]

Thanks for the post, but i already have the fields specified to Is Null, and the select query functions perfectly. Its only when i switch it to a delete query that it messes up. DOn't know how to fix it.

 

[Jon K]

I once got a similar problem. I used a Left Join to find the IDs in Table1 that did not exist in Table2:-

SELECT Table1.ID
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE (Table2.ID Is Null);

But when I switched it to a Delete query, it gave me the same error message as it gave you.


Only when I changed the outer join to a subquery:-

SELECT ID
FROM Table1
WHERE ID not in (Select ID from Table2)

could I successfully switch it to a Delete query.


It seems Access doesn't like outer joins in Delete queries.