Results 1 to 6 of 6
  1. #1
    tbalci is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014

    Count data from multiple fields

    This is my first post in this forum. Hi everybody!

    A quick question. Before I go, I'd like to say that I do not have any VBA experience, second, I'm not experienced in Access. Therefore I will go as simple as I can.

    I have a table (say tbl_compsect) that I am keeping the sectors the companies operate in. I have five columns to keep sector information. Users update the company information from a form with comboboxes. Sectors are written with SectorIDs but to ease things, I will go with names. Here is how my table looks like:

    CompanyID Sector1 Sector2 Sector3 Sector4 Sector5
    1 Metal Pipe Cable
    2 Auto Pipe Plastics
    3 Mining Petroleum Haulier Auto
    4 Plastics Spares Auto Cable Haulier

    I'd like to sum up the sectors in the columns, so that I have the following information to use in the reports/export to Excel (in no particular order for now , but probably will sort by the sum in the future )

    Cable: 2


    I'd like to know how to do that; to sum information by counting data from multiple fields.

    Thank you for all your assistance in advance.


  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    This is a perfect example of how working with a de-normalized database makes seemingly easy tasks much harder than the have to be. If your table was a normalized table, this would be very simple (you should not have repeating fields within a table returning the same type of information).

    A correct table design for this scenario would only have three fields:

    Then, you could do a simply Aggregate Query, grouping on the SectorEntry field and counting one of the other fields to get what you want.

    Here is a good write-up on relational database design and normalization:

    Do you have any ability to change the design of this table?

  3. #3
    tbalci is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Hi Joe,

    Thank you very much for your reply.

    I am just trying to do things in Access which cannot be done by Excel. I am not a database designer, nor an administrator, I know normalizations but I -as you see- am not good at They confuse me a lot, to be frank.

    I can make changes to the table I think. From the field names, as far as I could understand, if I am not mistaken, the table will look like this:

    CompanyID SectorID SectorEntry
    1 1 Metal
    1 2 Pipe
    1 3 Cable
    2 4 Auto
    2 2 Pipe
    2 5 Plastics
    3 6 Mining

    But in this case, I should have a different table, which has SectorID and SectorEntry fields. In this case, my tables should look like this:

    CompanyID SectorID
    1 1
    1 2
    1 3
    2 4
    2 2
    2 5


    SectorID SectorName
    1 Metal
    2 Pipe
    3 Cable
    4 Auto
    5 Plastics
    6 Mining
    7 Petroleum

    Is this correct?

    Thank you.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    If a Sector Entry is directly related to a specific Sector ID, then yes. But from your original post, that did not appear to be the case.
    For Sector 1, you had entries of Metal, Auto, Mining, and Plastics. So it did not appear there was that distinct correlation.

  5. #5
    tbalci is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Joe, thank you very much. With your guidance, I have eliminated all such fields from contact and company tables. This made a lot of work trash, but it was necessary. It saved me from hours of frustration in the future. Thank you once again for pointing me to the right direction.

    However, as always, it caused me further problems. I want the user to enter information via comboboxes. With this table setup, I can design a form, a combobox to enter sector information, that is fine. When the user wants to enter a second sector, I am stuck.

    Suppose that you have a cbo_companyname and cbo_sectorname.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	8 
Size:	3.3 KB 
ID:	18491

    User selects company and sector and saves data. Now user selects another sector and saves.

    1. The user cannot see the what he selected and saved (no visual feedback).
    2. Once the selection is made, the user cannot go back and make changes.

    How can I make the user enter multiple sectors and meantime (1) see what he entered (2) if necessary go back and make changes.

    I know I'm off topic, it's Friday late afternoon, yet I'm confused.

    Have a very nice weekend!

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Add the SectorNumber to the Form.

    When creating new records, you can have the form autopopulate the next SectorNumber upon entering the CompanyName. You can do that by placing the following VBA code in the "AfterUpdate" event of the CompanyName field:
    Private Sub CompanyName_AfterUpdate()
        Me.SectorNumber = DMax("[SectorNumber]", "TableName", "[CompanyName]='" & Me.CompanyName & "'") + 1
    End Sub
    Here is a write-up on the DMAX function for more details:

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

Similar Threads

  1. Replies: 6
    Last Post: 02-13-2014, 11:10 PM
  2. Replies: 1
    Last Post: 08-26-2013, 05:08 PM
  3. Parsing Data Into Multiple Fields
    By JeffGeorge in forum Access
    Replies: 3
    Last Post: 07-25-2013, 10:11 AM
  4. Multiple count of query fields
    By BLD21 in forum Queries
    Replies: 2
    Last Post: 05-23-2011, 01:09 PM
  5. Multiple Fields Same Data
    By cassidym in forum Database Design
    Replies: 2
    Last Post: 08-03-2010, 12:28 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