Results 1 to 5 of 5
  1. #1
    fratilaionut is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Location
    Bucharest
    Posts
    6

    Transpose excel MAXIF to acces expresion?

    Hello,



    So I am trying to build a little program in Microsoft access and i have done a simple crosstab where i have for row headings some usernames, dates as column headings and as values i have hours.
    The idea is that in the main table ( tbl_activitate) for those usernames in each day i have more hours values written. I want to somehow do a sum of MaxIF - MinIF in this crosstab by value if it is on the column heading date in table.

    I have done this in an excel, but i don't know how to do it in access.
    Click image for larger version. 

Name:	crosstab.png 
Views:	23 
Size:	14.8 KB 
ID:	48789

    Users - the rowheading of usernames;
    Data confirmare - the column headings with dates;
    Ora confirmare - the values ( hours );

    Sorry and thanks in advance, but i'm new to Acces and I am self-taught. I want to develop mysfel and I also don't know how to write a code in SQL.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Ionut,
    Can you show us some sample data and the desired output? Usually in Access you would use domain aggregate functions (DMax, DMin) to replicate MAXIF.
    https://learn.microsoft.com/en-us/of...plication.dmax
    Bine ai venit la forum!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    fratilaionut is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Location
    Bucharest
    Posts
    6
    Quote Originally Posted by Gicu View Post
    Ionut,
    Can you show us some sample data and the desired output? Usually in Access you would use domain aggregate functions (DMax, DMin) to replicate MAXIF.
    https://learn.microsoft.com/en-us/of...plication.dmax
    Bine ai venit la forum!
    Cheers,
    @Gicu, firstly, thank you for answering on this post.

    Click image for larger version. 

Name:	Excel.jpg 
Views:	16 
Size:	249.5 KB 
ID:	48804
    This is the data that i create the pivot from.

    Click image for larger version. 

Name:	Excel2.jpg 
Views:	16 
Size:	236.6 KB 
ID:	48805
    And this is the output that i want.

    I have created a pivot with only row heading from the first table, then i put the column headings with dates and after this i used this formula the get the data that i want: =MAXIFS(Activitate!L:L;Activitate!B:B;'Ore lucrate in Sistem'!A2;Activitate!K:K;'Ore lucrate in Sistem'!$B$1)-MINIFS(Activitate!L:L;Activitate!B:B;'Ore lucrate in Sistem'!A2;Activitate!K:K;'Ore lucrate in Sistem'!$B$1).

    I did not found many examples and I have tried with Dmax and Dmin but it didn't work out for me, or I should better say that i don't know how to use it.

    Thank you!

  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
    Maybe see if this helps, you can create you Excel pivot from Access:
    http://forestbyte.com/ms-access-util...able-designer/
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    fratilaionut is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Location
    Bucharest
    Posts
    6
    Hi again,

    That app wasn't working for me.
    I found another solution by creating more queries.

    You can close the topic now.

    Thank you !

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

Similar Threads

  1. Transpose linked Excel table in Access365
    By mike_b in forum Access
    Replies: 7
    Last Post: 08-15-2019, 09:40 AM
  2. send data from acces to excel
    By Husseintech in forum Queries
    Replies: 3
    Last Post: 01-26-2016, 04:06 PM
  3. Export to Excel in Transpose Format
    By abdulnaseer in forum Import/Export Data
    Replies: 1
    Last Post: 01-30-2013, 02:18 PM
  4. Replies: 1
    Last Post: 11-17-2011, 12:06 PM
  5. Acces or excel, for handleing my data?
    By oo0tommyk0oo in forum Access
    Replies: 1
    Last Post: 07-07-2011, 11:27 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