Problem with sub queries in Access

[whatcondo]

I am having a great deal of trouble with subqueries in Access.

I am trying to write a Payroll application, I have the following tables

PayRecords
+----+------------+--------+----------+
| Id | Date | Pay | Employee |
+----+------------+--------+----------+
| 0 | 19/04/2001 | 123.45 | 1 |
| 1 | 19/05/2001 | 123.45 | 1 |
| 2 | 19/06/2001 | 123.45 | 1 |
| 3 | 19/07/2001 | 123.45 | 1 |
| 4 | 19/08/2001 | 123.45 | 1 |
| 5 | 19/04/2001 | 97.68 | 2 |
| 6 | 19/05/2001 | 97.68 | 2 |
| 7 | 19/06/2001 | 97.68 | 2 |
| 8 | 19/07/2001 | 97.68 | 2 |
| 9 | 19/08/2001 | 97.68 | 2 |
+----+------------+--------+----------+

Employees
+----+------------+------------+
| Id | Surname | First name |
+----+------------+------------+
| 1 | Bloggs | Joe |
| 2 | Smith | Bill |
+----+------------+------------+
TaxCodes
+----+------------+------------+-------+
| Id | Date | Employee | Code |
+----+------------+------------+-------+
| 1 | 06/04/2001 | 1 | 99L |
| 2 | 06/06/2001 | 1 | 105L |
| 3 | 06/04/2001 | 2 | 150L |
| 4 | 06/07/2001 | 2 | 167L |
+----+------------+------------+-------+

The tax code table shows on what date a particular tax code applies for
an employee.

Once a tax code applies then earlier tax codes for that employee do not
apply.

So in the example above

Joe Bloggs has tax code 99L from 06/04/2001 until 06/06/2001 when he
then has tax code 105L

Similarly Bill Smith has tax code 150L from 06/04/2001 until 06/07/2001
when tax code 167L applies.

When working out the tax owed by an employee on each pay day I need to
determine which tax code is applicable.

This is where my problems lie.

What I think I want to query for is the tax code with the highest date
that is less than or equal to the PayRecords.Date field and that the
tax code and pay record employee fields are equal :-

SELECT PayRecords.Employee, PayRecords.Date,
PayRecords.PayTaxCodes.Code
FROM PayRecords, TaxCodes

WHERE (Max(TaxCodes.Date) <= PayRecords.Date) AND (PayRecords.Employee
= TaxCodes.Employee);

This gives me an error because you cannot have an aggregate function in
a WHERE clause.

After some research on the web I tried the following

SELECT PayRecords.Employee, PayRecords.Date, PayRecords.Pay,
TaxCodes.Code, TaxCodes.Date
FROM PayRecords, TaxCodes

WHERE PayRecords.Date >= (select Max(TaxCodes.Date) As TaxCodesDate
FROM TaxCodes WHERE (PayRecords.Employee = TaxCodes.Employee))

ORDER BY PayRecords.Employee, PayRecords.Date;
This query runs but it gives multple instances of the same payrecord
with different tax codes, some of which have dates later than the
payrecord's date. It seems to be ignoring the >= test

Does anybody know what I am doing wrong ?

I would be most grateful for any help people can offer.


regards
Darran

[David Seeto via AccessMonster.com]

Hi Darran,

You've posted a lot of detail, which has made it a lot easier to debug what
you're trying to do - thanks!

As a first up comment, you should rename the fields you've called Date to
more specific things like PayDate and TaxDate - "Date" has a particular
meaning in VBA in particular, which can cuase you difficulties later on
down the track.

OK, the reason you're getting multiple records is because your WHERE clause
only joins PayRecords.Date with the returned values, irrespective of what
Employee they apply to - I don't know of any way to use the subquery to
check on more than one field, so my recommendation is to create some new
queries that you can use to get what you need. This may be covered in
theory somewhere else, but maybe it will be helpful

Firstly, to get all of the pay records with all of the tax codes that come
into effect after the pay period:
PayAndLaterTax
SELECT PayRecords.Id, PayRecords.Employee, PayRecords.PayDate,
PayRecords.Pay, TaxCodes.TaxDate, TaxCodes.Code
FROM PayRecords INNER JOIN TaxCodes ON PayRecords.Employee =
TaxCodes.Employee
WHERE (((TaxCodes.TaxDate)>[PayRecords].[PayDate]));

We can then join this to the original TaxCode table to figure out what the
ones less than the Pay Date are:
PayAndEarlierTax
SELECT PayAndLaterTax.Id, PayAndLaterTax.Employee, PayAndLaterTax.PayDate,
PayAndLaterTax.Pay, TaxCodes.TaxDate, TaxCodes.Code
FROM PayAndLaterTax INNER JOIN TaxCodes ON PayAndLaterTax.Employee =
TaxCodes.Employee
WHERE (((TaxCodes.TaxDate)<[PayAndLaterTax].[TaxDate]));

In your example, this would be enough: however, this query returns ALL of
the Tax Codes that start before the date you want - you actually want the
biggest one of these dates, for which you need another two queries:
PayAndEarlierTaxMax
SELECT PayAndEarlierTax.Employee, Max(PayAndEarlierTax.TaxDate) AS
MaxOfTaxDate
FROM PayAndEarlierTax
GROUP BY PayAndEarlierTax.Employee;

And then use this Date to join back to your other query:
PayAndApplicableTax
SELECT PayAndEarlierTax.*
FROM PayAndEarlierTax INNER JOIN PayAndEarlierTaxMax ON
(PayAndEarlierTax.TaxDate = PayAndEarlierTaxMax.MaxOfTaxDate) AND
(PayAndEarlierTax.Employee = PayAndEarlierTaxMax.Employee);

I may have gotten this last query wrong - I'm not 100% sure that joining on
the TaxDates alone is enough, but give it a go on a bigger sample set and
let me know.

Cheers,
David...

--
Message posted via http://www.accessmonster.com