access query to filter and combine count

Question

i have two access tables

tableA

num  count
1    7
2    8
3    9
4    9
5    13
6    6


tableB

num  count
0    1
1    14
2    12
3    5
4    5
5    11
6    5

how can i create an access query that will ignore the numbers which have count less than 6 in any of the two tables. i.e. 0,3,4 & 6 and create a table with the rest of the numbers sorted by combined count

tableC

num    count
5      24
1      21
2      20

any help appreciated

Answer 1

How about

SELECT x.Num, x.Count FROM (
  SELECT Num, Count(*) 
  FROM tableA
  GROUP BY Num
  HAVING Count(*)>6

  UNION ALL

  SELECT Num, Count(*) 
  FROM tableB
  GROUP BY Num
  HAVING Count(*)>6) x

Or if count is a field, rather than a calculation:

SELECT x.Num, x.Count FROM (
  SELECT Num, Count
  FROM tableA
  WHERE Count>6

  UNION ALL

  SELECT Num, Count
  FROM tableB
  WHERE Count>6) x

Answer 2

Maybe....

SELECT a.num, a.count + b.count
FROM   tableA a
JOIN   tableB b on b.num = a.num
WHERE  a.count >= 6 
AND    b.count >= 6

this will include numbers which are in both A and B. To include numbers with count >= 6 that are in one table and not the other you'll have to add a Join and a "isnull" for the a.count and b.count values. ie; isnull(a.count,0) + isnull(b.count,0)

Answer 3

You can try something like this

SELECT DISTINCT tableA.num, [tableA].[val]+[tableB].[val] AS Expr1
FROM tableA INNER JOIN tableB ON tableA.num = tableB.num
WHERE (((tableA.val)>=6) AND ((tableB.val)>=6));