Max SQL Query, how to do it in Crystal?

[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 is 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.

basically I need something like INNER JOIN Action ON Master.SSNO = Current.SSNO AND Current2.DateIn = Current2.Max_Date. How can I write temp queries or pass this in Crystal

 

[synapsevampire]

What version of Crystal are you using?

Table 1 makes no sense to me, you reference a table/view called Action in the column Action.Max_Date, but not as a table/view.

Perhaps you meant to remove max_date from the first table, and use the max_date generated in table2?

It looks like table2 is the parent table, try reversing the relationship and drop max_date from the table1 query.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mgkSHA]

OK, let me rephrase what I am doing. This is the current query that runs in Access...it is a three tier query I wrote to get the MOST-RECENT work history off of the table. I had to break it into three because Access Query builder would not accept a nested query. There are two tables with the shared field of social security number: see below:

Query one: provide all data within the Join I need

SELECT DISTINCT Master.SSNO, Master.Last, Master.First, Master.Middle, Action.[action], Action.RC1, Action.Status, Action.[class], Action.

, Action.[pin], Action.[grade], Action.[step], Action.[annualy], Action.[biweekly], Action.[hour], Action.[datein], Action.[unit], Action.rc
FROM Master INNER JOIN Action ON Master.SSNO = Action.SSNO
ORDER BY Master.SSNO, Action.DATEIN DESC;

Query two:  parse out only the TOP DATES for the most recent action

SELECT DISTINCT Master.SSNO, Max(Action.DATEIN) AS max_Date
FROM Master INNER JOIN Action ON Master.SSNO = Action.SSNO
WHERE (((Action.DATEIN)<=#12/31/1990#))
GROUP BY Master.SSNO;

Query three:  Join these queries to get the results

SELECT *FROM Query1 INNER JOIN Query2 ON (Query1.Master.SSNO = Query2.Master.SSNO) AND (Query1.Action.Datein = Query2.max_Date)
ORDER BY Query1.Master.SSNO, Query1.Action.DATEIN DESC;

Now in my VB 6.0 application I can nest the top two queries into one statement for my recordset and forms.  Now for Crystal, I need to run the above query, but I can't get the syntax right.  As in:  I have two tables references in Crytal Reports 8.5, - - Master and Action...they share the same unique SS#.  I need to join these tables, but also Join a Max(datein) script so I only get the most recent work history.  I do not know how to write a nested query like this in Crystal or how do to a three tier approach like in Access.  Currently, I have the report working so that I get ALL work events for each employee in descending order:

name1   blah   blah   12/23/2002
&quot;                 &quot;   12/21/2002
etc.

but I need the top, or most recently only.  I don't know how to accomplish this in the Crystal Reports SQL builder. Please help, and I hope this make a little more sense.

 

[chelseatech]

In CR you can get the max value with a Group Selection formula of

{table.field} = Maximum({table.field},{table.employeeID}) Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[synapsevampire]

You might use a Stored Procedure/View, or if it's MS Access, create a query in MS Access and use that as the data source.

Crystal 8.5 doesn't work with multiple recordsets well, I think that you can use ADO to build the 2 selects and then join on them, but the performance may not be what you seek.

You might consider just passing the data to the report, rather than having Crystal execute the SQL.

Here's an example using a DDF (there are plenty of others on the web):

http://www.kather.net/VisualBasicSource/CrystalReports/Crystal_Reports_and_Visual_Basic.doc

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com