Delete Query Assistance

[Guest]

I have the following query built in Access 2k:

DELETE tblTable1.EntryDate, tblTable2.PrimaryKey
FROM tblTable1 LEFT JOIN tblTable2 ON tblTable1.PrimaryKey =
tblTable2.PrimaryKey
WHERE (((tblTable1.EntryDate) Between Date()-30 And Date()) AND
((tblTable2.PrimaryKey) Is Null));

I want the unmatched records deleted from tblTable1, however I am unable to
specify this to Access. Any assistance would be greatly appreciated!

Thanks!
Jake

 

[Anne]

Hey Jake -

You need to drop the * from the table you want to delete
from in to the grid. Also in the query properties, set
unique records to "yes". (You get to the query properties
by double clicking on the grey background of the pane that
the tables are in.)

Resulting SQL should be:

DELETE DISTINCTROW tblTable1.EntryDate,
tblTable2.PrimaryKey, tblTable1.*
FROM tblTable1 LEFT JOIN tblTable2 ON tblTable1.PrimaryKey
= tblTable2.PrimaryKey
WHERE (((tblTable1.EntryDate) Between Date()-30 And Date
()) AND ((tblTable2.PrimaryKey) Is Null));


Hope that helps - Anne

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If tblTable2's column "PrimaryKey" (bad name by the way - since "PRIMARY
KEY" is a reserved phrase in SQL) is really a primary key field it will
NEVER be NULL, 'cuz a Primary Key field must always have a value (it is
required).

If tblTable2.PrimaryKey is not a real Primary Key field, then you could
just delete records from tblTable2 that have NULL "PrimaryKey" fields:

DELETE *
FROM tblTable2
WHERE PrimaryKey IS NULL

If, as I suspect tblTable2 is an archive/mirror of tblTable1, then you
could just expand the above query to include the EntryDate criteria:

DELETE *
FROM tblTable2
WHERE PrimaryKey IS NULL AND EntryDate BETWEEN Date()-30 and Date()

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRKDD4echKqOuFEgEQJsZgCg9SiBMAE0vjDmCGwLdo7K+R7Jt9EAoJBT
cbcgcKaInzBPVKtCmRdst5Ko
=ff5a
-----END PGP SIGNATURE-----