Count different 2 field in 1 table at Access Query

[yosiro]

I have 2 table, below:

Tabel1
-------------------
ID C1 C2
-------------------
1 QQ QB
2 QB QC
3 QC QB
4 QC QQ
5 QC QQ
6 QC QB
7 QQ QC
8 QC QQ
9 QC QB
10 QQ QB

Tabel2
----------
ID A1
----------
1 QQ
2 QB
3 QC


So i want to create a query like this:

A1 Total
QQ 6
QB 6
QC 8

Please help me, i use MS access 2013, thanks

[jforbes]

You are not very clear with your question, so this is just a guess at what you are looking for:

SELECT 
   A1
, (SELECT COUNT(C1) FROM Table1 WHERE Table2.A1=Table1.C1 OR Table2.A1=Table1.C2) AS Total 
FROM Table2

[NeoPa]

Let's start by asking the question properly, shall we? Your job really, but I'm guessing you don't speak English natively so this might be easier for me than it is you. You just need to say if I get it wrong.

So, you would like a query that counts how many of each value are found in either column of Table1. I would guess Table2 is irrelevant and is only there because you hadn't realised that yet.

The way I would do that is to create a sub-query for each column (field) and UNION the results together.

Something like :

SELECT   [CField]
       , Sum([CountC]) AS [TotC]
FROM     (SELECT   [C1] AS [CField]
                 , Count(*) AS [CountC]
          FROM     [Table1]
          GROUP BY [C1]
          UNION ALL
          SELECT   [C2] AS [CField]
                 , Count(*) AS [CountC]
          FROM     [Table1]
          GROUP BY [C2])
GROUP BY [CField]
ORDER BY [CField]

[NeoPa]

Now, depending on whether or not it's possible to have Table1 records with the same value in both C1 & C2, JForbes' suggestion will either work or it won't. If it isn't possible then his will work, but will need both tables. Hardly a problem I would expect. If it is possible, of course, then his suggestion won't be accurate.

I would recommend changing the Count() parameter to *, but that's just for safety. In most scenarios it'll work fine just as it is.

[yosiro]

Am sory to make confuse coz my bad english, am from indonesia.
I want to revision about table 1. here's the image:



And this is the file : https://www.dropbox.com/s/mzszyt120i...se1.accdb?dl=0

So i want to create query that can count total of each Group of Table 2 in Table 1. I hope you are get it what i mean.

[yosiro]

I change the code from jForbes to my latest post, thanks man its work

SELECT 
   A1
, (SELECT COUNT(C1) FROM Table1 WHERE Table2.ID=Table1.C1 OR Table2.ID=Table1.C2) AS Total 
FROM Table2