VB Code To Delete Records Using Multiple Relationships

[foxhound]

Okay, I have 4 tables that I have to keep separate.  Employees, PayHistory, Codes and AwardDates.

Employees has 2 fields: EmpNo, Group
PayHistory has 4 fields: EmpNo, DBA, Date, Hours
AwardDates has 4 fields: Group, Type, Current, CalcDate
Codes has 3 fields: DBA, Sick, Vac

Each table has various relationships:  
   Employees.EmpNo = PayHistory.EmpNo
	PayHistory.DBA = Codes.DBA
	AwardDates.Group = Employees.Group

					
I need to delete all records from PayHistory where Date > CalcDate when Current = true and Type = “Vacation” and Vac = “Y”

Can anyone tell me the VB code for this?

 

[foxhound]

Anyone?

 

[Joe4]

I don't think you need VBA. Check out the built-in Access help on "Create a delete query".

 

[mdmilner]

As a tip, you don't need VBA but it can be done, but you have to know how to write the SQL to do it efficiently.

Read up on jmiskey's suggestion, setup a query to do it. Then look at the SQL View of the query and copy it into Word.

You can then setup a vba routine that does something like:

Dim strSQL As String

strSQL = "put_the_query here"
strSQL = strSQL & "add_more_of_the_query_here_as_needed"

DoCmd.RunSQL strSQL

But to emphasize, this is unnecessary if you're just doing things interactively. Assuming you'll have a long query, make sure you watch the spacing between lines. Also, convert all " to ' within the query that you're copying.

Mike

 

[foxhound]

Thanks for the suggestions guys. Although, I am still having difficulties. Here is my code:

DELETE [PayHistory].*, [PayHistory].EmpNo, [PayHistory].Date, [PayHistory].DBA, [PayHistory].Hours, [AwardDates].CalcDate, [AwardDates].Current, [AwardDates].Type, [Codes].Vac
FROM (Employees INNER JOIN ([PayHistory] INNER JOIN [Codes] ON [PayHistory].DBA = [Codes].DBA) ON Employees.EmpNo = [PayHistory].EmpNo) INNER JOIN [AwardDates] ON Employees.Group = [AwardDates].Group
WHERE ((([PayHistory].Date)>[calcdate]) AND (([AwardDates].Current)=True) AND (([AwardDates].Type)="vacation") AND (([Codes].Vac)="Y"));

I created a query that pulls the correct records that I want pulled, changed it to a delete query, and pulled the * to the bottom for the table that I need to delete the records from. But I get an error stating that I can't delete records from that table. Is it because of the joins? Please advise

 

[foxhound]

I got it. All I had to do was change "delete" to "delete distinctrow." I may need some further help later on another part of this. So, please keep me in mind