Results 1 to 14 of 14
  1. #1
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13

    Powerpivot, dates/ data

    Hi All,

    A question mostly PowerPivot related for data that are exported from Access:
    i would like to have a pivot table showing payroll.
    Raw data look like this:
    Click image for larger version. 

Name:	2018-12-24 11_22_09-Access - Test _ Database- C__Users_Spyros_Documents_Test.accdb (Access 2007 .png 
Views:	32 
Size:	9.0 KB 
ID:	36695
    Following some research, i created a Date table on PowerPivot that i linked with my table above.
    My aim was to create a pivot table that shows all months, not just the ones where there is relevant context.
    It didn't work (even though i suspect that's an easy fix)
    Click image for larger version. 

Name:	2018-12-24 11_24_06-Book1 - Excel.png 
Views:	32 
Size:	6.9 KB 
ID:	36696
    that would be my first question then!

    The second question, is there a way to fill in all gaps with the immediate previous salary change till a new change occurs?


    Click image for larger version. 

Name:	2018-12-24 11_25_20-Book1 - Excel.png 
Views:	32 
Size:	7.4 KB 
ID:	36697
    i.e. repeat Andy's 25000 from Oct-2011 till Apr-2015 (green rectangular) and so on?
    That way, i'll have a table with the entire payroll per month.

    Many thanks in advance

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can assure column headers even when there is no data by specifying them in CROSSTAB query design http://allenbrowne.com/ser-67.html#ColHead

    But if you want a value in every cell, there must be records.
    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
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13
    Many thanks June7, it's a shame that can't be done automatically. In that case I might need to create one record per month per employee which will kind of eliminate the need to force months with CROSSTAB as there will be records for each and every month.
    Cheers!

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is this what you had in mind (look at the qySalaryPivot), you would need a "dates" table.

    Cheers,
    Vlad
    Attached Files Attached Files

  5. #5
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13
    Hi Gicu,

    Oh, an approach through queries! Thanks a lot, yes, as long as Access will generate records by itself to be used on the pivot as June7 suggested, would be grand.
    Just a quick request though as i'm not familiarised with this code and i'm sure it'll be an easy fix for you:
    Even though qtSortedSalary holds the correct Promotion Months, qtSalaryPivot returns pay rises in the next whole year rather than month, i.e. all pay rises occur in January regardless of the Promotion Month

    Many thanks

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure what you mean, have a look at the attached sample, Spyros gets his latest promotion on May 2017 and the new salary shows up next month in June 2017. Please note that I added the month name and year month fields to the dates table to make it easier for the pivot.

    Cheers,
    Vlad
    Attached Files Attached Files

  7. #7
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13
    Hmm.... please see below.
    Probably a versioning issue my end?
    Click image for larger version. 

Name:	2018-12-29 20_33_03-Window.png 
Views:	25 
Size:	61.5 KB 
ID:	36739

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See if this gives the correct results.

    As your date format is dd/mm/yyyy, Access is misinterpreting your changeover dates
    I've specified the date format as mm/dd/yyyy in qrySalaryPivot to fix that issue.
    No other changes made.
    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

  9. #9
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13
    oh god! i'm such an amateur! yes that was it! Many thanks ridders52 and especially Gicu for setting this all up!

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're very welcome but Vlad did the real work.
    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

  11. #11
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13
    Just returning on this one guys for a quick one.
    At qrySortedSalary, i brought forward the End_Date of employees so that it will stop bringing forward salaries past the date employees leave the company.
    At qrySalaryPivot then, i attempted to edit the expressions to:
    AsOfSalary: DMax("Salary","qrySortedSalary","[MSL_Name]='" & [MSL_Name] & "' AND [PromotionMonth] < #" & Format([FirstOfMonth],"mm/dd/yyyy") & "#") AND [End_Date] < #" & Format([FirstOfMonth],"mm/dd/yyyy") & "#")
    I'm receiving the following message which i'm finding slightly bizarre as i'm copy/pasting the date expression
    Click image for larger version. 

Name:	2019-01-04 10_57_43-Access - MSL Master _ Database- V__R_Restricted_HR_MSL Master.accdb (Access .png 
Views:	17 
Size:	38.4 KB 
ID:	36786
    any clue?

    Many thanks

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You have a superfluous ") before the second AND. It should be

    Code:
    AsOfSalary: DMax("Salary","qrySortedSalary","[MSL_Name]='" & [MSL_Name] & "' AND [PromotionMonth] < #" & Format([FirstOfMonth],"mm/dd/yyyy") & "# AND [End_Date] < #" & Format([FirstOfMonth],"mm/dd/yyyy") & "#")
    NOTE Your screenshot shows <= rather than <
    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

  13. #13
    kalspiros is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2018
    Posts
    13
    oh God... ugh... Thank you Colin, yes, i know about the "<=" was testing something with the dates!
    Kind Regards

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by kalspiros View Post
    oh God... ugh... Thank you Colin
    Interesting combination of words there! You're welcome ... I think
    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: 8
    Last Post: 03-15-2018, 12:23 PM
  2. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  3. Dates w/o Data
    By x596201060405 in forum Queries
    Replies: 2
    Last Post: 02-19-2016, 12:34 PM
  4. table data to powerpivot
    By tjburdette in forum Access
    Replies: 1
    Last Post: 12-12-2012, 01:14 PM
  5. Replies: 10
    Last Post: 11-16-2011, 10:58 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