how to retrieve a record that falls in a date range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables, one containing information about the staff of an office
(names, address, tel, etc.), and the other one containing their salary
history (StaffID, salary, date of salary modification). The 2 tables are
related by the field StaffID. Salaries for each staff are not fixed but can
be modified with time. For example, in Table Staffs, I have 2 persons, Mark
(ID=1) and Nelly (ID=2). In table Salary History, Mark ´s salary starts at
500 USD on 1-1-98, but on 1-6-99 has been increased to 600, and on 1-8-99,
modified again to 750 USD. Nelly, on the other hand, receives a salary of 650
USD as of 15-7-1998, but from 1-1-2000, this has been increased to 820.
Suppose we have to calculate their salaries for the month of July 1999.
Clearly we see that Mark´s salary for that month is of 600, and Nelly ´s is
750 (starting point), but I do not know how to create an expression that can
tell Access to retrieve this record by date comparison (July 1999 is greater
than 1-1-1998 and 1-6-1999, but smaller than 1-8-1999, so Access SHOULD
retrieve the record of 600 USD, corresponding to Mark ´s 1-6-99 ´s salary
modification, which is the most RECENT modification for that date range. The
same logic is for Nelly ´s salary). Please help me to give a solution to my
obstacle. Your valuable help would be much appreciated.

Luu Phuong Chi
Access beginner
 
PLEASE NOTE: THIS IS A RE-POST

RE: how to retrieve a record that falls in a date range

I have 2 tables, one containing information about the staff of an office
(names, address, tel, etc.), and the other one containing their salary
history (StaffID, salary, date of salary modification). The 2 tables are
related by the field StaffID. Salaries for each staff are not fixed but can
be modified with time. For example, in Table Staffs, I have 2 persons, Mark
(ID=1) and Nelly (ID=2). In table Salary History, Mark ´s salary starts at
500 USD on 1-1-98, but on 1-6-99 has been increased to 600, and on 1-8-99,
modified again to 750 USD. Nelly, on the other hand, receives a salary of 650
USD as of 15-7-1998, but from 1-1-2000, this has been increased to 820.
Suppose we have to calculate their salaries for the month of July 1999.
Clearly we see that Mark´s salary for that month is of 600, and Nelly ´s is
750 (starting point), but I do not know how to create an expression that can
tell Access to retrieve this record by date comparison (July 1999 is greater
than 1-1-1998 and 1-6-1999, but smaller than 1-8-1999, so Access SHOULD
retrieve the record of 600 USD, corresponding to Mark ´s 1-6-99 ´s salary
modification, which is the most RECENT modification for that date range. The
same logic is for Nelly ´s salary). Please help me to give a solution to my
obstacle. Your valuable help would be much appreciated.

Luu Phuong Chi
Access beginner
 
One problem you may be having is that in SQL statements, Access will not
recognize dates in dd/mm/yyyy format unless the day is greater than 12
(since there isn't a 13th month).
 
Hi, could you please explain it in an easier way? I cann´t understand the
relationship between the need to have the DAY greater than 12 while the
answer is about MONTH. It sounds too confusing to me. Anyway, as I don´t even
have the SQL written (that ´s why I posted the question before), I´d
appreciate your help in giving me a possible syntax (I´m only accustomed to
simple queries and macros, not VBA).
Thanks
LPC
 
The point I was trying to make is that you must use mm/dd/yyyy date format
in your queries. Regardless of what your regional settings are, Access will
not recognize dates in dd/mm/yyyy format. The exception to that is when the
day in the date is greater than 12: Access will recognize 15/1/2005 as 15
Jan, 2005, because there is no 15th month. It will always, however,
recognize 12/1/2005 as 1 Dec, 2005, never as 12 Jan, 2005.

For more information about working with International Dates, see Allen
Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at
http://members.rogers.com/douglas.j.steele/SmartAccess.html)
 
Back
Top