Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSum & Sum (Access 97 SR2)

    1. Using an unbound form, I'm wanting to perform a calculation. I need to calculate the percentage of overtime used in a particular work area. There are 26 different work areas. The only distinct item of data that indicates overtime is in a field called type_hr_cd. All overtime begins with the letter O.

    2. I would also like to perform this calculation within a query and also in a bound report.

    Any assistance is appreciated !!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum & Sum (Access 97 SR2)

    You will need to tell us a bit more about the structure of your table. What are the field names and types? You only mention type_hr_cd.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    Hi. Sorry for not replying before now. Was placed on another project.

    The information comes from an Excel worksheet. I'm taking the total hours worked month-to-date and year-to-date, and calculating the percentage of overtime worked by each department.

    Table layout is flat-file.

    Below is an example of the hours I'm tracking for 1 of 26 divisions. The ALPHA column contains the Type-Hour codes. Any code beginning with the letter O denotes overtime.

    Type Civ Hrs Mil Hrs Tot Civ Tot OT %
    Hr Cd MTD MTD Hrs OT Hrs

    CD 9.00 0.00
    CN 4.50 0.00
    CT 27.50 0.00
    LH 16.00 0.00
    LS 3.50 0.00
    RG 311.25 0.00
    CD 35.75 0.00
    CN 64.00 0.00
    CT 9.00 0.00
    LA 108.00 0.00
    LH 80.00 0.00
    LS 92.00 0.00
    OS 2.00 0.00
    RG 1,524.25 0.00 2,286.75 2.00 0.09%

    I would like to;

    1. Using an unbound form, perform a calculation. I need to calculate the percentage of overtime used in a particular work area. There are 26 different work areas. The only distinct item of data that indicates overtime is in a field called Type Hr Cd. All overtime begins with the letter O.

    2. I would also like to perform this calculation within a query and also in a bound report.

    Can I use a DSUM expression to perform calculations within an unbound form? Not only will I be tracking month-to-date, but also year-to-date.

    Thanks in advance !!

    Bob in Indy

    PS: Hmmm.....the columns of info didn't post correctly.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum & Sum (Access 97 SR2)

    Let's say your data are in a table tblHours. Note: the expressions in the following may be too long to be displayed on one line.

    Place a text box on an unbound form and set its Control Source property to

    <font face="Georgia">=DSum("[Civ Hrs Mtd]","tblHours","Left([Type Hr Cd],1)='O'")/DSum("[Civ Hrs Mtd]","tblHours")</font face=georgia>

    and set its Format property to Percentage. This will calculate the overall overtime percentage. If you have all divisions in one table, you might put a combo box cboDivision on the form from which the user can select the dicision. To return results for a single division, set the Control Source property to

    <font face="Georgia">=DSum("[Civ Hrs Mtd]","tblHours","Left([Type Hr Cd],1)='O' And Division = '" & Me.cboDivision & "'")/DSum("[Civ Hrs Mtd]","tblHours", "Division = '" & Me.cboDivision & "')</font face=georgia>

    Since you have supplied no information on how the date comes into this, I can't tell you how month-to-date or year-to-date values should be calculated.

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    Hans, would it be better if I sent the Excel 97 spreadsheet showing what I'm trying to accomplish in Access? If so, I would want to send it directly to you, rather than post it here on the internet.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum & Sum (Access 97 SR2)

    Hi Bob,

    It's always better to post files in the Lounge; that way other Loungers can have a stab at your problem too. If your spreadsheet contains sensitive information, you can replace actual names etc. by dummy values. If the .xls file is too large (there is a 100 KB limit on attachments), you can WinZip it.

  7. #7
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    Greetings,

    Here is the format in Excel. I want to accomplish the same in Access. I'm using Access 97 SR2.

    The "All Lines" sheet contains all of the applicable information. The "Summary" sheet is the summary for all Organizations.

    The data here is for one month. As more months are added, the Year-To-Date will change accordingly. I will be tracking Month-To-Date and Year-To-Date numbers.

    Would it be easier to try and show these based on a query or queries rather than an unbound form/report?

    Thanking one and all for their assistance.

    Bob in Indy.
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum & Sum (Access 97 SR2)

    Bob,

    To get a report displaying a summary like that in your spreadsheet., I came up with a series of queries: separate queries to sum civilian hours and civilian overtime hours, and separate queries for month-to-date and year-to-date. They are all bound together with a query returning all divisions (orgs).

    Posting the SQL for each probably isn't very enlightening; I have attached a zipped database with the queries and a report. I have added dummy data for other months, and to keep the size down, I have kept only a few divisions. If you need explanation (or if it doesn't do what you want) post back.
    Attached Files Attached Files

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    Hi Hans

    Great stuff! You sovled a problem I was going to have next month.

    If you want the query to ask for starting year, would simpley add Year: Year([Period]) to qryOrgs with group by then set criteria to ask for year?

    John

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DSum & Sum (Access 97 SR2)

    Another way is to build a query (named qryTotal) for the total sum like:
    SELECT Sum([Civ Hrs Mtd]) as TotalHrs FROM tblHours

    Then build another query like:
    SELECT Sum([Civ Hrs Mtd])/qryTotal .TotalHrs as AvgHours FROM tblHours, qryTotal WHERE Left([Type Hr Cd],1)='O'

    I don't know if this is more or less efficient than Hans solution, but if the tables are small it should not matter. Perhaps Hans will respond to this.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum & Sum (Access 97 SR2)

    Hello John,

    It's a bit more complicated than that. The query qryOrgs in the database I posted simply returns all divisions (orgs). The four queries with CivHrs in their names all have criteria for restricting to the current year and month. If you want to prompt the user for a year and month, you must make these four into parameter queries. The parameters must be declared explicitly in Query/Parameters... for them to work correctly.

    In the (zipped Access 97) database attached to this reply, I have modified the four queries to ask for month and year; qryOrgs and qryPercOTPerOrg are unchanged. I also changed the text in the report header to reflect the parameters.
    Attached Files Attached Files

  12. #12
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    Hans,

    Unbelievable !! I don't think I would have though of designing the queries as you did. Sorry I have not responded before now, but I was placed onto another project. I've studied both attachments, and will certainly be able to use them for the intended data....and other applications.

    Again, thank you!!

    Bob in Indy

  13. #13
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    What happened to the attachment ?!?!?!?!?

    The file on my PC has data in the table, but nothing in the queries nor report. I thought I could redownload the attachment from here, but there is no data in any of the queries nor report.

    Anyone have any ideas ??

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: DSum & Sum (Access 97 SR2)

    That's because the sample data in the attached db (originally posted last year) all had dates in 2002. The current year is now 2003. As a result the queries displayed no data. You have to update the dates in "Period" field to reflect 2003. You can do this with some update queries. Example:

    UPDATE tblTime SET tblTime.Period = DateSerial(2003,1,Day([Period]))
    WHERE (((Month([Period]))=10));

    This updates the OCT 2002 dates to JAN 2003.

    I attached a copy of the same attachment with updated dates as zip file, converted to ACC 97 format (I'm using A2K & AXP).

    HTH
    Attached Files Attached Files

  15. #15
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum & Sum (Access 97 SR2)

    (Pointing finger at self while quoting Forrest Gump...)

    "Stupid is as stupid does..."

    Bob (feeling stupid) in Indy

Page 1 of 2 12 LastLast

Posting Permissions

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