Results 1 to 4 of 4
  1. #1
    marinerfm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    2

    Post SUM With Criteria

    New to MS Access. Trying to convert my excel files for database. have a sumifs formula in a field with multiple criteria.
    Need to calculate Field 5 which is difference of current cell of Field 3 to the last entry of Field 3 when Field 1, Field 2 and Field 4 criteria are met.



    i require answer of Field 5 in Record 3 = 1000 when Field 1 = removed, Field 2 is E1 and Field 4 is A
    I require answer of Field 5 in Record 4 = 900 when Field 1 = removed, Field 2 is E2 and Field 4 is B


    Record Field 1 Field 2 Field 3 Field 4 Field 5
    1 Installed E1 0 A 0
    2 Installed E2 0 B 0
    3 Removed E1 1000 A ?
    4 Removed E2 900 B ?

    My excel formula is =IF(AND(B27<>"",C27<>"",G27<>""),IF(C27="Removed", SUMIFS($F$5:F27,$C$5:C27,"Removed",$B$5:B27,"<="&B 27,$G$5:G27,G27)-SUMIFS($F$5:F27,$C$5:C27,"Installed",$B$5:B27,"<=" &B27,$G$5:G27,G27),SUMIFS($F$5:F27,$C$5:C27,"Remov ed",$B$5:B27,"<"&B27,$G$5:G27,G27)-SUMIFS($F$5:F27,$C$5:C27,"Installed",$B$5:B27,"<"& B27,$G$5:G27,G27)),"")

    Or if anyone can point to any tutorials. That would be great also.

    Thanks

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access works with field names rather than cells

    the equivalent of a sumif in a query would be something like

    select fld1, sum(iif([fld3]="removed",[fld4],0) as sumofremoved
    from mytable
    group by fld1

    can't recommend any tutorials to convert excel to access (or any db format) but google something like 'sql equivalent to sumif' or similar to find plenty of examples where the question has been asked before.

    Remember databases are not a larger excel - Excel tends to store data horizontally (short and wide) and combines data storage and presentation in the same view. Databases store data in tables (tall and thin) and uses queries, forms and reports for presentation.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    marinerfm,

    Further to Ajax's advice, if you are going to use Access you really need to learn/understand some database concepts. As he said, Access is not a larger Excel. These are 2 software products built on different object models (underlying concepts) serving different audiences.
    Quite often in these forums we see posters who have great Excel/spreadsheet experience, but are frustrated with "Access" because it is different. Many will suggest that you have to "unlearn" some Excel practices to use Access. "Cells" and "rows" are quite common.
    I recommend you work through one or two tutorials, and watch a couple of database-related videos below.

    Here are some sample tutorials that you should work through to understand and experience the database design process.
    Each tutorial comes with a problem statement, the process and a solution. Do work through some of the samples, follow the procedure and compare your answer with the solution provided. You will learn the basics that can be used with any database.

    Tutorials:
    Entity Relationship Diagramming
    Class information
    Catering Business
    Consolidated Widgets

    These are from Rogers Access Library other links on this page serve as excellent references. Especially Normalization and the 12 steps/nutshell article.

    Each tutorial should take about 30-60 minutes---less after you understand the process.

    Also, here are some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

    Intro to Database
    The Relational Model
    Data Modelling and the ER Model

    Here is a different set of videos by a different presenter to complement those above.
    These are free youtube videos from Mark Serva that deal with entity relationships, modelling, cardinality, ....
    The playlist is listed here.


    Your specific question concerning conversion of Excel formulas to some Access constructs would involve a considerable amount of vba coding. If you could write an Excel formula into plain English logic (pseudo code), then your "conversion" could be easier for someone to help with the vba. Many Access/vba users are not experienced with Excel, and would not necessarily know what the Excel formula "means". There are some (not me) who are "ambidextrous" and can use Access/Excel with equal proficiency.

    Good luck with your project.

  4. #4
    marinerfm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    2
    Thanks. For the short term I may have to use excel but for longer term will start access learning from the helpful content you provided and apply it according to my needs.
    Thanks a lot again.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-15-2016, 05:56 AM
  2. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  3. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  4. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  5. Replies: 5
    Last Post: 08-02-2012, 09:44 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