Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2006

    Unanswered: How to Join My Two Payroll Tables

    I want to join two Tables: PayDetails and StaffInfo tables. The common field is PayID. The field is a Primary key in the PayDetail Table and a Foreign key in the StaffInfo Table.

    I used the following SQL Statement to join the two Tables:
    rcsEmployees.Open "Select  tblStaffInfo.*, tblPayDetails.* From tblPayDetails Inner Join tblStaffInfo On tblPayDetails.PayID= tblStaffInfo.PayID Order By StaffNo", _
    con, adOpenStatic, adLockOptimistic
    To my surprise only the StaffInfo Table was updated! Please, how do I write the SQL Statement so that the two Tables are updated? Kindly help me.

    I decided to try another method as shown below. The result was worst.
    rcsEmployees.Open "SELECT StaffNo, StaffName, Title, StaffCat, Phone, Dept," & "BDate, MStatus, Position, DateEmployed, State, Union, ConGL, Step," & _
    "Accomodation, PayMode,Bank, Location, Branch, AccountNo, ProfGroup, " & _
    "ConPay, Rent, HazAllow, CallAllow, CallUnits, ShiftAllow, JuOTRate, SeOTRate, " & _
    "OTHrs, ConArrears, LeavePay, OTPay, GrossPay, FreePay, TaxablePay, NetPay, " & _
    "CoopRate, PensionRate, NHFRate, COEASURate, SSUCOERate, ASUURate, SSANURate, " & _
    "NASURate, NAATRate, RentDed, CoopCont, StaffWelfare, NHFCont, UnionLevy, " & _
    "Tax, PensionCont, SalAdvRecov, CoopLoRecov, CoopInt, UnionDues, PersLoRecov, " & _
    "PersLoInt, TotalDed, YTDConPay, YTDRent, YTDHazAllow, YTDCallAllow, YTDShiftAllow, " & _
    "YTDConArrears, YTDOTPay, YTDGrossPay, YTDFreePay, YTDTaxablePay, YTDNetPay, " & _
    "YTDTax, YTDPensionCont, YTDRentDed, YTDUnionDues, YTDCoopCont, YTDStaffWelfare, " & _
    "YTDNHFCont, YTDCoopLoRecov, YTDCoopInt, YTDUnionLevy, YTDPersLoRecov, YTDPersLoInt, " & _
    "YTDSalAdvRecov, YTDTotalDed, Notes FROM tblStaffInfo, tblPayDetails Join tblPayDetails ON tblStaffInfo.PayID= tblPayDetails.PayID ORDER BY StaffNo", _
    con, adOpenStatic, adLockOptimistic
    Thank you in advance. How can I connect these two tables?

    Last edited by loquin; 01-29-09 at 14:02. Reason: add [code] [/code] tags to improve readability

Posting Permissions

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