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...
Quote:
Phil Stanton wrote:
Quote:
>Thanks for your help, Ed
>
Ed has left the building :-).
>
Quote:
>>
>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
>
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
>
