ADP/SQL Report 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

 

[bitmite]

you will probably have to make a temp table and append to it because there rely in no easly way to make this happen unless you combine the tables in the first place. if the thable are identical they should be together.