Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Posts
    26

    Wink Unanswered: [StartDate] and [End Date] need every occurence of ??/01/?? and ??/15/?? in between.

    [StartDate] and [End Date] are fields in a Query. I need every occurence of ??/01/?? and ??/15/?? in between those two dates.

    The 1st and 15th of each month represent paydays. So if an employee is assigned to a project from 10/01/06 until 12/15/06 there should be a list of paydates that include:

    10/15/06, 11/01/06, 11/15/06, 12/01/06, and 12/15/06

    I then need to make a record entry into an [Expense] table on each of those dates for the specific employee and their wage rate for the two weeks.

    The actual fields in the Query[ProjectAssignments] are [EmloyeeName], [Project], [StartDate], [EndDate], [WageRate].

    Right now my first step is finding out how to get the results that I need from the two dates. I appreciate any help.

  2. #2
    Join Date
    Jul 2005
    Posts
    39
    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:

    Code:
    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;
    Last edited by taurus; 10-30-06 at 01:23.

  3. #3
    Join Date
    Oct 2006
    Posts
    26

    Getting there!

    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])
    EmpProjStartDate
    EmpProjEndDate
    WageRate


    [PayDate] with Field:

    PayOn (Primary Key)
    I created record entries for every paydate from 10/15/2006 to 10/01/2020


    [ProjectPayroll] with Fields:

    PayId (Primary Key)
    EmployeeId
    PayDate
    Project
    WageRate


    [Projects] with Fields:
    HJFProjectNumber (Primary Key)
    ShortName


    [Employees] with Fields:
    EmployeeId (Primary Key)
    EmployeeName
    DateHired

    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.

  4. #4
    Join Date
    Jul 2005
    Posts
    39
    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.
    Last edited by taurus; 10-30-06 at 19:02.

  5. #5
    Join Date
    Oct 2006
    Posts
    26
    Access is only letting me set one primary key. Is there an SQL method for preventing duplicate records in the table [ProjectPayroll]?

    Everything works great except that the code is generating one extra paydate for each employee.

    ex. Employee 01 is assigned to a project from 10/15/2006 to 12/15/2006

    there are record entries in table [ProjectPayroll] for dates

    11/01/2006
    11/15/2006
    12/01/2006
    12/15/2006
    01/01/2007

    The 12/15/2006 record entry should be the final one.

    I can run the [ProjectPayroll Query] multiple times and it doesn't add any duplicate records. Do you think I need a primary key other than maybe an autonumber for transactionid?

  6. #6
    Join Date
    Oct 2006
    Posts
    26
    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.

  7. #7
    Join Date
    Oct 2006
    Posts
    26
    Okay, I feel a bit silly but the code works correctly. If you are assigned to a project on the 15th then you should receive pay, at least for one day, on the following 1st of the month.

    How do you suggest I go about the primary key issue?

  8. #8
    Join Date
    Oct 2006
    Posts
    26
    Okay, there are some small problems after all. Here is the layout of the table [ProjectAssigned]

    ProjAssignID Employee EmpProjStartDate EmpProjEndDate Project WageRate

    1 Emp1 10/15/2006 12/14/2006 SIM2 $1,250.00
    2 Emp2 11/15/2006 12/14/2006 PULSE!! $1,400.00
    3 Emp3 10/15/2006 12/31/2006 PAC $1,000.00
    4 Emp1 11/15/2006 1/31/2007 PULSE!! $ 950.00
    5 Emp2 12/01/2006 2/14/2007 PAC $1,100.00
    6 Emp3 1/01/2007 2/27/2007 SIM2 $1,000.00



    After running the ProjectPayroll Query the table is populated with the following records.

    Employee PayDate Project WageRate

    Emp1 11/01/2006 SIM2 $1,250
    Emp1 11/15/2006 SIM2 $1,250
    Emp1 12/01/2006 PULSE!! $ 950
    Emp1 12/01/2006 SIM2 $1,250
    Emp1 12/15/2006 PULSE!! $ 950
    Emp1 12/15/2006 SIM2 $1,250
    Emp1 1/01/2007 PULSE!! $ 950
    Emp1 1/15/2007 PULSE!! $ 950
    Emp1 2/01/2007 PULSE!! $ 950
    Emp1 2/15/2007 PULSE!! $ 950
    Emp1 3/01/2007 PULSE!! $ 950

    Emp2 12/01/2006 PULSE!! $1,400
    Emp2 12/15/2006 PULSE!! $1,400
    Emp2 12/15/2006 PAC $1,100
    Emp2 1/01/2007 PAC $1,100
    Emp2 1/15/2007 PAC $1,100
    Emp2 2/01/2007 PAC $1,100
    Emp2 2/15/2007 PAC $1,100

    Emp3 11/01/2006 PAC $1,000
    Emp3 11/15/2006 PAC $1,000
    Emp3 12/01/2006 PAC $1,000
    Emp3 12/15/2006 PAC $1,000
    Emp3 1/01/2007 PAC $1,000
    Emp3 1/15/2007 SIM2 $1,000
    Emp3 1/15/2007 PAC $1,000
    Emp3 2/01/2007 SIM2 $1,000
    Emp3 2/15/2007 SIM2 $1,000
    Emp3 3/01/2007 SIM2 $1,000
    Emp3 3/15/2007 SIM2 $1,000



    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.

  9. #9
    Join Date
    Jul 2005
    Posts
    39
    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.
    Last edited by taurus; 10-31-06 at 00:54.

  10. #10
    Join Date
    Oct 2006
    Posts
    26
    That did it! It works great!

    I can't thank you enough.

  11. #11
    Join Date
    Jul 2005
    Posts
    39
    You're welcome, glad to have helped.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •