Missing Pages

[GMAN33]

Hi All

CR XI/Oracle 9 DB
This is for a doctor's office for a clinical trial. We have a DB table that we enter in individual records the pages that a patient may receive for each visit.

Visit 1: Pages 1, 2, 3, 7, 10
Visit 12: Pages 4, 5, 6, 12

They may or may not fill out each page at each visit

I have another table that records the pages that they do complete

Patient X: Visit 1: Page 1, 3, 7
Patient Y: Visit 2: Page 13, 17, 18

Is there a way that I can look at the one table with the pre-defined pages and then look at the one they did receive and state the pages that are still missing from the list?

I was thinking of an if/else statement, but there are over 200+ potential pages that they can complete

I was going to group this by patient and then by visit

Thank you for the help

 

[synapsevampire]

You need to show the actual data in the tables, not show a summary of it, we need to know how it is stored, not how you interpret it.

Tables look like this:

field1 field2 field3
MydatA More Andmore

So show what you have as data, and what you want as the output.

-k

 

[GMAN33]

Sorry about that

Table: Pages

VISIT NAMEAGE NAMEAGE NUMBER
Visit 1 Intro 1
Intro 2
Qual 3
Visit 2 DemogA 4
DemogB 5

Table: Patients
PATIENT NUM:VISITAGE NUM
1001 1 1
1001 1 3
1001 2 5

Hope this helps. I would need to find out from the above example that Page 2 and Page 4 are missing from the required list of fields

Thanks for the help

 

[Madawc]

You could do something like

If NEXT({PAGE NUM}) > ({PAGE NUM} + 1)
THEN 
"Pages missing between " & NEXT({PAGE NUM}) & " and " & ({PAGE NUM} + 1)

This could be displayed on the line, to draw attention to the gap. When there was no gap, nothing would show.

The first page might be a problem: if the first record is 2 and the next is 3, that would be accepted. For that, you'd need to do a running total and check if the first record has a page number greater than 1.

Hope this helps.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[GMAN33]

Thank you for the response...I appreciate it

That may work if the pages were collected in sequence

Visit 1: could have pages 1, 2, 3, 122, 123
Visit 2: could have pages 4, 5, 6, 33
Visit 3: could have pages 7, 12, 34

So I may have to create a concrete structure for the page table within a formula...who knows

I do like this formula ;-) Always learning.

 

[lbass]

If the Pages table contains all the required pages and the Patient table shows only those pages that are completed, then you could use a left join FROM the Pages table to the Patient table and then use a record selection formula like:

isnull({Patient.PageNum})

This would return only the missing pages. Insert a group on {patients.patientnum} and place {Pages.PageNum} in the detail section.

-LB

 

[GMAN33]

Thanks LB but I am a newbie with this.

I have the two tables linked and I get all the pages that have been entered.

I created a formula with isnull({Patient.PageNum}) in it but I am lost after this...not sure the steps. Do I put this in the record selection to equal TRUE?

Thanks again for the help

 

[lbass]

If you used the left join as I indicated, then just go to report->selection formula->record and put the following there:

isnull({Patient.PageNum})

To insert the group, go to the toolbar->insert->group and select {Patient.PageNum} for your group.

-LB

 

[GMAN33]

Thanks LB...i did as you said but for some reason, I am getting blanks now...I did the join using the Page No and inserted the group, but nothing

I will have to keep trying

Thank you again

 

[lbass]

What do you mean by "getting blanks"? You have to add {Pages.PageNum} to the detail section.

-LB

 

[GMAN33]

Yes...but I think the linking is incorrect

REQUIRED_PAGES-->RECEIVED_PAGES (left join using PAGE_ID field)

Performed grouping using RECEIVED_PAGES.PAGE_NO
Select Records isnull({RECEIVED_PAGES.PAGE_NO)
Placed on details isnull({RECEIVED_PAGES.PAGE_NO)

Not sure where I am going wrong

 

[lbass]

The grouping should be on {ReceivedPages.PatientID}, and the detail section should have the field {Required_Pages.Page_No} placed on it, not some formula with null in it.

Please do not keep changing the names of your tables/fields.

-LB