Dynamic Form Field Query

[bob hicky]

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

[Matthew Sullivan]

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, hi*******@yahoo.com (bob hicky) wrote:

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

[bob hicky]

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!

[bob hicky]

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!

[Matthew Sullivan]

Everyone was a noob once... ;-)

On 04 Apr 2004 06:17:32 GMT, bob hicky <hi*******@yahoo.com> wrote:

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!