473,698 Members | 2,943 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with sub queries in Access

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.Empl oyee, PayRecords.Date ,
PayRecords.PayT axCodes.Code
FROM PayRecords, TaxCodes

WHERE (Max(TaxCodes.D ate) <= PayRecords.Date ) AND (PayRecords.Emp loyee
= TaxCodes.Employ ee);

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.Empl oyee, PayRecords.Date , PayRecords.Pay,
TaxCodes.Code, TaxCodes.Date
FROM PayRecords, TaxCodes

WHERE PayRecords.Date >= (select Max(TaxCodes.Da te) As TaxCodesDate
FROM TaxCodes WHERE (PayRecords.Emp loyee = TaxCodes.Employ ee))

ORDER BY PayRecords.Empl oyee, 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

Nov 13 '05 #1
1 1734
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.Empl oyee, PayRecords.PayD ate,
PayRecords.Pay, TaxCodes.TaxDat e, TaxCodes.Code
FROM PayRecords INNER JOIN TaxCodes ON PayRecords.Empl oyee =
TaxCodes.Employ ee
WHERE (((TaxCodes.Tax Date)>[PayRecords].[PayDate]));

We can then join this to the original TaxCode table to figure out what the
ones less than the Pay Date are:
PayAndEarlierTa x
SELECT PayAndLaterTax. Id, PayAndLaterTax. Employee, PayAndLaterTax. PayDate,
PayAndLaterTax. Pay, TaxCodes.TaxDat e, TaxCodes.Code
FROM PayAndLaterTax INNER JOIN TaxCodes ON PayAndLaterTax. Employee =
TaxCodes.Employ ee
WHERE (((TaxCodes.Tax Date)<[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:
PayAndEarlierTa xMax
SELECT PayAndEarlierTa x.Employee, Max(PayAndEarli erTax.TaxDate) AS
MaxOfTaxDate
FROM PayAndEarlierTa x
GROUP BY PayAndEarlierTa x.Employee;

And then use this Date to join back to your other query:
PayAndApplicabl eTax
SELECT PayAndEarlierTa x.*
FROM PayAndEarlierTa x INNER JOIN PayAndEarlierTa xMax ON
(PayAndEarlierT ax.TaxDate = PayAndEarlierTa xMax.MaxOfTaxDa te) AND
(PayAndEarlierT ax.Employee = PayAndEarlierTa xMax.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
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
1915
by: JMorrell | last post by:
First post to this community so am not sure if this is the correct place. Here goes. I have a MS Access db that keeps track of employees sick and annual leave balances. In it, I have a report, built around a query, that lists everyone's leave balances. There are columns (fields) that are generated with nested IIF statements. Everything there works like it should and the results are accurate. Now, I'm wanting to put this SQL query...
4
3557
by: John Smith | last post by:
Isn't life a bitch! You know what you want but you don't know how to get it. I have produced 12 queries that calculate a payment profile over 12 months. For a number of the records (ie with the same product id)there are likely to be more than one payment recorded. Nonetheless, when I run the queries individually and the query relates to a table that provides referential integrity through a one to many relationship, the query does what...
8
4039
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked table in access, the total number of records is OK but some records appear several times and some records do not appear at all. It seems as if access or the ODBC drivers returns several times the same record and skips some of the records, curiosly...
1
1626
by: christopher_mouse | last post by:
I have a split Access 97 database that I've been asked to secure. The data is sensitive and users include programmers who are routinely accessing the data directly, so I plan to remove all permissions from the tables in the backend and then create RWOP queries to retrieve all the fields in the tables so users are limited to the column/row access that I design in these queries. I also plan to remove permissions for users to create new...
10
2131
by: Saso Zagoranski | last post by:
hi, this is not actually a C# problem but since this is the only newsgroup I follow I decided to post my question here (please tell me where to post this next time if you think this post shouldn't be here). I have two design questions: 1. what is the correct (or best) way to include database queries into the code if you plan on
0
1316
by: kkrizl | last post by:
I've tried to research this problem, and I haven't been able to find any references to it. Probably because I shouldn't be doing it, but it was working, and now it's not. I'm trying to develop a small application to run on our intranet. I have a lot of old knowledge, and not a lot off access to new knowledge, so I'm shooting from the hip and fumbling. I have a small Access database that I'm using as a back end for some ..ASP pages. ...
1
1750
by: kkrizl | last post by:
I've tried to research this problem, and I haven't been able to find any references to it. Probably because I shouldn't be doing it, but it was working, and now it's not. I'm trying to develop a small application to run on our intranet. I have a lot of old knowledge, and not a lot off access to new knowledge, so I'm shooting from the hip and fumbling. I have a small Access database that I'm using as a back end for some ..ASP pages. ...
1
15214
by: eblackmo | last post by:
I have a test network consisting of four servers running windows 2003 server R2 SP2. I have set up a domain which functioned correctly for about a day and a half until the other servers decided they can no longer connect to the domain controller. Ping resolves the domain controllers name to the correct ip nslookup finds the ip but can not resolve the name. When I attempt to log on to a machine on the domain it takes around twenty minutes...
3
2554
prn
by: prn | last post by:
Hi folks, I'm trying to create letters based on an Access database. I'm using Access 2003 and Word 2003. Most of the data tables are actually on a SQL server and Access is the front end for them. I have the boilerplate for my letter ready. Now, with Access open and connected to the SQL server, I open Word with the file I want, click "Tools", "Letters and Mailings" and select MailMerge. Word allows me to select the document type, with...
13
1844
by: harshakusam | last post by:
Hi All, MDB file with 110 passthru queries Run each query and save it in excel.. I had already written VBcode.... my code works fine for normal queries.. but all my queries using case, substr, and sooo.. so if you see access won't understand case, substr.. if you want access to run such queries then you have to make them as passthru queries.. then the query will work fine.. if i want same query to run thru VB it's throwing error (sytax...
0
8683
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8610
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8902
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8873
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7740
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6528
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5862
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4372
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4623
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.