Hi,
I take it that your data is laid out similar to the following in one table:
EmployeeID HoursNotWorked Code
1 10 v
1 40 v
1 15 p
1 20 o
2 30 v
2 25 o
3 30 v
3 60 v
In another Table I guess you have something like:
EmployeeID AvailableHours
1 200
2 200
3 300
Then I would create a query based on the first table that would give me the
following result
EmployeeID HoursNotWorked
1 50
2 30
3 90
SQL Syntaxt used:
SELECT EmployeeID, Sum(HoursNotWorked) AS TotalTaken
FROM [your table]
GROUP BY EmployeeID
WHERE Code = 'v';
Then create another Query that would INNER Join the above query with the
2nd table I noted, example:
EmployeeID TotalTaken AvailableHours
RemainHours([AvailableHours]-[TotalTaken])
1 50 200
150
2 30 200
170
3 90 300
210
SQL Syntax used:
SELECT Table2.EmployeeID, Table2.AvailableHours,
Query1.TotalTaken, [AvailableHours]-[TotalTaken] AS RemainHours
FROM Table2 INNER JOIN Query1 ON Table2.EmployeeID = Query1.EmployeeID;
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| Content-Class: urn:content-classes:message
| From: <
[email protected]>
| Sender: <
[email protected]>
| Subject: Calculation of field
| Date: Thu, 10 Jun 2004 05:46:52 -0700
| Lines: 10
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcRO6QDm89ApStglRRifo4TZZdqFNA==
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.tablesdbdesign:80485
| NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| Hello,
|
| I created a DB to monitor employee hours and i want to be
| able to calculate how many hours an employee has left for
| vacation.For example, 10 employee with each having 200
| hours a year for vacation.How would the calculation work
| in reducing each employees 200 hours as they are taken?I
| already have a field for (hours not worked) and a drop
| down field for the codes(v for vacation,p for personal or
| o for other.Any help would be appreciated...thanks
|
