Matching two tables with count

[ImiAccess]

I'm very much an Access beginner but currently trying to create a query in which I can select a count across two tables with matching postcodes.

Table 1 : Address database with column called 'STRIPCODE' (e.g. ME8, ME9, ME10)
Table 2 : Area Code table; each column has a list of area codes to match to the STRIPCODE column. (e.g. MEDWAY column will have all of the relevant area codes)

I would pretty much want a count of each match as below :

STRIPCODE
MEDWAY 650
BLACKPOOL 563
HULL 352

Have a few hundred areas to match and tired of going through these manually and entering counts!

Hope this makes sense.

 

[pr2-eugin]

You need to JOIN the two tables and then use a TOTALS query. Something like.

SELECT table1.descriptionColumnName, Count(table2.someFieldName) As CountOfSomething
FROM table1 INNER JOIN table2 ON table1.ID = table2.ID
GROUP BY table1.descriptionColumnName;

 

[ImiAccess]

Thanks but as mentioned I'm pretty much a beginner.

I'm currently doing a query to match the postcodes from both tables and jotting down the figure of matches I have. However, I have round 300 postcodes to match and could take up most of my day! ;o)

Any simpler explanation?

 

[pr2-eugin]

Okay let me explain, JOIN is the concept of bringing two tables related information together. As you have, One table has all the addrresses, the other table is a description of such address. So when you want to get the Description you simply marry them together, there are two JOINS that can happen, INNER Join or OUTER Join. OUTER JOIN is simply divided as RIGHT and LEFT JOIN. More information of what JOINS can do, and how to effectively use them can be learnt here:

http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_join_inner.asp
http://www.w3schools.com/sql/sql_join_left.asp
http://www.w3schools.com/sql/sql_join_right.asp

Next, TOTALS is a concept to get the Aggregation of the data, some of the common aggregation function used as Count, Sum, Max etc. So you need the Count of all the StipCode GROUPED BY the AreaCode. So that is what we have done in the following. Just make sure you rename the table and field names to suit your need.

SELECT Table2.AreaCode, Count(Table1.STRIPCODE) As CountOfStripCode
FROM Table1.STRIPCODE INNER JOIN Table2 ON Table1.STRIPCODE = Table2.STRIPCODE_ColumnName
GROUP BY Table2.AreaCode;