query before date

[Regan via AccessMonster.com]

hi i have a query that gets fields from different tables. one of the tables
is tblPaychanges. where if and employee gets pay changes it is recorded. it
records the date of the change and the change itself.

Now in the query io want to have a column [PayRate].

how do i make a query so that the payRate before a certain date is 100 but
not after that date

Table Paychanges

PayID EmployeeID PayDateChange Rate
1 8 31/05/06 15
2 8 11/07/06 20
3 9 18/07/06 21

i want it so in the Rate column in my query for EMployee 8 has 15 before date
and 20 after that date. Here is my query so far. any ideas on how i can
icorpirate what i want to do

SELECT DISTINCTROW Format$(tblTimeSheet.Date,'ww',4) AS WeekEnding,
tblTimeSheet.Employee, Sum(tblTimeSheet.Units) AS [Sum of units]
FROM tblEmployees INNER JOIN tblTimeSheet ON tblEmployees.
EmployeeID=tblTimeSheet.Employee
WHERE (((tblTimeSheet.Date)>=forms!FrmSwitchboard![1sttsent] And
(tblTimeSheet.Date)<=Forms!FrmSwitchboard!wkedng And (tblTimeSheet.Date)

=tblEmployees.AutopayStDate) And (((tblTimeSheet.Code))<>23 And (

(tblTimeSheet.Code))<>24 And ((tblTimeSheet.Code))<>25) And ((tblTimeSheet.
Employee)=forms!FrmWageBal!Employee))
GROUP BY Format$(tblTimeSheet.Date,'ww',4), tblTimeSheet.Employee;

I hope i'm making sense.


Thanks Regan

 

[Guest]

Regan:

Add a subquery to the query's SELECT clause so that it returns the rate for
the latest date before the date in the current row. Also it’s a good idea to
always declare date/time parameters in a query as otherwise a date parameter
in short date format can be interpreted by Access as an arithmetical
expression and give the wrong results. Taking both these factors into
account, and assuming the Employee column is of text data type, your query
would go like this:

PARAMETERS
Forms!FrmSwitchboard!wkedng DATETIME,
Forms!FrmWageBal!Employee TEXT;
SELECT
FORMAT(tblTimeSheet.Date,'ww',4) AS WeekEnding,
tblTimeSheet.Employee,
SUM(tblTimeSheet.Units) AS [Sum of units],
(SELECT P1.Rate
FROM PayChanges AS P1
WHERE P1.EmployeeID = tblEmployees.EmployeeID
AND P1. PayDateChange =
(SELECT MAX(PayDateChange)
FROM PayChanges AS P2
WHERE P2.EmployeeID = P1.EmployeeID
AND P2.PayDateChange <= tblTimeSheet.Date))
AS PayRate
FROM tblEmployees etc.

BTW I'd avoid using Date as a column name. It could be confused with the
built in Date function. Use something like PaymentDate to avoid any possible
confusion.

Ken Sheridan
Stafford, England

hi i have a query that gets fields from different tables. one of the tables
is tblPaychanges. where if and employee gets pay changes it is recorded. it
records the date of the change and the change itself.

Now in the query io want to have a column [PayRate].

how do i make a query so that the payRate before a certain date is 100 but
not after that date

Table Paychanges

PayID EmployeeID PayDateChange Rate
1 8 31/05/06 15
2 8 11/07/06 20
3 9 18/07/06 21

i want it so in the Rate column in my query for EMployee 8 has 15 before date
and 20 after that date. Here is my query so far. any ideas on how i can
icorpirate what i want to do

SELECT DISTINCTROW Format$(tblTimeSheet.Date,'ww',4) AS WeekEnding,
tblTimeSheet.Employee, Sum(tblTimeSheet.Units) AS [Sum of units]
FROM tblEmployees INNER JOIN tblTimeSheet ON tblEmployees.
EmployeeID=tblTimeSheet.Employee
WHERE (((tblTimeSheet.Date)>=forms!FrmSwitchboard![1sttsent] And
(tblTimeSheet.Date)<=Forms!FrmSwitchboard!wkedng And (tblTimeSheet.Date)

=tblEmployees.AutopayStDate) And (((tblTimeSheet.Code))<>23 And (

(tblTimeSheet.Code))<>24 And ((tblTimeSheet.Code))<>25) And ((tblTimeSheet.
Employee)=forms!FrmWageBal!Employee))
GROUP BY Format$(tblTimeSheet.Date,'ww',4), tblTimeSheet.Employee;

I hope i'm making sense.


Thanks Regan