Joining only for 1 record...

[Sprawl]

This gets even more confusing as I go.


Two tables again needing to be joined. But only need to fill the data in the first line.... yes, Pain in the ARSE> here's a sample.

PAYLINES TABLE
EMPLOYEE --------- DATE -------- Paytype
121614000 -------- 12/31/2007 --- 300
121614000 -------- 12/31/2007 --- 122
121614000 -------- 12/31/2007 --- 100
121614000 -------- 12/23/2007 --- 100

TAXTABLE
EMPLOYEE --------- DATE -------- CPP ---- EI
121614000 -------- 12/31/2007 --- 1.23 --- 2.35
121614000 -------- 12/23/2007 --- 2.32 --- 1.35


The end Query I need is

EMPLOYEE --------- DATE -------- Paytype --- CPP ---- EI
121614000 -------- 12/31/2007 --- 300 ------- 1.23 ---- 2.35
121614000 -------- 12/31/2007 --- 122 ------- NULL ---- NULL
121614000 -------- 12/31/2007 --- 100 ------- NULL ---- NULL
121614000 -------- 12/23/2007 --- 100 ------- 2.32 ---- 1.35

The TAXTABLE only needs to assign it's data to the FIRST row of the resulting query

 

[pono1]

Guessing... Wouldn't you need a key for PayType in the tax table? The following SQL assumes you have a PayType column as well as an effective date column in your tax table.

[FONT="Courier New"]SELECT p.Employee, p.PayDate, p.PayType, t.CPP, t.EI
FROM PayLines AS p LEFT OUTER JOIN Tax AS t 
         ON    (p.PayDate = t.EffectiveDate) 
         AND  (p.PayType = t.PayType);[/FONT]

Joining on the date, though, is counter-intuitive since dates are not typically keys. If you can live with slightly different results and you don't need to consider the date as a key and if I'm understanding what you're trying to do...

SELECT p.Employee, p.PayDate, p.PayType, t.CPP, t.EI
FROM PayLines AS p LEFT OUTER JOIN Tax AS t 
         ON p.PayType=t.PayType
WHERE p.PayDate >= t.EffectiveDate;

Regards,
Tim

 

[gemma-the-husky]

not trying to be presumptuous but you have a normalisation issue here - first you shouldnt be thinking in terms of first record - access deals with a set of records - if you are storing nulls there is a normalisation issue

so what you really need is a totalpay record fpr each payslip which includes name/ni no/gross pay/tax/ni/ers ni/pension/deductions/expenses etc,

and a payelements table storing the items making up the gross pay - some of which may be taxable and others not

if you are trying to develop a payroll system you really do need to know what are doing - there are many legal issues - if you make wrong deductions you (the employer) is probably responsible for making them good, not the employee

as i say, sorry if i am being presumptuous, but the tables necessary to manage a payroll system are really really complex

 

[Sprawl]

Gemma, you've hit a few nail's on the head so to speak. Im not developing a payroll system, But using Access / SQL2005 Report Writer to write a series of detail reports for our existing accounting package. We are using Microsoft Dynamics Great Plains. While it is very powerful and does a lot of what we need to do, The reporting functionality for detailed reports that are also easy to read is seriously lacking.

I've been charged with writing from scratch the "commision" and "net spread" for our agency. This gets difficult as we are a payroll company that must charge for every hour paid. UNfortunately Great Plains data structure in the back end becomes very disjointed between the different modules. For example the "timesheet" system and the "payroll" system completely obliterates relevant keys to properly know exactly which timesheet line is to be paid on what date. Lets say I pay a timesheet dated June 1st, but pay it on June 11th's payroll cause it's late. The timesheet side puts it in the database as June 1st and passes ti to the payroll side as June 11th.

Writing this thing was really hard as it got more complext with the fees and taxes. In a realistic situation EI and CPP and TAX would have been calculated on a paycode by paycode basis. However they are not. They were stored as a single value for that week as a total CPP. Which created a problem when we needed an hour by hour basis.
Further complications arise when only certain employees for certain paycodes get certain fees and charges.

After about 3 weeks of SQL Writing I finally created around 5 views that linked together resulted in a fairly acurate data set. It came down to more "averaging" certain fees. FOr example i figured that Employee 1, had a total of 57 hours and $20.00 in CPP, so i devided it down to a per hour, and then for each payline just multiplied that line by the average. While not 100% accurate the totals when everything was summed were.

Long story short. I finished it within a 99% accuracy rate which replaced the current one written by a 3rd party that was hitting about 70% accuracy. The sale people are not happy as the old report wasn't adding any costs to anything but regular pay, missing all overtime costs and additional pays.

Thanks for the help though as this was a pain in the ass. If anyone wants I will post the different SQL codes from each View