ADP/SQL Data Selection Criteria

[patiya]

I am using ADP front end and SQL on the back end. I want to run a report based on these 2 tables.

Table1 Data:

Client Id Date Employee ID Record Source

23376 1/9/2004 128 Table1
23376 1/8/2004 30 Table1
23379 1/6/2004 15 Table1
23377 1/6/2004 15 Table1
23380 12/31/2003 15 Table1
23377 12/31/2003 15 Table1


Table 2 Data:

Client Id Date Employee ID Record Source

23376 7/20/2004 30 Table2
23376 7/12/2004 30 Table2
23376 7/6/2004 30 Table2
23376 7/4/2004 30 Table2
23377 7/6/2004 30 Table2


Now I need to select the data from both Tables for the report based on this criteria:

Select Only one record (i.e. the same Client Id with the latest date). That is my report row source should look like this:

Final row source:

Client Id Date Employee ID Record Source

23376 7/20/2004 30 Table2
23377 7/6/2004 30 Table2
23379 1/6/2004 15 Table1
23380 12/31/2003 15 Table1


First I thought to create a Temporary File in a SP and append data from Table1 and Table2 but I don’t know how to populate the desired result in SP and return as a report row source in ADP.

But If I need to run everything in ADP, what will be my best approach? Create a Hard Coded Temporary file, append data from Table1 and Table2 and Populate the report result and run the report. But Only problem I think what will happen if 2 users run the same report at the same time since the report row source is a hard coded temporary file.

Any suggestion or help with coding will be appreciated. Thanks

 

[Nevermoor]

As a start,

SELECT     TOP 100 PERCENT clientID, MAX([date]) AS LatestDate
FROM         (SELECT     *
                       FROM          dbo.Table2
                       UNION
                       SELECT     *
                       FROM         dbo.Table1) DERIVEDTBL
GROUP BY clientID

Returns clientID and Date correctly. Maybe someone else has a suggestion on how to expand it to return the other data without screwing up the grouping...

 

[patiya]

Thanks Nevermoor (Programmer) for your help. But If I need to return more fields such as employee Id, the grouping seems not working.