Count data from multiple fields

[tbalci]

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 )

Metal:1
Pipe:2


Cable: 2
Auto:3
Plastics:2
Mining:1
Petroleum:1
Haulier:2
Spares:1

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.

Tolga

[JoeM]

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:
CompanyID
SectorNumber
SectorEntry

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:
http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

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

[tbalci]

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

and

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

Is this correct?

Thank you.
Tolga

[JoeM]

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.

[tbalci]

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.



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!

[JoeM]

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: http://ss64.com/access/dmax.html