Counting instances of localities

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have a basic contacts database:

Name, Address, Town, Locality, Phone number etc.

What I am trying to find out is how to create a report
that displays a count of the number of records from each
particular Locality.

i.e. I have 5 records from Edinburgh customers, 6 from
Fife, 8 from Glasgow etc. and i'd like to display a
report that said:

Edinburgh customers = 5
Fife customers = 6
etc.

Any suggestions as how to do this would be greatly
appreciated. Thank you in advance.
 
One way round this is to use something like the postcode
as a marker, with a query that COUNTs the number of
instances by postcode (the postcode region, such as EH1
for Edinburgh, or G6 for Glasgow).

If you did this, you'll need to generate the alpha-string
from the postcode. Here's a way to extract using the user-
defined function =GetPostalRegion([postcode]). If you
create a query that extracts out all the postcodes and
GetPostalRegions() them then you can requery these with
the COUNT:

'---
Function GetPostalRegion(Postcode As Variant) As Variant
'extracts out the left half of the Postcode to generate
the Postal Region

Dim x As Integer

x = InStr(1, Postcode & " ", " ")
If x > 0 Then
GetPostalRegion = Left(Postcode, x - 1)
Else
GetPostalRegion = " "
End If

End Function
'---

You could adapt this to extract out the single or double
letter-only component as well
 
Create a totals query like
SELECT Locality, Count(Locality) as NumOf
FROM tblAddresses
GROUP BY Locality;

Build your report based on this query.
 
Thanks Duane - spot on!

-----Original Message-----
Create a totals query like
SELECT Locality, Count(Locality) as NumOf
FROM tblAddresses
GROUP BY Locality;

Build your report based on this query.

--
Duane Hookom
MS Access MVP





.
Click to expand...
 
Back
Top