Results 1 to 3 of 3
  1. #1
    noel is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    1

    Searching for a specific calculate/ count function

    Hello community,



    currently, I wanna convert an excel table into an access database.

    The excel table has 9 columns (Last Name, First Name, Card-number, Event, Event year, No. of Events of 2019, No. of Events in 2018, No. of Events in 2017, No. of Events from 2017).
    My composite primary key is "Last Name, First Name, Card-number, Event, Event year".

    So now my problem:
    My database should search through the whole table and calculate automatically the columns
    "No. of Events in 2019, No. of Events in 2018, No. of Events in 2017, No. of Events from 2017".

    I've made an example table below:
    Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	88.8 KB 
ID:	40295Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	88.8 KB 
ID:	40295

    In excel, I use the command "=COUNTIFS(A:A;A2;B:B;B2;C:C;C2;E:E;2019)" to get the number of all Events in 2019 for each customer.

    How can I get such an overview in access?

    Thanks a lot for your help!
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    note that excel sheets do not translate to Access database tables.
    your design is slightly different.
    youd need 2 tables:
    tPersons
    tEvents

    tPersons table:
    PersonID
    FirstN
    LastN

    tEvents
    EventID
    Event
    EventYr

    then you need queries to sum the events for the year / person.
    (you don't import functions)

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    suggest you study normalization. I don't see how the suggested table structure allows you to associate an event with a person. Rather than debate that, you'd be better off understanding how to normalize your tables with the entire scope of project requirements in mind. I was going to post links just for normalization but it's just as easy to throw everything at you and hope it helps to avoid a lot of pitfalls. Access is easy to do badly and it has quite the learning curve.
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 17
    Last Post: 03-30-2018, 02:54 AM
  2. Replies: 17
    Last Post: 08-09-2016, 07:15 AM
  3. Replies: 6
    Last Post: 07-27-2015, 10:23 AM
  4. Searching function in querries
    By sdc1234 in forum Queries
    Replies: 1
    Last Post: 06-25-2013, 03:02 PM
  5. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM

Tags for this Thread

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