Hi, I am making a clinic Management. I have to generate a report of test to be conducted for a particular patient. Problem I am facing is, I am able to display the tests name and costs, But i want to display Patient Name and Doctor Name only ONCE!! i.e. Code: Doctor Name Patient Name Test Cost of Test Doctor1 Patient 1 test1 cost1 test2 cost2 test3 cost3 (don't mind the identation, its not coming proper here for some reason i don't know...) But it is being displayed as Code: Doctor Name Patient Name Test Cost of Test Doctor1 Patient 1 test1 cost1 Doctor1 Patient 1 test2 cost2 Doctor1 Patient 1 test3 cost3 How do I do this? Also I thought of a little cheat way out of this. I thought i'll create a new table of the values required i.e table (doc name, patient name, test name, test cost) in which my first row will have doc name, patient name, null, null and subsequent rows will have null, null, test name(i), test cost(i) So it is expected that the values are inserted like this Code: DNAME PNAME TESTNAME COST D1 p1 - - - - CHEST XRAY 100 - - URINE 250 - - BLOOD 100 But it comes like this Code: DNAME PNAME TESTNAME COST - - CHEST XRAY 100 D1 p1 - - - - URINE 250 - - BLOOD 100 I don't understand whats the problem...Also note If the table is created the first time the values get inserted properly, but if i insert values after deleting all values it goes random... Here's the code... Code: Private Sub cmdGivePresc_Click() On Error GoTo step1 If txtInfo(7).Text <> "" Then step2: Set temp = New ADODB.Recordset temp.Open "delete from for2mins", Ado, adOpenKeyset, adLockOptimistic temp.Open "select * from for2mins", Ado, adOpenKeyset, adLockOptimistic temp.AddNew temp(0) = doc_name temp(1) = pat_name temp(2) = Null temp(3) = Null Set Test = New ADODB.Recordset Test.Open "select tname, cost from test where test_id in (select test_id from test_result where test_result.presc_id in (select presc_id from appt a, doctor d where a.appt_time = '" & appt_time & "' and a.appt_date = '" & appt_date & "' and d.doc_id = " & doc_id & "))", Ado, adOpenKeyset, adLockOptimistic While Not Test.EOF temp.AddNew temp(0) = Null temp(1) = Null temp(2) = Test(0) temp(3) = Test(1) Test.MoveNext Wend temp.Update DataEnvironment1.Connection1.Open DataEnvironment1.Command1 DataReport1.Show Else MsgBox "Enter Patient ID or Select Appointment Date/Time", vbCritical End If Exit Sub step1: DataEnvironment1.Connection1.Close GoTo step2 End Sub
Because in a report doctors name and patients name comes only once, not repeatedly while tests-costs can be multivalued hence it has to display all of them
no truncate didn't help.... I found a cheat in it, I added another coloumn id, in which i add 1...2....3...as coloumn number and i use order by id....which sorts the problem out. But this isn't a right way to do it, someone told me to use grouping i tried that but that too didnt help ...I don't want to create another table and all....if thats possible ..?
group by won't help I don't think this can be done using sql it should be done after the record is feteched, while adding the output to the report I don't think this should be a problem as long as you are just displaying and not storing them in that way.