Results 1 to 6 of 6
  1. #1
    LeeGro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2014
    Posts
    3

    New to Access with what seems to be a complex problem

    In excel I have a labor report that lists out labor. This report's important columns are: project number, labor type, date, and quantity. I want to summarize this data based on another report I have. This other report has the same project numbers but states different milestone dates. I want to have access take my labor hours and based on the milestone date summarize the hours before this milestone and after the milestone, by each project.

    So for example one project has 100 different labor entries each with a date. I want to summarize this labor by project, then based on if it is after a certain milestone date or before. Once I have totaled the amount of labor before and after this milestone date I want to have another field that calculates the labor distribution. So it would say: before%=beforehours/totalhours. And the same thing for the after distribution.



    I think I confused myself typing this out. Hope you guys can decipher this.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    Presume Access is linking to the Excel sheet.

    Build a query that includes the tables/links by joining on the common project number fields.

    Calculate a group ID field in query with expression:

    IIf([date]<=[milestone], "Before", "After")

    Then can make that query an aggregate GROUP BY (Totals) query and include that expression in the GROUP BY clause of the query:

    ... GROUP BY IIf([date]<=[milestone], "Before", "After") ...

    Or build a report and use report Grouping & Sorting functionality with aggregate calcs in group footer section.
    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
    LeeGro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2014
    Posts
    3
    Thanks! This worked. However, I only used the expression in the field section and selected group by in the total section and it worked. I didnt need to put the expression in the totals group by... I dont think. Any reason I would?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    Switch to SQL View and look at the resulting SQL statement. Is there GROUP BY clause?

    If you want the summation by Project, labor type - those fields would also have to be in the GROUP BY clause.
    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.

  5. #5
    LeeGro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2014
    Posts
    3
    Not exactly sure what you mean but here is what there is.

    SELECT [Project Dates].[PROJ #], IIf([START DATE]<[TMD],"Pre-Fac","CAT1/CAT2") AS Classification, Sum([Labor Detail].[TOT HRS]) AS [SumOfTOT HRS]
    FROM [Labor Detail] INNER JOIN [Project Dates] ON [Labor Detail].[PROJ #]=[Project Dates].[PROJ #]
    GROUP BY [Project Dates].[PROJ #], IIf([START DATE]<[TMD],"Pre-Fac","CAT1/CAT2");

    This is the result.
    PROJ # Classification SumOfTOT HRS
    C16846 CAT1/CAT2 3162
    C16846 Pre-Fac 2068.25

    Now I want to put another column that calculates the %. So I would like a column labeled % distribution. This column would be the percent for each classification. So cat1/cat2 would be 3162/(3162+2068). This file is just a test file there will be hundreds of projects in the final so I need some way of distinguishing them in the calculation column.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    What is it you are not sure of? See the GROUP BY clause just as I described?

    If you want percentage, I recommend you build a report. Accomplishing all in a query would require a nested subquery or DSum() to get the sum of the records. Let the report do this heavy lifting.

    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be ProjNo, ProjNum, Proj_Num.
    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.

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

Similar Threads

  1. Complex problem, hopefully a simple solution?
    By g4tv4life in forum Queries
    Replies: 1
    Last Post: 03-28-2014, 11:30 AM
  2. Replies: 6
    Last Post: 08-15-2012, 04:05 PM
  3. Complex Problem with Access 2007, Pls help.
    By adornis in forum Access
    Replies: 2
    Last Post: 12-31-2011, 05:07 PM
  4. complex design problem
    By Madmax in forum Access
    Replies: 2
    Last Post: 12-09-2011, 08:25 AM
  5. Complex Lookup Problem
    By alagrad94 in forum Programming
    Replies: 5
    Last Post: 07-27-2011, 10:24 AM

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 - Senior Forums