Deleting records in Table

[1994mkiv]

We have 2 tables LaborHed and LaborDtl. LaborHed contains EmployeeNum, ClockInDate ans some other info. LaborDtl contains EmployeeNum, ClockInDate, JobNum and some other info.
Example of LaborHed:

Example of LaborDtl:

To show the records the qry I wrote is :
SELECT PUB_LaborHed.EmployeeNum, PUB_LaborHed.ClockInDate, PUB_LaborDtl.JobNum
FROM PUB_LaborHed LEFT JOIN PUB_LaborDtl ON (PUB_LaborHed.ClockInDate = PUB_LaborDtl.ClockInDate) AND (PUB_LaborHed.EmployeeNum = PUB_LaborDtl.EmployeeNum);

Now you can see there are some records in LaborHed that Do not have any detail. I need to write a qry to delete all of these records from LaborHed.

 

[traingamer]

delete
FROM PUB_LaborHed LEFT JOIN PUB_LaborDtl ON (PUB_LaborHed.ClockInDate = PUB_LaborDtl.ClockInDate) AND (PUB_LaborHed.EmployeeNum = PUB_LaborDtl.EmployeeNum)
[COLOR=blue]where PUB_LaborDtl.EmployeeNum IS NULL[/color]

Test it as a select first.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[1994mkiv]

There is an error in the qry

"Specify the table containg the records you want to delete

 

[traingamer]

delete [b]PUB_LaborHed.*[/b]
FROM PUB_LaborHed LEFT JOIN PUB_LaborDtl ON (PUB_LaborHed.ClockInDate = PUB_LaborDtl.ClockInDate) AND (PUB_LaborHed.EmployeeNum = PUB_LaborDtl.EmployeeNum)
where PUB_LaborDtl.EmployeeNum IS NULL

Test first.

The table name is optional if using only one table in the query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[1994mkiv]

Error Again

"Could not Delete From Specified Table"


Thankyou for keep looking at the code

 

[dhookom]

Is the primary key of LaborHed the combination of ClockInDate and EmployeeNum?

Duane
Hook'D on Access
MS Access MVP

 

[1994mkiv]

yes they are

 

[dhookom]

Access doesn't always resolve queries very well. Try something like:

delete PUB_LaborHed.*
FROM PUB_LaborHed 
WHERE ClockInDate Not In 
(SELECT ClockInDate FROM PUB_LaborDtl 
WHERE PUB_LaborHed.ClockInDate = PUB_LaborDtl.ClockInDate AND PUB_LaborHed.EmployeeNum = PUB_LaborDtl.EmployeeNum)

Duane
Hook'D on Access
MS Access MVP

 

[1994mkiv]

Thank you, it worked like a champ