Calculate difference between query results

[jpharps]

I have query that calculates pay based on several factors then returns a figure for a fortnightly wage. I'm trying to work out a way to have a running sum in that query that will show the difference beween the current and preceeding result for that fortnighlty wage.

eg 1st wage = 1000 diff = 0 (as this is first entry)
2nd wage = 1500 diff = 500
3rd wage = 1750 diff = 250

and so on.

this is driving me nuts so any help would be appreciated.

john

[Stewart Ross]

Hi John. A total-before value and current difference can be worked out in SQL without coding, with a bit of work, but to do so we need to know a bit more detail. Could you post the SQL of your totals query, please, and some details of your employee payment table (in particular, what the primary key/foreign key structure is, and the name of your payment amount and payment date fields)?

Thanks

Stewart

[jpharps]

Hi John. A total-before value and current difference can be worked out in SQL without coding, with a bit of work, but to do so we need to know a bit more detail. Could you post the SQL of your totals query, please, and some details of your employee payment table (in particular, what the primary key/foreign key structure is, and the name of your payment amount and payment date fields)?

Thanks

Stewart

that was quick!

the SQL of the totals is as follows:

SELECT Q_FY_Salary_DataEntry.FinYr, Q_FY_Salary_DataEntry.Date, Q_FY_Salary_DataEntry.[Full Salary], Q_FY_Salary_DataEntry.Hours, Q_FY_Salary_DataEntry.[PT%], Q_FY_Salary_DataEntry.PTY_Wage, [Salary_Allowances_Main-FinYr].Allowances, Format(IIf(DateValue([Date])>=#10/6/2007#,[PTY_Wage]*([Salsac_%]/100),"0"),"0") AS SalSac, [PTY_Wage]+[Allowances]-[SalSac] AS TaxInc, Format(IIf([TaxInc]<=[End1],0,IIf([TaxInc]<=[End2],([TaxInc]-[End1])*[Rate2],IIf([TaxInc]<=[End3],(([TaxInc]-[End2])*[Rate3])+[Tax3],IIf([TaxInc]<[End4],(([TaxInc]-[End3])*[Rate4])+[Tax4],(([TaxInc]-[End4])*[Rate5])+[Tax5])))),"Fixed") AS Tax, [PTY_Wage]+[Allowances]-[Tax]-[Salsac] AS Inc_Yr, Format([inc_Yr]/26,"0") AS Inc_FN, ([PTY_Wage]+[Allowances])/26 AS FNInc, [Q_Total-VPSalaryAllowances].VPSalaryAllow AS SalAll, Format([Tax]/26,"0") AS FNTax, [Q_Total-VPSalaryDeductions].VPDeduction AS FNSalD, [Q_Total-Expense].[Expense Total], [Inc_FN]+[Income Total]+[VPSalaryAllow]-[VPDeduction]-[Expense Total] AS DispInc_FN
FROM [Q_Total-VPSalaryAllowances], [Q_Total-VPSalaryDeductions], [Q_Total-Income], [Q_Total-Expense], ((Q_FY_Salary_DataEntry INNER JOIN Tax_Rates ON Q_FY_Salary_DataEntry.FinYr = Tax_Rates.FinancialYear) INNER JOIN ESSS_ContributionRates ON Q_FY_Salary_DataEntry.[ESSS%] = ESSS_ContributionRates.[ESSS_%]) INNER JOIN [Salary_Allowances_Main-FinYr] ON Tax_Rates.FinancialYear = [Salary_Allowances_Main-FinYr].FinancialYear
ORDER BY Q_FY_Salary_DataEntry.Date;

this query acceses sevaral tables (containing salaries increments, tax rates, allowances, and superannuation contributions) then works out tax, allowances and so on to come up with a fortnighly wage [FN_GrossWage]. The date field used to order the results is [Date].

The wages are listed in groups determined by their financial year and the dates can be duplicated (as some have same base pay increments but different allowances, super payments etc)

There isn't a primary field and I'm not sure what a foreign key is!

Basically I want to compare the FN_GrossWage results so I can include a field that shows the difference in FN_GrossWage from the curernt result compared to the previous (ie the amount of the increase or decrease for each returned FN_GrossWage).

Hope this is what you were after and thanks again


John.

[Stewart Ross]

Hi John, and thanks for your prompt reply. A primary key is the field (or combination of fields) that uniquely identifies a row within a table. A foreign key (alternately known as a secondary key) is a key value from one table linking a characteristic in a related table (e.g. in an employment history table the employee reference number is a foreign key linking back to the details of specific employees). A foreign key is on the 'many' side of a one-to-many relationship.

There is a good article on database design and normalisation in the HowTo pages of the forum at http://www.thescripts.com/forum/thread585228.html.

Further to your previous reply, could I ask you to identify the employee number or other reference field you are using that identifies specific employees? In an employee table the employee number would normally be the primary key, and it is a little disquieting that in your reply you mention there is no primary key!

Could I also ask you to use the Code tags (available from the hash symbol '#' at the top of the editor window) around your SQL in future posts, as it helps the readability of the code?

-Stewart

[jpharps]

Hi John, and thanks for your prompt reply. A primary key is the field (or combination of fields) that uniquely identifies a row within a table. A foreign key (alternately known as a secondary key) is a key value from one table linking a characteristic in a related table (e.g. in an employment history table the employee reference number is a foreign key linking back to the details of specific employees). A foreign key is on the 'many' side of a one-to-many relationship.

There is a good article on database design and normalisation in the HowTo pages of the forum at http://www.thescripts.com/forum/thread585228.html.

Further to your previous reply, could I ask you to identify the employee number or other reference field you are using that identifies specific employees? In an employee table the employee number would normally be the primary key, and it is a little disquieting that in your reply you mention there is no primary key!

Could I also ask you to use the Code tags (available from the hash symbol '#' at the top of the editor window) around your SQL in future posts, as it helps the readability of the code?

-Stewart

Thanks Stewart,
Forgot about the # keys, sorry for making it difficult.

In regards to the primary key, I didnt need the records to be unique so didnt make a primary key. Sounds like that was a bad idea but I'm learning as I go! There are no employee details/ID numbers involved in the data base, it's soley wage increases over certain periods factoring in super payments, tax etc. It's for my use only to try and work out the complicated pay system I work under!

The query acesses a table (salary_Allowan ces_FinYr) containing the basic wage details being the [Date] of the pay increase, the full salary [Full Salary], and other details such as [hours] worked which is used to determine if its a Part Time wage.

I use a combination of [Date], [Full Salary] and [Hours] to identify specific records if needed.

The table is linked to other tables queries as needed either by the three fields above or else by the FinancialYear (determined in a query) for things such as Tax Rates etc

Thanks

John

[Stewart Ross]

Hi John. I will come back to you once I've played around with this one a bit. It is a distinctly non-trivial task, although it can be done. Thought: this kind of running total and difference task is one that Excel is excellent at. As the data is fortnightly and is for no-one other than yourself (i.e. you are not trying to summarise totals for a small business) have you considered maintaining a weekly payment log sheet in Excel in which you could easily calculate the sums and differences?

An SQL solution is possible, but an Excel solution is far simpler, and Excel is the better tool for the job. As the old saying goes, if the only tool you've used is a hammer then the whole world looks like a nail...

Regards

Stewart

[Stewart Ross]

Hello again John. This lengthy response boils down to very simple advice - you should use Excel instead for your pay sum and difference calculations.

With Excel you can refer directly to values from previous pay weeks (i.e. rows before the current row in the table) which in SQL is difficult to do. (Reason for that is that SQL is based on set arithmetic where sets such as {1, 2, 3}, {2, 3, 1} and {3, 1, 2} are all equivalent. Because set membership is independent of item position explicit record numbering is not and never has been part of SQL. SQL has always provided means of ordering sets but still has no concept of record number.)

To check out your requirements in Access I created a simple table called Pay which has just three fields: Date, Hours and Wage. This last is the analogue of your overall calculation field. I populated it with some test data, some of which is replicated below:

Date Hours Wage
01/01/2008 20 200
15/01/2008 25 220
29/01/2008 30 300
12/02/2008 25 250
26/02/2008 30 300

To perform a pay difference calculation is a multi-stage process in SQL. First, join the pay table back to itself in such a way that the current and last pay dates are side by side:
QryPayPeriods
[CODE=SQL]SELECT Pay.Date, Max([Last Pay].Date) AS [Prev Pay Date]
FROM Pay, Pay AS [Last Pay]
WHERE ((([Last Pay].Date)<[Pay].[Date]))
GROUP BY Pay.Date
ORDER BY Pay.Date, Max([Last Pay].Date);
[/CODE]
Then, join the pay periods back to two copies of the Pay table to get the current and previous wage amounts and calculate their differences:
QryPayDiffs
[Code=SQL]SELECT QryPayPeriods.D ate, Pay.Wage, QryPayPeriods.[Prev Pay Date], [Previous Pay].Wage AS [Prev Wage], Pay.Wage-[Previous Pay].Wage AS Diff
FROM (Pay INNER JOIN QryPayPeriods ON Pay.Date = QryPayPeriods.D ate) INNER JOIN Pay AS [Previous Pay] ON QryPayPeriods.[Prev Pay Date] = [Previous Pay].Date; [/CODE]
When run, this gives:

Date Wage Prev Pay Date Prev Wage Diff
15/01/2008 220 01/01/2008 200 £20.00
29/01/2008 300 15/01/2008 220 £80.00
12/02/2008 250 29/01/2008 300 -£50.00
26/02/2008 300 12/02/2008 250 £50.00

Whilst it is correct (when checked against equivalent Excel calcs) it takes much longer to set up and does not allow cumulative wages to be calculated, which can be done easily in Excel. The cumulative calculations require another two queries, which I won't reproduce here as it is really wasted effort to go down this route.

Use Excel instead - it's much quicker, much more capable for such calculations. Excel does not pretend to be a relational database - what your deceptively simple original question really needs is not a database but the powerful general-purpose calculating engine that is Excel.

-Stewart

[jpharps]

Stewart,
as a mere mortal you appear as a God!!!

Many thanks, it worked a treat. I don't know Excel but it sounds as if I need to learn. Many thanks your your help.

John