Query counts - multiple columns

[rashutaarbl]

Hi everyone

I'm very new to access in general and I started to learn it because I was asked to work on a database at my new work. So literally I learned everything along the way. Now I am stuck as I want to generate some statistics and charts but I need to generate some counts on my tables.

So I have a table with the following columns:

ID | Substation | Meter1 | MProducer1 | Meter2 | MProducer2 | Meter3 | MProducer3 | Meter4 | MProducer4

Each substation has 4 different types of meters of different production companies (let's say 3 ABB, SCHNEIDER and SIEMENS). On the Meter columns I have the serial numbers of the meters.

What I am struggling to do is getting the total count and sum of how many meters are from ABB, SCHNEIDER and SIEMENS from ALL 4 columns.
My initial idea was to make a Query for each column and use group by and count but when I wanted to sum all 4 queries, I just lost it.

What I want to achieve at the end is to have a query like for example:
ABB | 20
SCHNEIDER | 10
SIEMENS | 10
SUM | 40

Any suggestion what should I do and how? Thank you in advance

 

[MajP]

Look at a union query which can put it into a single meter and producer column

 

[MajP]

https://www.w3schools.com/sql/sql_union.asp union

 

[jdraw]

I have a suspicion that there is more to your business than counting meters by manufacturer.
Can you tell us about the tables in your database?
Good luck.

 

[Galaxiom]

The table structure is wrong. All Meters and Producers should be stored in the same two fields rather than numbered instances holding the same types of information.

Getting the required results would be trivial with the right structure using Group By manufacturer. Until the structure is corrected you will be forced to keep doing clumsy workarounds like the Union query suggested by MajP.

 

[MajP]

If you do the union query you can then do a make table query to get the proper structure. You would not have to re-enter any data

 

[rashutaarbl]

If you do the union query you can then do a make table query to get the proper structure. You would not have to re-enter any data

Thank you so so much, it works perfectly for my case! Really appreciate your help ☺

The table structure is wrong. All Meters and Producers should be stored in the same two fields rather than numbered instances holding the same types of information.

Getting the required results would be trivial with the right structure using Group By manufacturer. Until the structure is corrected you will be forced to keep doing clumsy workarounds like the Union query suggested by MajP.

Yeah I figured this out later but when I made the forms, I wanted for a selected record(substation in my case) to have all the meters shown on that particular record. Maybe there was a simpler way to do that as well without complicating the structure of the table, but that's how I thought it would work for my form without taking into consideration later that I would face this problem to generate whatever counts I needed for my charts. If you have any suggestion please let me know. Thank you for your input, really appreciate it.

 

[MajP]

If you have any suggestion please let me know.

You would have tblStations with a substation id and other fields related to that station
Then you would have tblMeters with a link to which substation
tblMeters
---MeterID
---Producer
---Serial
---SubStation_FK 'links back to the SubstationID

then you have a Main form with a meter subform. So you can enter a substation and in the subform list the information for as many meters that you like.