Inner Join question..please help

[mgkSHA]

Hello:

I am having a tough time getting a query that runs in my VB app to work in Crystal reports. Basically, I am making several reports with a join between two tables: Master and Action(personal data and work history). Now some reports, need only the MOST-RECENT work history for current employees (where they currently are now), for this I use the Max SQL command and I wrote a nested query. This query provides the records I need and it works for creating an Access Report.

Now, in Crystal, I am trying to re-create the reports....I am re-writing this application in ADO and VB 6.0. I have two tables in my Crystal Report and I can get the report to display ALL employee records, I have linked the two tables via the SSNO field. The problem is, I can't get the Max Date to work in Crystal Syntax. Here is what I have so farthis like the current query that works in VB, you can see what I am trying to do):

ADO Table1:

select DISTINCT master.SSNO, master.LAST,master.FIRST, master.MIDDLE,master.SEXIN, Action.Max_Date FROM master INNER JOIN Current2 ON Master.SSNO = Current2.SSNO

ADO Table2:
select DISTINCT Action.SSNO, Action.rc, MAX(Action.Datein) As Max_Date, Action.status, Action.class FROM Action INNER JOIN Master ON Action.SSNO = Master.SSNO GROUP BY Action.SSNO, Action.rc, Action.status, Action.class

Now please note, ADO Table2 compiles fine: it takes Max(Action.DateIn) As Max_Date. The problem is, I can't pass this to the first table (the statements Action.Max_Date is not accepted by Crystal). So I get 6 records for an employee (all that exist on the action table) instead of the one most recent I need. Can anyone help me with this, how do I pass this query to my report? I am new to Crystal so if this isn't the best way to do this, sorry, can you help . I am fluent in VB and SQL. Thanks

 

[mgkSHA]

oops, I mixed up the table names in the first query..I am doing several reports at once: this is correct, the two tables are Master and Action.

ADO Table1:

select DISTINCT master.SSNO, master.LAST,master.FIRST, master.MIDDLE,master.SEXIN, Action.Max_Date FROM master INNER JOIN Action ON Master.SSNO = Action.SSNO

 

[synapsevampire]

Ahhh, that makes a *bit* more sense...

So a different table called Action already has the max_date in it?

It seems you can just do a

select DISTINCT master.SSNO, master.LAST,master.FIRST, master.MIDDLE,master.SEXIN for Table1.

Then join this on SSNO to your existing Table2 since table2 is determining the relevant max_date and detail data.

I would likely address this using a Stored Procedure or Views on the database.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mgkSHA]

Synapse:

No, Action does not have a Max() date, I need to query that. For some reason I can not get this right..basically this is what I need:

Master.First, Master.Last, Master.Middle, Master.Sexin

Max(Action.DateIn), Action.RC1, Action.Status, Action.Class

Both these tables share the same social security number. These are the fields off each table, I don't know why, maybe I have been looking at this too long, but I can't get this JOIN right...I have never linked tables before in Crystal or used a stored procedure, but I can't imagine it being much harder than VB. I write queries all the time in VB, I just can't get this today . I am at my wits end..can you help me . I need to put together a query which JOINS the personal and work history with only the MOST-RECENT record shown.

If you had to do this, what steps and code would you use with the field and tables given.