Maybe this will help. First create tblPayDate and populate with your two fixed days per month + 2007-01-01 (or in your format). I assume you have two other tables tblProjectAssigned and tblProjectPayroll. Attributes (employeeId , paydate) must be primary key in tblProjectPayroll to avoid duplicate inserts from following:
INSERT INTO tblProjectPayroll ( employeeid, payDate, wageRate )
SELECT tblProjectAssigned.employeeId, tblPayDate.PayOn, tblProjectAssigned.wageRate
FROM tblProjectAssigned, tblPayDate
WHERE (((tblPayDate.PayOn>tblProjectAssigned.startDate And IIf(Day(tblProjectAssigned.endDate)<15,tblPayDate.PayOn-tblProjectAssigned.endDate<14,tblPayDate.PayOn-tblProjectAssigned.endDate<27))=-1))
GROUP BY tblProjectAssigned.employeeId, tblPayDate.PayOn, tblProjectAssigned.wageRate;
Employees may be assigned to multiple projects, which are selected from tblProjects, at a time. During such instances they would receive multiple pay entries which are dependent on the number of projects assigned during the pay period.
I have my tables setup as follows.
[ProjectAssigned] with Fields:
ProjAssignID (Primary Key)
Employee (Lookup value from table [Employees])
Project (Lookup value from table [Projects])
[PayDate] with Field:
PayOn (Primary Key)
I created record entries for every paydate from 10/15/2006 to 10/01/2020
[Projects] with Fields:
HJFProjectNumber (Primary Key)
[Employees] with Fields:
EmployeeId (Primary Key)
Here is your code modified with the new names.
INSERT INTO tblProjectPayroll ( EmployeeId, PayDate, WageRate )
SELECT tblProjectAssigned.EmployeeId, tblPayDate.PayOn, tblProjectAssigned.WageRate
FROM tblProjectAssigned, tblPayDate
WHERE (((tblPayDate.PayOn>tblProjectAssigned.EmpProjStar tDate And IIf(Day(tblProjectAssigned.EmpProjEndDate)<15,tblP ayDate.PayOn-tblProjectAssigned.EmpProjEndDate<14,tblPayDate.Pa yOn-tblProjectAssigned.EmpProjEndDate<27))=-1))
GROUP BY tblProjectAssigned.EmployeeId, tblPayDate.PayOn, tblProjectAssigned.wageRate;
Where in my database would I enter this code? Is it something to do with a module? If so I have no experience using modules? Would it be a Query? If so, where in the query? I would also like to have the Project in the ProjectPayroll table. I greatly appreciate your help.
Paste the code into a new query, save with a name (qry_appProjectPayroll or something of your choice) and run query as usual. A dialog box may appear to alert you that duplicates cannot be appended to the tblProjectPayroll, just click yes. You can also switch to the design view as that is sometimes easier to use in modifying the code.
If you create a new query in the design view, do not add any tables and instead change to the SQL view (View - SQL view or click upper left SQL icon in the query design toolbar).
Also you should modify the code to include attribute tblProjectAssigned.Project and tblProjectPayroll should now have employeeId project and PayDate selected as primary key.
I was wrong, it will add duplicate records. They just didn't appear until I closed the db and re-opened it. I tried messing with the code but still haven't solved the extra date problem. Is there a way to tell the code to subtract the last record from the results? Or do you already know how to adjust it? Thanks.
For Emp1 the Pulse!! assignment ended on 1/31/2007 so the last paydate should have been 2/01/2007. It generated 2 extra paydates.
All of the paydates for Emp2 are correct.
For Emp3 the PAC assignment ended on 12/31/2006 so the last paydate for PAC should have been 1/01/2007. It generated 1 extra paydate.
For Emp3 the SIM2 assignment ended on 2/27/2007 so the last paydate should have been 3/01/2007. Again it generated 1 extra paydate.
To create a composite primary key, in the table design view, select the multiple fields whilst pressing the CTRL-key, then set the multiple selection as a primary key by clicking the key icon in the design toolbar. In which case the payId attribute is not required in the tblProjectPayroll.
To recheck the extra dates, delete all records from tblProjectPayroll and rerun qry_appProjectPayroll after you change the where condition from tblPayDate.PayOn-tblProjectAssigned.endDate<27 to tblPayDate.PayOn-tblProjectAssigned.endDate<13.