Count if query matching list

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I'm quite new and i'm looking to write a query.

I have two table ( A and B ) like

Table A
ID #1 #2 #3
1 4 5 7
2 1 4 9

Table B
ID #1 #2 #3 #4 #5 #6
1 1 2 4 5 6 7
2 1 3 4 5 7 9
2 4 5 6 7 8 9

I looking to write a query to count when Table A matching Table B

Table A count
ID #1 #2 #3 Count
1 4 5 7 3
2 1 4 9 1


Thank in advance
 
That is one non-normalized table design. I think the following will
work if there aren't too many records involved.

SELECT Count(A.1) as TheCount, ID, A.1, A.2, A.3
FROM TableA as A, TableB as B
WHERE
A.1 in (B.1, B.2, B.3., B.4, B.5, B.6)
And A.2 in (B.1, B.2, B.3., B.4, B.5, B.6)
AND A.3 In ((B.1, B.2, B.3., B.4, B.5, B.6)
GROUP BY ID, A.1, A.2, A.3

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top