Sub query - select according to main query

[Johanna Gronlund]

Hello,

I have done a report with main and sub query. My main query should just
select one doctor. This is done on a form with a drop down menu. The doctor's
name on the main form seem to update with my selection but the information in
the sub query.

The sub query displays the services that the doctor provides. I am unable to
make make the subquery to display services according to the doctor. They are
just displayed in order of their ID. E.g. all smoking cessation services are
displayed first despite the practice, etc.

Is this enough information for someone to help me?

Many thanks in advance,

Johanna

 

[Golfinray]

You need a form and probably not a subform. Put a combo box on the form. Make
the combo box the Drs. Names from your query. Go into the events on the
combo, find the After Update event and click on it. Go to code builder and
type:
Me. Filter = "[name of your query] = """ & Me.Combo# of your combo & """"
Me.Fiteron = Yes
Now when you select a dr name the info on that doctor will pop up on your
form. You subform could work but you have to make sure it is linked properly
and you have to write the subquery properly. It would be easier just to use a
form and combo.

 

[Evi]

How easy this is, depends on your database design. If you are using lookup
fields in your tables the following may not work as expected. (Use proper
forms with combo boxes to enter your data).

It *sounds* as if you need

TblService (contains a list of all the possible services and has nothing to
do with the doctors)
ServID(Primary Key)
Service


TblPractice
PracID (PrimaryKey, Autonumber)
PracticeName
PracticeAddress

TblDoctor
DocID (PK)
DocFirstName
DocSurname
other details only about the doctor
PracID (this presumes that doctors can only belong to one practice at a
time) Linked from TblPractice

TblDoctorService
DocSerID
DocID Linked from TblDoctor
ServID Linked From TblService

your Main Report could be based on TblPractice
Your first subreport (RptServiceSub) could be based on TblDoctor and be
linked to the main report by PracID

Create a query based on TblDoctorService.
Add all the fields from this table to the grid. Add TblService to the Query
but only add the ServiceName to the Query grid.

drag this query into your main report in the same section as the Other
subreport, to create RptServiceSub2.

Click on it in the main report's Design View and click on Properties. Click
on the Data tab.

Next to Link Child Field, put DocID
Next to Link Master Field put RptServiceSub.Report.[DocID]
It will now be linked to DocID in RptServiceSub

Evi