By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,544 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,544 IT Pros & Developers. It's quick & easy.

Dynamic Form Field Query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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;


Nov 12 '05 #2

P: n/a
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!
Nov 12 '05 #3

P: n/a
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!
Nov 12 '05 #4

P: n/a
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!


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.