Count if query matching list

[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

 

[John Spencer]

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
'====================================================