Dates

I am being thick.

I have a table of employees - EmpID & EmpName
I have a table or wage rates for each employee - RateID, EmpID, WageRate
RateFron (Date)
I have a table of payments - PayID, EmpID PayAmount, DatePaid
how on earth do I do a query to give me EmpName, DatePaid, HoursWorked.
The Hours worked is calculated from the PayAmont and the appropriate
WageRate at that date. Somehow have to find the rate at any date

TIA

Phil

Unless I'm missing something, you would create a totals query with all 3
tables (group on EmpID and DatePaid), and add a calculated field (
hours:[tblPay.PaidAmount]/[tblRate.WageRate] ), assuming your wage rate is $
per hour.

-Ed



"Phil Stanton" <phil@stantonfamily.co.ukwrote in message
news:4558e5a0$0$8748$ed2619ec@ptn-nntp-reader02.plus.net...

Phil Stanton wrote:The problem is thick. This seemingly simple SQL problem can become a
hydra-headed monster. An example of where it can lead can be found
here:

http://groups.google.com/group/micro...953ab4a3911d21

Until the time that Access catches up with the SQL3 standard, allowing
for an elegant solution, employees seem bothered by the possibility of
not getting raises because the queries can't handle time-varying rates.
I think you can start with something like (air code):

SELECT *, tblPayments.PayAmount / (SELECT A.WageRate FROM tblWageRates
AS A WHERE A.EmpID = tblPayments.EmpID AND A.DatePaid = (SELECT
Max(B.DatePaid) FROM tblWageRates AS B WHERE B.EmpID =
tblPayments.EmpID AND B.RateFrom <= tblPayments.DatePaid)) AS
CalculatedRate, (SELECT A.EmpName FROM tblEmployees AS A WHERE A.EmpID
= tblPayments.EmpID) AS EmpName FROM tblPayments;

You can do a totals query based on that query to sum PayAmount (maybe
group by EmpID and DatePaid) or try to adjust that query directly. The
EmpName also could have been obtained using a join.

James A. Fortune
CDMAPoster@FortuneJames.com

The cure for this ill is not to sit still,
Or frowst with a book by the fire;
But to take a large hoe and a shovel also,
And dig till you gently perspire. -- Kipling

Thanks for your help, Ed

The query comes up with asking for the paramaters of B.DatePaid and
A.DatePaid which is totally confusing me. Can you explain the A & B

Thanks

Phil


<CDMAPoster@FortuneJames.comwrote in message
news:1163482235.954930.97210@m73g2000cwd.googlegro ups.com...

Thanks Ed

Yes I'm afraid you are missing the point. The problem is finding the correct
WageRate on the date the payment was made. So for example if Jo is paid £10
/ hour from Jan 1 2005 to Dec 31 2005 and earns £100 a week, he is working
for 10 hours each week. If his pay goes up to £11 an hour from Jan 1 2006
and he continues to earn £100 per week, he is only working for hust over 9
hours per week. It is how you find his wage rate when he was paid on July 20
2005 that is the problem

Phil


"Ed Robichaud" <edrobichaud@wdn.comwrote in message
news:45594220$0$5789$2ff6ac69@news.wdn.com...

"Phil Stanton" <phil@stantonfamily.co.ukwrote in message
news:455990ef$0$8737$ed2619ec@ptn-nntp-reader02.plus.net...A & B are aliases for the tables. You would normally use this notation in
Oracle queries. Try removing the aliases.

Regards,
Keith.

Pragmatically, I would add a 'to date' to your rates table. Then you
can join using a between clause/criteria. Yes, I know that 'to date'
is denormalised and that the between query won't be fast. However it
will be faster (and simpler) than trying to derive the 'to date' on the
fly using subqueries plus you can add some data integrity.

For example, apply schema constraints/validation to:

* Restrict 'from date' to (say) Saturdays and the 'to date' to (say)
Fridays
* Ensure that the from date is less than the to date.
* Ensure that the time elements of the date (hours, minutes and
seconds) are all zero.
* You possibly need to have the 'to date' accept nulls and thus will
need to handle it very carefully in all queries. (The from date should
not need to accept nulls?)
* Add a unique index the substitutes the 'to date' for the 'from date'
in the unique index that you probably have already. You should have a
pair of unique indexes. (Access seems to handle composite unique
indexes that admit nulls in one field a little bit differently - check
out what benefit you get from the suggested unique index carefully.)

I would also suggest a report that will check for 'missing' time
periods ie where there is no 'to date' + 1 day row correspoining to
rows that have a not null 'to date'. You may have to construct this
'Missing' type query your self - not sure the wizard will handle it.

Phil Stanton wrote:Ed has left the building :-).
I use the letters to indicate that a subquery or a subquery within a
subquery is being used even if the letters aren't required. I use the
letter A to alias the table in a normal subquery so that it's easier to
see where the data from the main query gets substituted. I use the
letter B for a subquery within a subquery to indicate that the results
from that subquery within a subquery are used in the initial subquery.
The DMax function could have been used in place of the subquery
containing the B's. The important thing to understand about subqueries
(and the DMax function) is that they aren't aware of the WHERE part of
the main query or of a surrounding query. I.e., they stand on their
own. So it's sometimes necessary to pull in values from a surrounding
query to help restrict the WHERE condition properly. I use this syntax
often in Access. Check to make sure you've got the same table and
field names. BTW, I've never used Oracle. The main problem is that if
you don't understand how to use subqueries, you won't be able to go
anywhere with the SQL I supplied. You may have to try breadon's
suggestions or denormalize slightly since to do this problem the
"right" way will be a real challange and adding new capabilities to the
query later will get even more hirsute.

James A. Fortune
CDMAPoster@FortuneJames.com

You can't do karaoke in Memphis without knowing Elvis. -- Hawk

Whoops - sorry about the name James. Had a reply from Ed as well

Thanks also for your help,

In the end I found it was simpler to write a bit of code, but I will play
around with your method to get more familiar with sub-queries

Thanks again

Phil


SELECT DISTINCT Employee.EmployeeName, Wages.*,
WageToTime([Wages!EmployeeID],[DatePaid],[Wage]) AS Hours,
round(IIf([Hours]<>0,[Wage]/[Hours]),2) AS Rate
FROM (Employee LEFT JOIN WageRates ON Employee.EmployeeID =
WageRates.EmployeeID) RIGHT JOIN Wages ON Employee.EmployeeID =
Wages.EmployeeID
ORDER BY Employee.EmployeeName, Wages.DatePaid;

Option Compare Database
Option Explicit
Function WageToTime(EmpID As Long, DatePd As Date, Wage As Currency) As
Double

Dim MyDb As Database
Dim RatesSet As Recordset
Dim FirstDate As Date
Dim Rate As Currency
Dim HrsWorked As Single
Dim SQLStg As String

SQLStg = "SELECT WageRates.* FROM WageRates WHERE EmployeeID = "
SQLStg = SQLStg & EmpID
SQLStg = SQLStg & " ORDER BY Date DESC;"

Set MyDb = CurrentDb
Set RatesSet = MyDb.OpenRecordset(SQLStg)

On Error GoTo WageToTime_Err

With RatesSet
.MoveFirst
If Nz(!Date) = 0 Then
FirstDate = #12/31/2999#
Else
FirstDate = !Date
End If

CheckRate:
If DatePd >= FirstDate Then ' No initial date
Rate = !WageRate
WageToTime = Round(Wage / Rate, 2)
Debug.Print WageToTime
.Close
Set RatesSet = Nothing
Exit Function
Else
.MoveNext
If .EOF Then
WageToTime = 0
.Close
Set RatesSet = Nothing
Exit Function
End If
FirstDate = !Date
GoTo CheckRate
End If
End With
Set RatesSet = Nothing

Exit Function

WageToTime_Err:
If Err = 3021 Then ' no wage rate
WageToTime = 0
RatesSet.Close
Set RatesSet = Nothing
Else
MsgBox Err.Description
End If

End Function


<CDMAPoster@FortuneJames.comwrote in message
news:1163536739.867191.81380@k70g2000cwa.googlegro ups.com...