Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398


    When you talk about Pivot table
    I'm talking about the Pivot section of the query (last line)

    Would you mind if I have some issues to re-visit.
    if its about this specific issue, you can post to this thread, but otherwise start a new thread. I respond here when I can so you may have to wait!

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    meant to say - if your projects extend over multiple years, you may need to modify your column headers to include the year - I usually go for a number style which is easily sorted e.g. instead of 'EXP March' use something like '19-03' or 'FY19-03'

  3. #18
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44

    Update Many to May

    COSTFY TOPD JO# JOTITLE KE# KETITLE Issue Total Of Expended Hrs Exp March Exp April
    FY-19 PJK3 B7501002 PATT 3 BRK 225 202 202
    FY-19 SFS1 5ZL50330 MFR 2 CHILL 240 274 274

    TRANSFORM Sum(LABORDFILE.[Expended Hrs]) AS [SumOfExpended Hrs]
    SELECT JOBORD.COSTFY, JOBORD.TOPD, JOBORD.[JO#], JOBORD.JOTITLE, JOBORD.[KE#], JOBORD.KETITLE, JOBORD.Issue, Sum(LABORDFILE.[Expended Hrs]) AS [Total Of Expended Hrs]
    FROM JOBORD LEFT JOIN LABORDFILE ON (JOBORD.[JO#] = LABORDFILE.[JO#]) AND (JOBORD.TOPD = LABORDFILE.TOPD)
    GROUP BY JOBORD.COSTFY, JOBORD.TOPD, JOBORD.[JO#], JOBORD.JOTITLE, JOBORD.[KE#], JOBORD.KETITLE, JOBORD.Issue
    PIVOT "Exp " & Format([Trans date],"mmmm") In ("Exp March","Exp
    April");

    Alax: It worked perfectly. One question if may. If I have the fiscal year in the end result would I need to Format the "Exp " to 3-19 or FY 3-19. Appreciate you opinion.

    Again thanks a mil

    Gene

  4. #19
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it will be text so will be sorted as text

    FY19-3

    will come after

    FY19-10

    So you need

    FY19-03

    I'll leave you to figure out the impact if you use

    FY3-19
    FY10-19

    let alone if you go over a financial year

    FY12-19
    FY11-20

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Many to one Relationship?
    By TastyOs in forum Queries
    Replies: 1
    Last Post: 11-09-2016, 05:46 PM
  2. One to many Relationship
    By mjf8563 in forum Access
    Replies: 14
    Last Post: 10-27-2016, 01:53 AM
  3. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  4. Replies: 1
    Last Post: 12-06-2014, 12:49 PM
  5. Replies: 5
    Last Post: 11-30-2011, 07:02 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