Results 1 to 5 of 5
  1. #1
    Khatuaaccess is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jul 2016
    Posts
    13

    access vba code for row comparision

    Hi All,



    I am very new to ms access vba.

    actually i have three tables like below. where as i want to compare the data for 1 employee for different month and based on that i want to update the required columns.

    Please refer the attached file.

    For example, i want to pick 1 employee and check if the designation of an employee is different compare to the last month then i update the column "promotion?" comparing with data from table 2 and table 3.

    Simillarly other columns if there is a change in salary, then update the column increment?.

    Please advise if this is possible using VBA in access.


    thanks
    Prasanta





    Tabel 1 Table 2 Table 3
    EmpID EmpName Designation Salary DataMonth Promotion? SalaryIncrement? Promotion+SalaryIncrement? Dcode Designation1 Dcode Designation2
    1234 Ashok Asst Manager 5000 07/27/15 PM1 Asst Manager IC1 Executive
    1234 Ashok Manager 5000 08/27/15 PM2 Manager IC2 Sr Executive
    1235 Sameer Executive 4000 07/27/15 PM3 Senior Manager
    1235 Sameer Asst Manager 5000 08/27/15
    1236 Karim Sr Manager 6000 07/27/15
    1236 Karim Manager 5000 08/27/15
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you would need to a query and make use of subqueries - something like

    SELECT *, (SELECT top 1 designation FROM table1 T WHERE EmpID=Table1.empID AND DateMonth<Table1.DateMonth ORDER BY DateMonth Desc)<>Designation AS Promotion, (SELECT top 1 Salary FROM table1 T WHERE EmpID=Table1.empID AND DateMonth<Table1.DateMonth ORDER BY DateMonth Desc)<Salary AS SalaryIncrement, (SELECT top 1 designation FROM table1 T WHERE EmpID=Table1.empID AND DateMonth<Table1.DateMonth ORDER BY DateMonth Desc)<>Designation AND (SELECT top 1 Salary FROM table1 T WHERE EmpID=Table1.empID AND DateMonth<Table1.DateMonth ORDER BY DateMonth Desc)<Salary AS PromotionAndIncrement
    FROM table1 inner join (SELECT Max(datemonth) as current FROM Table1 T where empID=Table1.EmpID) M ON table1.datemonth=M.currrent

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Just thought of a simpler way
    SELECT C.*,P.designation<>C.Designation as Promotion, P.salary<C.Salary as SalaryIncrement, P.designation<>C.Designation AND P.salary<C.Salary AS PromotionAndIncrement
    FROM table1 P, table1 C inner join (SELECT Max(datemonth) as current FROM Table1 T where empID=Table1.EmpID) M ON C.datemonth=M.current
    WHERE Datediff("m",C.datemonth,P.datemonth)=1 AND C.EmpID=P.EmpID

  4. #4
    Khatuaaccess is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jul 2016
    Posts
    13
    thanks for this query.

    But how can check with table2, if its a promotion or demotion.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. open the query is design view
    2. drag table2 onto the grid twice - the second one will be called table2_1
    3. Connect P.Designation to table2.designation1
    4. Connect C.designation to table2_1.designation1
    5. compare the resultant table2.DCode to table2_1.DCode

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-31-2016, 05:18 PM
  2. update VBA 2.0 code in forms into access 7.0 code
    By toughwg in forum Programming
    Replies: 1
    Last Post: 12-08-2015, 11:40 AM
  3. Year over Year Comparision of Portfolio
    By richard70 in forum Queries
    Replies: 2
    Last Post: 10-31-2011, 08:19 PM
  4. Word code in Access - How to modify my current code
    By Alexandre Cote in forum Programming
    Replies: 0
    Last Post: 11-15-2010, 08:26 AM
  5. Access 2003 code vs Access 2007 Code
    By ralphjramirez in forum Access
    Replies: 5
    Last Post: 11-23-2009, 12:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums