Results 1 to 7 of 7
  1. #1
    dbuckmas is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    3

    Simple Data Analysis

    Hi!

    I am fairly new to access, but I have learned a great deal about creating databases and the forms, queries, and reports that are derived form them.

    I have a database of several thousand “issues”. Each issue has a unique ID, a title, a priority, as well as a completion field (limited to “complete” or “incomplete”).

    I am looking for a way to create a form (or report?) that the user can open that will show the status of the issues. For instance, something that tells the user that 34% of all priority 1 issues are complete......

    Can someone recommend a tutorial or a resource that can help me accomplish this?

    Thank you!


    Dan

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Research building report using its Sorting & Grouping features with aggregate calcs.

    Suggest you get an introductory tutorial book.
    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.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum

    Attached is a simple DB to get you started & which hopefully you can adapt to your own needs

    It has one table tblIssues:
    ID (autonumber PK)
    Title (text)
    Priority (number)
    Completed (Boolean - Yes/No) - much easier than text if you only have 2 choices
    CompletionDate (date/time)

    I've created a query qryIssues to get total issues, completed issue & % complete
    Code:
    SELECT Count("ID") AS TotalIssues, CInt(DCount("*","tblIssues","Completed=True")) AS CompletedIssues, CInt(DCount("*","tblIssues","Completed=True"))/Count("ID") AS [%Complete] FROM tblIssues;
    NOTE: There are many other ways of doing the same thing e.g. create a function
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    dbuckmas is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    3
    Quote Originally Posted by ridders52 View Post
    Welcome to the forum

    Attached is a simple DB to get you started & which hopefully you can adapt to your own needs

    It has one table tblIssues:
    ID (autonumber PK)
    Title (text)
    Priority (number)
    Completed (Boolean - Yes/No) - much easier than text if you only have 2 choices
    CompletionDate (date/time)

    I've created a query qryIssues to get total issues, completed issue & % complete
    Code:
    SELECT Count("ID") AS TotalIssues, CInt(DCount("*","tblIssues","Completed=True")) AS CompletedIssues, CInt(DCount("*","tblIssues","Completed=True"))/Count("ID") AS [%Complete] FROM tblIssues;
    NOTE: There are many other ways of doing the same thing e.g. create a function

    This works great, except when I run a similar query in my database, it fills in the desired value in multiple rows, rather in just one row like your example. What setting am I missing to make that happen?

    Thanks!
    Dan

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If your query contains any grouping fields it will give multiple rows:
    For example, see qryIssuesGrouped in the attached:

    Code:
    SELECT tblIssues.Priority, Count("ID") AS TotalIssues, CInt(DCount("*","tblIssues","Completed=True  And Priority = " & [Priority] & "")) AS CompletedIssues, CInt(DCount("*","tblIssues","Completed=True And Priority = " & [Priority] & ""))/Count("ID") AS [%Complete]FROM tblIssues
    GROUP BY tblIssues.Priority;
    If its not that, please post the query in SQL view or a screenshot in design view
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    dbuckmas is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    3
    Solved!

    I was able to correctly gain the totals I needed from my data set via a crosstab query.

    Thank you for the assistance!
    Dan

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Glad you got it to work but it should have been possible using an aggregate query as in my original example
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Inspection Record and Data Analysis Database
    By cap.zadi in forum Access
    Replies: 4
    Last Post: 06-17-2017, 03:50 AM
  3. Replies: 1
    Last Post: 12-28-2014, 03:06 AM
  4. Data Analysis Direction
    By canyon289 in forum Access
    Replies: 6
    Last Post: 03-05-2012, 11:51 PM
  5. Table Data Analysis
    By bdf48 in forum Programming
    Replies: 1
    Last Post: 12-01-2011, 02:21 PM

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