If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
PLEASE NOTE: THIS IS A RE-POST
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 |
#3
|
|||
|
|||
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). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "LPC" wrote in message ... PLEASE NOTE: THIS IS A RE-POST 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 |
#4
|
|||
|
|||
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 "Douglas J. Steele" wrote: 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). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "LPC" wrote in message ... PLEASE NOTE: THIS IS A RE-POST 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 |
#5
|
|||
|
|||
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....artAccess.html) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "LPC" wrote in message ... 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 "Douglas J. Steele" wrote: 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). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "LPC" wrote in message ... PLEASE NOTE: THIS IS A RE-POST 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Date Range | Marie | General Discussion | 3 | December 28th, 2004 05:46 PM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 03:19 PM |
Date Range (year from entered date) | accessquestion | Running & Setting Up Queries | 4 | December 10th, 2004 07:01 PM |
Report with date range selection | Marv Trott | Setting Up & Running Reports | 9 | November 5th, 2004 04:28 PM |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |