Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query confusion (Acc 2000)

    As far as I can tell, you don't need ConsProgObjID and ProgObjID in this query. Delete these columns.

    Move ObjName to the front; this is the field you want to group by.

    Move ProgramName to the fifth column and set the Total option to Where. This will automatically deselect the Show check box. We don't need to see this.

    Next, we will calculate the number of records with Approved and Achieved. If you just sum them, you'll get negative numbers in Access, since True = -1 (in SQL Server, True = 1). Also, Access will assign captions automatically.
    Instead, we will assign an alias ourselves, and sum the absolute value.
    In the second column, enter NumApproved: Sum(Abs([Approved])) and set the Total option to Expression.
    In the third column, enter NumAchieved: Sum(Abs([Achieved])) and set the Total option to Expression.

    In the fourth column, we will calculate the percentage. Enter PercAchieved: [NumAchieved]/[NumApproved] and set the Total option to Expression. With the cursor in this column, activate the Properties window and set the Format property to Percentage.

    The SQL for this query looks like

    SELECT tlkpObj.ObjName, Sum(Abs([Approved])) AS NumApproved, Sum(Abs([Achieved])) AS NumAchieved, [NumAchieved]/[NumApproved] AS PercAchieved
    FROM tlkpProg INNER JOIN (tlkpObj INNER JOIN (tblProgObj INNER JOIN (tblConsProg INNER JOIN tblConsProgObj ON tblConsProg.ConsProgID = tblConsProgObj.ConsProgID) ON tblProgObj.ProgObjID = tblConsProgObj.ProgObjID) ON tlkpObj.ObjID = tblProgObj.ObjID) ON tlkpProg.ProgramID = tblConsProg.ProgramID
    WHERE (((tlkpProg.ProgramName)="Work Services"))
    GROUP BY tlkpObj.ObjName;

    (Use the View menu to switch between SQL view and Design view)

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query confusion (Acc 2000)

    I'm bamboozled about how to set this query up. I need to have a report that (1) lists the objectives in a program; (2) counts the number of people approved for each objective using a Boolean field, (3) counts the number of people who have achieved that objective, also a Boolean field and (4) calculates the percentage of those approved who achieve the objective.

    I've gotten a start on a query and attach it here. Could really use some pointers on how to proceed.

    E
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query confusion (Acc 2000)

    That was slick! Thanks

Posting Permissions

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