Results 1 to 11 of 11
  1. #1
    LWOLF is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    6

    Assistance with query

    Hello,

    Let me start by stating that I am completely green to Access but have learned a lot the past couple of days.

    Here is my situation. I have a simple table that contains three columns; Column 1 "Date", Column 2 "Total Hours", Column 3"Earned Hours". I have created a query that to allow me to select the data from both "hours" column between a specified date range. Then I have the query sum the total of each hours columns. So I'm all good here, but what I would like to do is take the totals of each (combined totals for Totals hours and Earned hours) and divide them (total for "earned hours" / total of "total hours") and have only that answer appear as the output.
    I have attached a screen shot of the beginning of my query. So for I have been unsuccessful in my attempts to complete this task.



    Appreciate the assists.
    Thanks,
    Attached Thumbnails Attached Thumbnails Access Query.PNG  

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    between a specified date range
    Where do these dates come from? What happens when you run the query?

    BTW, 'Date' is a reserved word in Access and should not be used as a data field name. Also, best practice indicates that spaces should not be used in field names.

  3. #3
    LWOLF is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    6
    Quote Originally Posted by davegri View Post
    Where do these dates come from? What happens when you run the query?

    BTW, 'Date' is a reserved word in Access and should not be used as a data field name. Also, best practice indicates that spaces should not be used in field names.
    Hello,

    And thanks for the reply. Here is a little back story. This is an old db that my company uses. The person who created it is unfortunately no longer with the company. In the file there is a form that allows users to enter data to a table named "machine date". This table goes back 15+ years. The dates that I'm using are pulled from this table. As for the naming convention, I would fix but with my luck once I started renaming fields the whole DB is going to break.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I have created a query that to allow me to select the data from both "hours" column between a specified date range. Then I have the query sum the total of each hours columns. So I'm all good here
    What is your table name? Is [Machine Data] that table or a (totals) query based on that table? If it's a query, we need to see the SQL for it.

  5. #5
    LWOLF is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    6
    The table is called Machine Data and it has all the data that I'm needing; dates, total hours and earned hours. The query I have just assigned a generic name for the time being.
    My end goal for this task is to have the query:
    1. Collect the (Earned Hours) and (Total Time Hours) data from the (Machine Data) table, selected by a specified date range.
    2. Then add all the numbers together for each column: Sum all of (Total Time Hours) column and sum all of (Earned Hours) Column.
    3. Take the sum of both individual columns (Earned Hours) and (Total Time Hours) and divide (Earned Hours) by (Total Time Hours)
    4. Have the answer from the previous step available in just a single field so I can use that in a report.

    What I'm getting now when I run the query is a field for each day of the specified date range.
    I am hoping that this is possible in just one query.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    SELECT Sum([Machine Data].[Earned Hours]) AS [SumOfEarned Hours], Sum([Machine Data].[Total Time Hours]) AS [SumOfTotal Time Hours], Sum([earned hours]/[total time hours]) AS Total
    FROM [Machine Data]
    WHERE ((([Machine Data].Date) Between [Forms]![frmDateSpec]![Starting Date] And [Forms]![frmDateSpec]![End Date]));
    This query will produce one summed row for all the rows in Machine Data whose dates fall between the Starting Date and End Date. Those two dates, entered by a user, are taken from a form I called frmDateSpec. Don't know where yours come from, but the example works.

  7. #7
    LWOLF is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    6
    Hello again Davegri,

    Sorry for the long delayed response. Thank you again for taking the time to look at this and you assistance.
    I was having the specified date entered during the query using the Criteria option (Between [Starting Date] And [End Date]). This would pop a text box for users to enter their desired dates.
    I tried using the code you provided, with modified WHERE field. with no luck. I'm getting an error: "This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements......."

    Here is the code I'm using.

    SELECT Sum([Machine Data].[Earned Hours]) AS [SumOfEarned Hours], Sum([Machine Data].[Total Time Hours]) AS [SumOfTotal Time Hours], Sum([earned hours]/[total time hours]) AS Total
    FROM [Machine Data]
    WHERE ((([Machine Data].Date) Between [Starting Date] And [End Date]));

  8. #8
    LWOLF is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    6
    Got the the first two parts of the Select field to work. It just seems to be the last part that Access doesn't like. Sum([earned hours]/[total time hours]) AS Total


    SELECT Sum([Machine Data].[Earned Hours]) AS [Sum Of Earned Hours], Sum([Machine Data].[Total Time Hours]) AS [Sum Of Total Time Hours]
    FROM [Machine Data]
    WHERE ((([Machine Data].Date) Between [Starting Date] And [End Date]));

  9. #9
    LWOLF is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    6
    Davegri,

    Got it. Changed the titles for the output. Thanks again for your assistance, it was a HUGE help!!!!

    SELECT Sum([Earned Hours]) AS [Combined Earned Hours], Sum([Total Time Hours]) AS [Combined Total Time Hours], [Combined Earned Hours]/[Combined Total Time Hours] AS Total
    FROM [Machine Data]
    WHERE ((([Machine Data].Date) Between [Starting Date] And [End Date]));

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Great job of adapting the query. Good luck with the rest of your project!

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Agreed. Good job.

    One note.... you should REALLY stop using spaces in object names. I'm just sayin.....

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

Similar Threads

  1. Query Assistance - New User
    By Hvns in forum Queries
    Replies: 1
    Last Post: 11-08-2012, 01:37 PM
  2. Assistance designing an archiving query
    By gm_lowery in forum Access
    Replies: 1
    Last Post: 06-14-2012, 01:57 PM
  3. Query and report assistance
    By POS120 in forum Queries
    Replies: 1
    Last Post: 03-18-2012, 04:48 PM
  4. Assistance in form/query
    By abodi in forum Forms
    Replies: 1
    Last Post: 09-19-2011, 03:30 AM
  5. Query Assistance Needed
    By elotromanuel in forum Queries
    Replies: 1
    Last Post: 03-17-2009, 09:31 PM

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