Hi

I have two tables, tblOrders with 2247 records and tblOrderDetails with
30573 records. The tables are NOT linked at present. Both tables have a Long
Integer field called OrderID (Unique Index in tblOrders). Using the Unmatched
Query Wizard I have identified 27081 records in tblOrderDetails that do not
have corresponding records in tblOrders (just don't ask me how). Is there any
simple way, say by using a Delete Query, to delete these orphan records
without having to write a load of VBA code. Access will not let me delete the
list that the query returns.

This is the query :-

SELECT tblOrderDetails.ID
FROM tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.OrderID =
tblOrders.OrderID
WHERE (((tblOrders.OrderID ) Is Null));

--
Peter Hibbs

DELETE FROM tblOrderDetails
WHERE tblOrderDetails.OrderID NOT IN
(SELECT DISTINCT OrderID FROM tblOrders)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Peter Hibbs" <(E-Mail Removed)_SPAM> wrote in message
news:53EF296F-EFB3-44C4-95B0-(E-Mail Removed)...
> Hi
>
> I have two tables, tblOrders with 2247 records and tblOrderDetails with
> 30573 records. The tables are NOT linked at present. Both tables have a
> Long
> Integer field called OrderID (Unique Index in tblOrders). Using the
> Unmatched
> Query Wizard I have identified 27081 records in tblOrderDetails that do
> not
> have corresponding records in tblOrders (just don't ask me how). Is there
> any
> simple way, say by using a Delete Query, to delete these orphan records
> without having to write a load of VBA code. Access will not let me delete
> the
> list that the query returns.
>
> This is the query :-
>
> SELECT tblOrderDetails.ID
> FROM tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.OrderID =
> tblOrders.OrderID
> WHERE (((tblOrders.OrderID ) Is Null));
>
> --
> Peter Hibbs

This subquery should identify the records in tblOrderDetails that have no
matching OrderID value in tblOrders:

DELETE FROM tblOrderDetails
WHERE NOT EXISTS
(SELECT OrderID FROM tblOrders
WHERE tblOrders.OrderID = tblOrderDetails.OrderID);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Presumably you want to create the relation with RI once this is cleaned up.
Can I also suggest that you set the Required property to Yes for OrderID in
tblOrderDetails, and remove the zero from its Default Value property. The
zero is often the source of these orphans, and the Required property is
needed even with RI.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Hibbs" <(E-Mail Removed)_SPAM> wrote in message
news:53EF296F-EFB3-44C4-95B0-(E-Mail Removed)...
>
> I have two tables, tblOrders with 2247 records and tblOrderDetails with
> 30573 records. The tables are NOT linked at present. Both tables have a
> Long
> Integer field called OrderID (Unique Index in tblOrders). Using the
> Unmatched
> Query Wizard I have identified 27081 records in tblOrderDetails that do
> not
> have corresponding records in tblOrders (just don't ask me how). Is there
> any
> simple way, say by using a Delete Query, to delete these orphan records
> without having to write a load of VBA code. Access will not let me delete
> the
> list that the query returns.
>
> This is the query :-
>
> SELECT tblOrderDetails.ID
> FROM tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.OrderID =
> tblOrders.OrderID
> WHERE (((tblOrders.OrderID ) Is Null));
>
> --
> Peter Hibbs

Thanks very much Douglas and Allen for some useful information. Tables now
linked OK.
--
Peter Hibbs


"Allen Browne" wrote:

> This subquery should identify the records in tblOrderDetails that have no
> matching OrderID value in tblOrders:
>
> DELETE FROM tblOrderDetails
> WHERE NOT EXISTS
> (SELECT OrderID FROM tblOrders
> WHERE tblOrders.OrderID = tblOrderDetails.OrderID);
>
> If subqueries are new, see:
> How to Create and Use Subqueries
> at:
> http://support.microsoft.com/?id=209066
>
> Presumably you want to create the relation with RI once this is cleaned up.
> Can I also suggest that you set the Required property to Yes for OrderID in
> tblOrderDetails, and remove the zero from its Default Value property. The
> zero is often the source of these orphans, and the Required property is
> needed even with RI.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Peter Hibbs" <(E-Mail Removed)_SPAM> wrote in message
> news:53EF296F-EFB3-44C4-95B0-(E-Mail Removed)...
> >
> > I have two tables, tblOrders with 2247 records and tblOrderDetails with
> > 30573 records. The tables are NOT linked at present. Both tables have a
> > Long
> > Integer field called OrderID (Unique Index in tblOrders). Using the
> > Unmatched
> > Query Wizard I have identified 27081 records in tblOrderDetails that do
> > not
> > have corresponding records in tblOrders (just don't ask me how). Is there
> > any
> > simple way, say by using a Delete Query, to delete these orphan records
> > without having to write a load of VBA code. Access will not let me delete
> > the
> > list that the query returns.
> >
> > This is the query :-
> >
> > SELECT tblOrderDetails.ID
> > FROM tblOrderDetails LEFT JOIN tblOrders ON tblOrderDetails.OrderID =
> > tblOrders.OrderID
> > WHERE (((tblOrders.OrderID ) Is Null));
> >
> > --
> > Peter Hibbs
>
>
>