most recent date

[Guest]

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)

 

[Ken Snell [MVP]]

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>

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)

 

[Guest]

thanks that helped a bunch!