how do i find first two records in related table

[IanMullineaux]

Hi

I have two tables in a database lnked by an id number..
table 1 contains patient details and patient 2, patient assessments.

is there a way i can find, for each record in the main table, the first assessment from table two and place it in a column in the report, and then the second assessment and place that in a nother column?

Thanks

 

[lbass]

What version of CR are you using? Is the horizontal display just for display purposes, or do you then want to do calculations on the separate columns?

-LB

 

[IanMullineaux]

i'm using crystal reports 10 -
the purpose of the report is to transfer clinical data from our patient record system into an acess database which is then sent off to update a central nhs system..

to try and explain a little further - the main table holds a patient record -

each patient will have one or more than one medical assessment made on them whilst the ambulance crew is in attendance.. these records are held in the second table related by incident number.

the data required by the central system is in a different form that that held by us..

I need to find the first two entries for pain score held in the second table - in seperate records and transfer them to seperate fields in the report. but in the process, i need to convert the number into another value -

for example - if the pain score falls between 0-10 i need the report field to show 90, nothing entered = 70 etc

thanks for taking the time to look at this - i've been somewhat dropped in the deep end as i can do a bit of access - you know the script... 'its the same isnt it!'

 

[lbass]

You could try something like:

//{@col1}:
if {table.incidentno} = Nthlargest(1,{table.incidentno},{table.patientID}) then
if {table.score} in 0 to 10 then 90 else
if {table.score} in 11 to 20 then 80 else 70 //etc.

//{@col2}:
if {table.incidentno} = Nthlargest(2,{table.incidentno},{table.patientID}) then
if {table.score} in 0 to 10 then 90 else
if {table.score} in 11 to 20 then 80 else 70 //etc.

Place these in the detail section and then create four formulas:

//{@reset} to be placed in the patientID group header:
whileprintingrecords;
numbervar maxnth1 := 0;
numbervar maxnth2 := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar maxnth1;
numbervar maxnth2;

if {@col1} > maxnth1 then
maxnth1 := {@col1};
if {@col2} > maxnth2 then
maxnth2 := {@col2};

//{@displ1} to be placed in the patient group footer:
whileprintingrecords;
numbervar maxnth1;

//{@displ2}to be placed in the patient group footer:
whileprintingrecords;
numbervar maxnth2;

Then drag the patient ID groupname into the group footer and suppress the group header and the detail section.

You could also use "Add command" as your datasource so you could return only the two most recent records. However, the syntax varies by database, and I'm not sure what would work for you--even if you supplied the information.

-LB

 

[IanMullineaux]

thanks for this - i'll give it a try -

i really appreciate you spending the time on this - thanks for your help