Dates

[Phil Stanton]

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

[Ed Robichaud]

Re: Dates

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.PaidAmou nt]/[tblRate.WageRat e] ), assuming your wage rate is $
per hour.

-Ed



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

>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
>

[CDMAPoster@FortuneJames.com]

Re: Dates

Phil Stanton wrote:

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

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/microsoft.public.access/browse_frm/thread/3a0befa85b47ec0/61953ab4a3911d21

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.Pay Amount / (SELECT A.WageRate FROM tblWageRates
AS A WHERE A.EmpID = tblPayments.Emp ID AND A.DatePaid = (SELECT
Max(B.DatePaid) FROM tblWageRates AS B WHERE B.EmpID =
tblPayments.Emp ID AND B.RateFrom <= tblPayments.Dat ePaid)) AS
CalculatedRate, (SELECT A.EmpName FROM tblEmployees AS A WHERE A.EmpID
= tblPayments.Emp ID) 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@Fort uneJames.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

[Phil Stanton]

Re: Dates

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@For tuneJames.comwr ote in message
news:1163482235 .954930.97210@m 73g2000cwd.goog legroups.com...

Phil Stanton wrote:

>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

>
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/microsoft.public.access/browse_frm/thread/3a0befa85b47ec0/61953ab4a3911d21

>
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.Pay Amount / (SELECT A.WageRate FROM tblWageRates
AS A WHERE A.EmpID = tblPayments.Emp ID AND A.DatePaid = (SELECT
Max(B.DatePaid) FROM tblWageRates AS B WHERE B.EmpID =
tblPayments.Emp ID AND B.RateFrom <= tblPayments.Dat ePaid)) AS
CalculatedRate, (SELECT A.EmpName FROM tblEmployees AS A WHERE A.EmpID
= tblPayments.Emp ID) 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@Fort uneJames.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
>

[Phil Stanton]

Re: Dates

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@wd n.comwrote in message
news:45594220$0 $5789$2ff6ac69@ news.wdn.com...

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.PaidAmou nt]/[tblRate.WageRat e] ), assuming your wage rate is
$ per hour.
>
-Ed
>
>
>
"Phil Stanton" <phil@stantonfa mily.co.ukwrote in message
news:4558e5a0$0 $8748$ed2619ec@ ptn-nntp-reader02.plus.n et...

>>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
>RateFrom (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
>>

>
>

[Keith Wilby]

Re: Dates

"Phil Stanton" <phil@stantonfa mily.co.ukwrote in message
news:455990ef$0 $8737$ed2619ec@ ptn-nntp-reader02.plus.n et...

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
>

A & B are aliases for the tables. You would normally use this notation in
Oracle queries. Try removing the aliases.

Regards,
Keith.

[breadon]

Re: Dates

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.

[CDMAPoster@FortuneJames.com]

Re: Dates

Phil Stanton wrote:

Thanks for your help, Ed

Ed has left the building :-).

>
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@Fort uneJames.com

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

[Phil Stanton]

Re: Dates

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.Employ eeName, Wages.*,
WageToTime([Wages!EmployeeI D],[DatePaid],[Wage]) AS Hours,
round(IIf([Hours]<>0,[Wage]/[Hours]),2) AS Rate
FROM (Employee LEFT JOIN WageRates ON Employee.Employ eeID =
WageRates.Emplo yeeID) RIGHT JOIN Wages ON Employee.Employ eeID =
Wages.EmployeeI D
ORDER BY Employee.Employ eeName, Wages.DatePaid;

Option Compare Database
Option Explicit
Function WageToTime(EmpI D 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.OpenRecord set(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@For tuneJames.comwr ote in message
news:1163536739 .867191.81380@k 70g2000cwa.goog legroups.com...

Phil Stanton wrote:

>Thanks for your help, Ed

>
Ed has left the building :-).
>

>>
>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@Fort uneJames.com
>
You can't do karaoke in Memphis without knowing Elvis. -- Hawk
>