Results 1 to 10 of 10
  1. #1
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16

    Expression query not working

    I have a database to track a very intensive qualification process.
    I have 3 tables


    UserDataTBL - has userid, last name, first name, etc...

    QualItemListTBL - Has a list of sections in the qualification process - QualID, Qual Item

    QualTrackerTBL - Where you choose the user and what sections of the qualification they have completed. - TrackerID, UserID, QualID

    What I am trying to do is create a query to calculate the percentage of completion of the qualification. I kind of have it working with the below expression:

    Percentage: Count([QualTrackerTBL].[UserID])/(SELECT COUNT([QualItemListTBL].[Qual Item]) FROM [QualItemListTBL])

    Here is the SQL statement:

    SELECT UserDataTBL.Dept, UserDataTBL.Rate, UserDataTBL.[Last Name], Count([QualTrackerTBL].[UserID])/(SELECT COUNT([QualItemListTBL].[Qual Item]) FROM [QualItemListTBL]) AS Percentage
    FROM UserDataTBL LEFT JOIN QualTrackerTBL ON UserDataTBL.UserID = QualTrackerTBL.[UserID]
    GROUP BY UserDataTBL.Dept, UserDataTBL.Rate, UserDataTBL.[Last Name], UserDataTBL.Qualified
    HAVING (((SailorDataTBL.Qualified)=False));

    This works on the first person in the list it will give the correct decimal number but all the other users it gives whole number answers as well. So I have one person who has only 3 sections of the 43 completed and it says they are like 4.65116279069767E-02 which isn't correct. I am not sure what I am doing wrong here.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your sub query counting QualItemListTBL].[Qual Item] isn't joined to anything so you'll get the same count for each record.
    I'm pretty sure that isn't what you want.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    But I am not getting the same count for each record. They are linked in the database the Qual Item = QualItemID with a one to many relationship.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Chevy757IT View Post
    But I am not getting the same count for each record. They are linked in the database the Qual Item = QualItemID with a one to many relationship.
    Not in the sub query they aren't. The bit in red below isn't joined to anything.

    Humour me add (SELECT COUNT([QualItemListTBL].[Qual Item]) FROM [QualItemListTBL]) as MyCount as another field in your query and see what you get.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why does the HAVING clause reference SailorDataTBL?

    Shouldn't the subquery just return total record count for QualItemListTBL?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    the SailorData is my bad typo on my end it is UserDataTBL. If I add the QualItemListTBL to the query I get:

    "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I didn't say to add the table, just re-add your sub query again, without doing the calculation.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I attempted to replicate your tables and data. 1 user has 3 of 43 items. Ran the query and it calcs 6.97674418604651E-02 which is correct percentage.

    If you want to provide your db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    For start try a basic query
    Code:
    SELECT qt.UserID, COUNT(qt.[UserID])/(SELECT COUNT([QualItemListTBL].[Qual Item]) FROM [QualItemListTBL]) AS Percentage
     FROM  QualTrackerTBL qt
     GROUP BY qt.UserID;
    Check what you get. When this is OK, then add UserDataTBL and set join conditions, but don't add any fields jet. This query must return exactly same result table as basic query.

    The next step, set the filter condition (either using HAVING or WHERE). Users having qualification finished must be left out from result table.

    As final step, add additional fields from table UserDataTBL, and add those into WHERE clause too.

    With such approach, you can determine at which step your data get wrong (or don't get wrong).

  10. #10
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    I am an idiot and I forgot E- at the end for the conversion. I am soooo sorry to have wasted our time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated Expression not working
    By Desy77 in forum Access
    Replies: 2
    Last Post: 04-24-2018, 01:18 PM
  2. Expression used on form not working.
    By bravura in forum Forms
    Replies: 2
    Last Post: 11-18-2016, 05:28 PM
  3. Greater than expression not working
    By mpreston14 in forum Queries
    Replies: 9
    Last Post: 05-08-2015, 11:49 AM
  4. DSUM Where Expression not working
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 02-23-2015, 09:41 PM
  5. Expression not working in query
    By Bob Blooms in forum Access
    Replies: 4
    Last Post: 08-25-2012, 08:42 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums