I've never used Crystal, I have a basic question...

[mzac1]

Hi all, this is my first post. Im working on a VB6 application which uses CR8.5 to generate reports. In one of my reports, I need to add a field. Here is the situation:
All data is stored in Access tables, and im using an ODBC connection. In a table called PatientInfo, there is data about all patients and the field I am specifacally working with stores their last therapy session. We'll call this field therapyDate. In another table called PatientTransactions, we keep track of all the status transactions for each patient, and the date of the transaction, in a field called TransDate. An example of a transaction would be the therapy session I just mentioned. There is another field in PatientTransactions called FinancialClass. This is the field I need to access. Basically I need to do some sort of lookup where I can display the FinancialClass of a patient where PatientInfo.therapyDate = PatientTransactions.TransDate.

If I were writing a SQL Statement, I would say

SELECT FinancialClass FROM PatientTransactions WHERE TransDate = PatientInfo.therapyDate

Please Help, I have no idea what Im doing. Thank you

 

[synapsevampire]

Can't you join the tables based on the date fields?

You can also create an MS Access query as the datasource and have the criteria within that.

-k

 

[LLowrance]

Wouldn't you want to join on a patientID also? Just joining on date would not keep the patient information together. Hard to tell without knowing what the primary key is on each table. Just a thought.

I too work with patients and therapies etc with more date fields than I can mention.

-LLL

 

[synapsevampire]

Yep, you'd want other joins as well...

-k

 

[CR85user]

Hi, As you are new to Crystal I will give a very very basic outline of the structure of creating reports in it compared to your usual method of sql queries.

You state you would normally write:

SELECT FinancialClass FROM PatientTransactions WHERE TransDate = PatientInfo.therapyDate

In crystal this is done in three stages - Firstly telling it which tables to look at - then the selection of relevant accounts followed by the data you are wanting to see.

Firstly select your chosen database & tables.

Then within the main report screen browse menu to:
Report - Edit Selection formule - Record

In the text box you would enter:

{PatientTransactions.Transdate} = {Patientinfo.therapydate}

This then would tell crystal to only look at the records where this arguement is true. As the others have already stated this may be too loose a way of looking at things so other joins may be required. You may find opening the visual linking editor and deciding your primary table (Patientinfo? in this case) with a left outer join to Patienttransactions.

As suggested you would need a primary key by which to ensure the right, 'unique' patient data is listed.

If such a value exists then it would be recommended to link the two tables on both that primary key & the date field (provided format is the same)

To test the setup so far add a database filed in the detail section of your report to show which accounts it is retreiving compared to your sql query.

From this point on you can then begin to place & display any of the relevant information you require as well as working with that data in many, many ways.

I hope this helps with some of the basics of the structure of the software - Perhaps try it out yourself a little then come back to let s know how you are progressing.

Good luck

J