viewing specific records in a report

[bajanJedi]

hey there,

two questions. i am running this query which is based on a 4 tables. one of the tables - Appointments - has in a field - Prescribed Medication - which may or may not contain information. in the query i have prescribed medicatio and it associated cost (from another table), but when i run the query, the only records showing up are those that have something in the Prescribed Medication field. Is there any way around that?

next, I was wondering how to view a specific record in a report based on a key field. the form that is based on report has a patient id, name, date, time etc, and a 'view patient bill' command button. when the button is clicked, the information for the current record in the form should only come up. usually the wizard asks if u want to view all records or specific records for forms but it didn't work for the report.

bajanJedi

 

[batteam]

This might help:

For question one, sounds like you need to evaluate your joins in the query. Its probably just a one to many or a many to one relationship problem.

For question two: have a query be the record source for your report and have the 'patient bill record' unique identifier be the criteria on that query. That unique identifier can be passed over to that query from the form, something like:

Forms!frmShowPatientBill![txtUniquePatientId]

Now, when the query is run, it will only show the columns you selected (and want on the report) but only for the record containing that unique identifier.

Hope this helps.

 

[lespaul]

to change the query so it returns all records whether or not there is a matching Medication, right click on the "relationship line" in the query design view and select edit relationship. You will have three choices, you will want to choose the one that says something like:

Show all records from tblAppointment and only those records that match from tblMedications.

this will change your join from an inner join (only where the field is the same in both tables) to a left or right join.

HTH

leslie

 

[bajanJedi]

ok i got the first one figured out. but the second one i'm still a bit lost. i already have a query and it has a unique identifier called PatientID. i'm lost at the criteria part. the form has a button to open the patient's bill report (which is based on the query). i thought maybe there was some kinda code i would have to put in the button like

DoCmd.OpenReport "rptPatientBill", View, FilterName

something like that, maybe?

 

[bajanJedi]

anyone else with some ideas on this?

 

[bajanJedi]

no worry folks, i got it working.

bajanJedi