Dynamic Form Field Query

I have two tables (RadiationOncologists and EvaluationEntry) and forms
for each of the two. My problem is that I have a text box in the
RadiationOncologists form that is called Number. This field needs to
dynamically count each instance of a certain doctors name from the
EvaluationEntry table which is RADLNAME and return the total for each
doctor listed as there record in the RadiationOncologist Form appears.
I can do a count of all listed doctors by either total or from a
query that will list all of the drs with their totals but can't seem
to figure out how only do the calculation for the current doctor
listed.

Here is the SQL code i have to get the count to work

SELECT Count(EvaluationEntry.RADLNAME) AS CountOfRADLNAME,
EvaluationEntry.RADLNAME
FROM EvaluationEntry INNER JOIN RadiationOncologists ON
EvaluationEntry.RadID = RadiationOncologists.RadID
GROUP BY EvaluationEntry.RADLNAME;

I believe the group by could go away if there was a WHERE statement
stating to us the current RadiationOncologists.RADLNAME

re: Dynamic Form Field Query

Bob:

You can refer to a control on an *open* form using this syntax:
Forms![FormName]![ControlName]

So, to get the count for only the doctor displayed, your SQL would
be something like:

SELECT Count(EvaluationEntry.RADLNAME) AS CountOfRADLNAME,
EvaluationEntry.RADLNAME
FROM EvaluationEntry
WHERE (EvaluationEntry.RADLNAME =
Forms![NameOfRadiationOncologistsForm!]![NameOfRADLNAMEControl]);

You could make that query the Record Source for a subform, and then
display the subform on the parent form that shows the
RadiationOncologists data.

-Matt


On 31 Mar 2004 18:18:29 -0800, hickykids@yahoo.com (bob hicky) wrote:
[color=blue]
>SELECT Count(EvaluationEntry.RADLNAME) AS CountOfRADLNAME,
>EvaluationEntry.RADLNAME
>FROM EvaluationEntry INNER JOIN RadiationOncologists ON
>EvaluationEntry.RadID = RadiationOncologists.RadID
>GROUP BY EvaluationEntry.RADLNAME;[/color]

re: Dynamic Form Field Query

Matt,
Thanks so much, I figured out how to do it with an expression but this
clears up how to do it with a SQL query. Appreciate the help, sometimes
being a newbie is more painful on others than it is the newbie.

Rob




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Dynamic Form Field Query

Matt,
Thanks so much, I figured out how to do it with an expression but this
clears up how to do it with a SQL query. Appreciate the help, sometimes
being a newbie is more painful on others than it is the newbie.

Rob


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Dynamic Form Field Query

Everyone was a noob once... ;-)

On 04 Apr 2004 06:17:32 GMT, bob hicky <hickykids@yahoo.com> wrote:
[color=blue]
>Matt,
>Thanks so much, I figured out how to do it with an expression but this
>clears up how to do it with a SQL query. Appreciate the help, sometimes
>being a newbie is more painful on others than it is the newbie.
>
>Rob
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]