A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to retrieve a record that falls in a date range



 
 
Thread Tools Display Modes
  #1  
Old January 7th, 2005, 03:17 PM
LPC
external usenet poster
 
Posts: n/a
Default 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  
Old January 12th, 2005, 07:35 AM
LPC
external usenet poster
 
Posts: n/a
Default

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  
Old January 13th, 2005, 12:16 AM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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  
Old January 15th, 2005, 09:43 AM
LPC
external usenet poster
 
Posts: n/a
Default

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  
Old January 15th, 2005, 01:26 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 05:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.