Query that sorts the Most current Date

[tomslacks]

Hello All,

I have two tables that I am trying to join in MS Access and sort by a date and only return the most current date.

Table1
JobNO Date Comment
1 4/2/03 yes
1 4/3/03 no
2 4/4/03 yes
3 4/4/03 no
3 4/5/03 yes

Table2
JobNo Client City
1 MS NY
2 GTE LA
3 ATT NY

SO my final return on my query will look something like this:

JobNO Client City Comment Date
1 MS NY no 4/3/03
2 GTE LA yes 4/4/03
3 ATT NY yes 4/5/03

I am trying to create a join that will return the most current comment for each jobNo and list the client, city. There are no duplicates dates for each jobNo in Table 1. I have been trying the max date function but I get the max for the entire table and it only returns one row, I am unclear how to use the function using the JobNo as a qualifier.

Any direction would a great help

 

[DrSimon]

You need to do this as two queries.
In the first you use Table 1 and group by JobNo and get Max([Date]). Save that query and use that as the key to join the two tables.
Simon Rouse

 

[swampBoogie]

select t1.jobno , client, city, comment, [date]
from table1 t1 join table2 t2 on t1.jobNo = t2.jobNo
and [DATE] = (select max([DATE]) from table1
where jobNo = t1.jobNo)

 

[tomslacks]

I have tried your query and keep getting an error "Syntax error. in expression". and when I click ok it sends me to the t1.JobNo = t2.JobNo section.

SELECT t1.JobNo, t2.Client, t2.City, t1.Date, t1.Comment
FROM Table1 t1 inner JOIN Table2 t2 ON t1.JobNo = t2.JobNo
and [Date] = (select amx([Date]) from Table1 where t2.JobNo = t1.JobNo;


I dont see an error in the syntax and have debuggin and havent had an luck.

 

[tomslacks]

SELECT t1.JobNo, t2.Client, t2.City, t1.Date, t1.Comment
FROM Table1 t1 inner JOIN Table2 t2 ON t1.JobNo = t2.JobNo
and [Date] = (select max([Date]) from Table1 where t2.JobNo = t1.JobNo;


the statement I using is this, I spelled max wrong in the first post, I still get the same error with everything spelled right

 

[dodge20]

You are missing your closing parenthesis at the very end of the statement.

Dodge20

 

[tomslacks]

even if i add the parenthesis I still get the same error. The message box doesnt have the line with the error line like a syntax error normally has.

 

[swampBoogie]

Maybe

SELECT t1.JobNo, t2.Client, t2.City, t1.Date, t1.Comment
FROM Table1 t1 inner JOIN Table2 t2 ON t1.JobNo = t2.JobNo
where [Date] = (select max([Date]) from Table1 where t2.JobNo = t1.JobNo)

?

 

[tomslacks]

ok, this is my max(date) query Call qryMaxDate

SELECT t2.JOBNO, Max(t1.CDATE) AS LastCommentDate
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.JOBNO=t2.JOBNO
GROUP BY t2.JOBNO;

Then this to bring it all together

SELECT t1.JobNo, t2.Client, t2.City, qryMaxDate.LastCommentDate, t1.Comment
FROM Table1 t1 INNER JOIN Table2 t2 ON(t1.JOBNO = t2.JOBNO) INNER JOIN qryMaxDate ON (t1.JOBNO = qryMaxDate.JOBNO);

The first query works greatand then I try to combine everything. I keep getting and error that states "syntax error(missing operator) '(t1.JOBNO = t2.JOBNO) INNER JOIN qryMaxDate ON (t1.JOBNO = qryMaxDate.JOBNO)'

Kind of stuck but plugging along.

cheers big ears...

 

[r937]

access requires you to parenthesize the joins if there's more than one:

SELECT t1.JobNo
, t2.Client
, t2.City
, qryMaxDate.LastCommentDate
, t1.Comment
FROM (
Table1 t1
INNER JOIN Table2 t2
ON t1.JOBNO = t2.JOBNO
)
INNER JOIN qryMaxDate
ON t1.JOBNO = qryMaxDate.JOBNO

rudy

 

[tomslacks]

For those interested this is how I was able to get it to work

First query qryMaxDate

SELECT t2.JOBNO, Max(t1.CDATE) AS LastCommentDate
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.JOBNO=t2.JOBNO
GROUP BY t2.JOBNO;

Second query

SELECT [Table2].[JobNo], [Table2].[Client], [Table2].[City], [Table1].[CDate], [Table1].[Comment]
FROM Table2 INNER JOIN (Table1 INNER JOIN qryMaxDate ON [Table1].[CDate]=[qryMaxDate].[LastCommentDate]) ON [Table2].[JobNo]=[Table1].[JobNo];

Thanks to all that lead me in the right direction

T