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
|
|||
|
|||
most recent date
ok I've seen tons of answers to this question but none of them really work for my purpose, or I just simply don't understand.
Here's what I'm trying to do: I have a table named login; the fields are ID, first_name, last_name... I have another table named tb; the fields are ID, tb_ID, and last_tbdate. Here's the relationship: ID.login ---------- tb_ID.tb The ID fields in both tables are unique key autonumber. The tb table is used to record the tb test date of each employee. Every employee has to have a tb test every year. I have been trying to create a query that will tell me when each person is due for another tb test, within a 45 day period (30 days before and 15 days after the current date) EX. login: ID first_name last_name 1 jeremy scott 2 john doe 3 jane michaels 4 brian peterson 5 bill gates EX. tb ID tb_ID last_tbdate 1 1 09/02/2003 2 1 09/01/2002 3 3 09/05/2004 4 5 09/10/2003 5 5 09/10/2002 The results should show: jeremy scott 09/02/2003 bill gates 09/10/2003 I have tried everything under the sun and can't get it to work Thanks, any help is greatly appreciated. (please keep in mind that the query is on in a web page recordset not in access it self) |
#2
|
|||
|
|||
Perhaps this will get you started. This is a query that will return the
person's name and the most recent date in the second table: SELECT login.first_name, login.last_name, Max(tb.last_tbdate) FROM login INNER JOIN tb ON login.ID = tb.tb_ID GROUP BY login.first_name, login.last_name; You can then apply a criterion expression to the Max(tb.last_tbdate) field to filter to just the ones you want based on your date range. -- Ken Snell MS ACCESS MVP wrote in message ink.net... ok I've seen tons of answers to this question but none of them really work for my purpose, or I just simply don't understand. Here's what I'm trying to do: I have a table named login; the fields are ID, first_name, last_name... I have another table named tb; the fields are ID, tb_ID, and last_tbdate. Here's the relationship: ID.login ---------- tb_ID.tb The ID fields in both tables are unique key autonumber. The tb table is used to record the tb test date of each employee. Every employee has to have a tb test every year. I have been trying to create a query that will tell me when each person is due for another tb test, within a 45 day period (30 days before and 15 days after the current date) EX. login: ID first_name last_name 1 jeremy scott 2 john doe 3 jane michaels 4 brian peterson 5 bill gates EX. tb ID tb_ID last_tbdate 1 1 09/02/2003 2 1 09/01/2002 3 3 09/05/2004 4 5 09/10/2003 5 5 09/10/2002 The results should show: jeremy scott 09/02/2003 bill gates 09/10/2003 I have tried everything under the sun and can't get it to work Thanks, any help is greatly appreciated. (please keep in mind that the query is on in a web page recordset not in access it self) |
#3
|
|||
|
|||
thanks that helped a bunch!
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
If statement | Doug | Worksheet Functions | 9 | June 28th, 2004 06:13 AM |
more dates!!! | brigid | Running & Setting Up Queries | 6 | May 26th, 2004 10:59 AM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |