Crystal SQL Designer Syntax

[SteveFairclough]

I'm trying to create a view in SQL Designer that looks at the minimum date of a field in my table for each reference to a ClaimID field. The SQL I'm trying to use is as follows :-

SELECT ClaimProgress.ClaimID, Min(IIf([ClaimProgress]![Status] In ("NEW","NOTIFIED&quot,[ClaimProgress]![DateTime])) AS StatusNew
FROM ClaimProgress
WHERE ClaimProgress.Status In ("NEW","NOTIFIED&quot
GROUP BY ClaimProgress.ClaimID;

My problem is that I get an error message stating the following :-

ODBC Error:[Microsoft][ODBC Mircosoft Access Driver] Too few parameters. Expected 8.

I'm sure it's because I haven't included a statment for the "false" part of the IIf function. But the reason I've left that blank is because I want a blank field to be displayed is there is no minimum date for that group of records.
I've built the statement in Access's Query Designer as I'm very new to the Crystal SQL designer.

Maybe somebody could give me a clue as to what syntax to use if I have to put something in the "false" part of the IIf function.

I hope I've explained that OK. I'd be grateful for any help anybody has to offer.

Thanks
Steve Fairclough
I.T. Manager

 

[IdoMillet]

There are simple ways to do all of this within Crystal,
but if you insist on doing most of the work in SQL,
here is one approach:
-----------------------------------------------
SELECT ClaimProgress.claim_Id, Min(IIF(ClaimProgress.status In ("new","notified&quot, ClaimProgress.claim_date, '9/9/9999')) AS Min_Claim_Date
FROM ClaimProgress
GROUP BY ClaimProgress.claim_Id
;
-----------------------------------------------

Those claims with no 'new' or 'notified'
records would show up with a date of '9/9/9999'
Within Crystal, you would then simply suppress
the Min_Claim_Date if it is '9/9/9999'

Cheers,
- Ido ixm7@psu.edu

 

[SteveFairclough]

Ido, thanks for your reply.

I understand what you're saying but there is a reason for me doing it in SQL Designer - unless you know an easier way.

I'm actually trying to create a summary view of a table to be linked in at the detail level in a Crystal Report. This is not something I can do by grouping due to the complex nature of the selection criteria - trust me, I'll explain it in full detail if you want. Anyway, I've got a few problems in attempting this :-

1. I don't yet know how to link a .qry file into a report

2. When designing the SQL, it doesn't seem to save the summary view ie. When changing fields to Min of, Max of etc, they disappear when I reload the query.

3. Am I completely missing the point. Is there a way to create a summary view of a table in the Report Designer and then link that with another table ?

Any help is appreciated.

Many thanks Steve Fairclough
I.T. Manager

 

[IdoMillet]

Steve,

If you need to link the summary results (did you test the SQL query I suggested?) to another table, you must modify the SQL Designer query itself to include the necessary data or use a linked subreport.

Cheers,
- Ido ixm7@psu.edu

 

[IdoMillet]

Another option, if you have permissions,
is to create a View in the database itself.
You can then use and link the View to any
other table.

Cheers,
- Ido
ixm7@psu.edu

 

[SteveFairclough]

Ido

thanks for staying with me on this! I did try your statement in SQL Designer and it worked OK. I see what you mean about having to enter the SQL statement directly so it saves properly.

I have to use this method rather than sub-reports as I need to base some groupings on the summary view from the SQL designer file. I can't create a view in my database - I'm having difficulty with the supplier of the product providng me with access to create queries in the mdb file. So, my final attempt was to use the SQL Designer method.

Now, although nearly there, I'm still having a problem. I have tried to base a report on the newly created .qry file. It presents me with a list of field names in the Report Expert but when I try to run the report I get the following three errors in sequence :-

xBase error : Could not open the file

Can not open database

File not found

Any thoughts ? Steve Fairclough
I.T. Manager

 

[SteveFairclough]

I've now managed to gain access to create a summary view in the database - WOW ! What a difference, this option is so much easier.

Thanks for your help once again with this problem. Steve Fairclough
I.T. Manager