I have to create a simple report for a health provider that shows his charges and payments for a particular group of patients. Each encounter has one charge, but the patients often make several cash payments per encounter. Since the key is encounter number, his medical records software builds a database with multiple values in a single (payments) field. Also the charges and patient info are in one table, payments are stored in another. When the tables are joined the multiple row payments create extra charges in the charges column or (if grouped by charge) do not display all records in the payments column/total. I’ve dealt with this by making a parent grid with all the patient info and charges, and a nested grid in one column with a child app that just pulls payments. This works good for me and all my data is now on screen how it should be. But the report total is only calculated for charges. If I select my link and click the Display Total bullet, I get totals but only per encounter. Is there a way to get a report total on the nested grid data? Or maybe another way to grid up my multiple values in one row that allows totals/summary to work normally? I tried comma sepeated values and it would only add the first value. Thanks in advance if you can help. Please let me know if my question is unclear or if there is a better place to post it.
Hi all. I see I have some views, but no replies. I usually like to read and figure things out for myself, but this issue is still giving me trouble. This is my first and only post on this forum. I thought this might be an “easy” question for someone who knows. Am I doing something wrong? Please help if you can. Thanks.
Hi,
not quite sure about your table and grid layout but when you change your SQL statement of the main grid to something like:
SELECT …, SUM(amount_field_of_payment_table) AS payed FROM charges_table t1 LEFT OUTER JOIN payments_table t2 ON t1.enc_id = t2.enc_id GROUP BY t1.enc_id
This gives you a field in your main grid which can be summed up for a grand total.
jsb