Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Angry Unanswered: Query to get total from table to form

    I have two tables (EvaluationEntry) and (RadiationOncologists).

    I have a form based on the RadiationOncologists Table with a field called NUMBER which needs to do a count of one of the columns (RADLNAME) in the EvaluationEntry Table for every instance matching the current (RADLNAME) in the RadiationOncologists Table and populate the NUMBER field with it's total.

    I can get the total per RADLNAME but I can't figure out how to make it so the query looks to the current RADLNAME name showing in the form.

    for example in the RadiationOncologists form the current RADLNAME is Smith, I would like to count the number of times Smith is listed in the EvaluationEntry Table and return that number to the NUMBER field in the RadiationOncologists form.

    Here is my calculation for gathering the totals from the EvaluationEntry Table

    SELECT EvaluationEntry.RADLNAME, Count(EvaluationEntry.RADLNAME) AS CountOfRADLNAME
    FROM EvaluationEntry
    GROUP BY EvaluationEntry.RADLNAME;

    I know this isn't complete but this is the base query and everything I've tried with WHERE has failed miserably.

    any help you can provide will be greatly appreciated. If you have any questions or need to see a working copy you can email me at bob@hicky.net thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Re: Query to get total from table to form

    Originally posted by bobhicky
    I have two tables (EvaluationEntry) and (RadiationOncologists).

    I have a form based on the RadiationOncologists Table with a field called NUMBER which needs to do a count of one of the columns (RADLNAME) in the EvaluationEntry Table for every instance matching the current (RADLNAME) in the RadiationOncologists Table and populate the NUMBER field with it's total.

    I can get the total per RADLNAME but I can't figure out how to make it so the query looks to the current RADLNAME name showing in the form.

    for example in the RadiationOncologists form the current RADLNAME is Smith, I would like to count the number of times Smith is listed in the EvaluationEntry Table and return that number to the NUMBER field in the RadiationOncologists form.

    Here is my calculation for gathering the totals from the EvaluationEntry Table

    SELECT EvaluationEntry.RADLNAME, Count(EvaluationEntry.RADLNAME) AS CountOfRADLNAME
    FROM EvaluationEntry
    GROUP BY EvaluationEntry.RADLNAME;

    I know this isn't complete but this is the base query and everything I've tried with WHERE has failed miserably.

    any help you can provide will be greatly appreciated. If you have any questions or need to see a working copy you can email me at bob@hicky.net thanks
    Ok, the easiest thing is: "WHERE EvaluationEntry.RALDNAME=Namehighlighted"

    I suppose that you've already tried it, but I don't why it shouldn't work.

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    I did try that to no avail. I've had more than a few people look at it and walk away scratching their heads. Thanks for the reply though.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Query to get total from table to form

    Originally posted by bobhicky
    I have two tables (EvaluationEntry) and (RadiationOncologists).

    I have a form based on the RadiationOncologists Table with a field called NUMBER which needs to do a count of one of the columns (RADLNAME) in the EvaluationEntry Table for every instance matching the current (RADLNAME) in the RadiationOncologists Table and populate the NUMBER field with it's total.

    I can get the total per RADLNAME but I can't figure out how to make it so the query looks to the current RADLNAME name showing in the form.

    for example in the RadiationOncologists form the current RADLNAME is Smith, I would like to count the number of times Smith is listed in the EvaluationEntry Table and return that number to the NUMBER field in the RadiationOncologists form.

    Here is my calculation for gathering the totals from the EvaluationEntry Table

    SELECT EvaluationEntry.RADLNAME, Count(EvaluationEntry.RADLNAME) AS CountOfRADLNAME
    FROM EvaluationEntry
    GROUP BY EvaluationEntry.RADLNAME;

    I know this isn't complete but this is the base query and everything I've tried with WHERE has failed miserably.

    any help you can provide will be greatly appreciated. If you have any questions or need to see a working copy you can email me at bob@hicky.net thanks
    "SELECT COUNT(EvaluationEntry.RADLNAME) AS CountOfRADLNAME FROM EvaluationEntry WHERE (EvaluationEntry.RADLNAME='" & FormNameHere![TextboxNameHere] & "');"

    This is an exact match only ...

    Also, you can substitute Me.TextboxName for the Form name if the query is run within the confines of the form ...

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    I assume when you say

    SELECT COUNT(EvaluationEntry.RADLNAME) AS CountOfRADLNAME FROM EvaluationEntry WHERE (EvaluationEntry.RADLNAME='" & FormNameHere![TextboxNameHere] & "');

    that you mean the current form and that the [TextboxNameHere] is too be the corresponding [RADLNAME] in the RadiationOncologists Form.

    For some reason, I come back with "zero" when I use this, though it makes complete sense that it should work, am I missing something with a relationship?

  6. #6
    Join Date
    Sep 2003
    Posts
    5
    Any other ideas? I'm brain jelly right now.

    thanks for the previous posts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •