Get unique addresses

  • Thread starter Thread starter Gerald Stanley
  • Start date Start date
G

Gerald Stanley

For part 1, try something along the lines of
SELECT DISTINCT HouseNum, AddressDir, AddressStreet,
AddressCity
FROM etc

For part 2
SELECT Count(1) FROM
(SELECT DISTINCT HouseNum, AddressDir, AddressStreet,
AddressCity
FROM etc)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hello -

I have a table with Fname, Lname, HouseNum, AddressDir,
Click to expand...
AddressStreet, AddressCity and I need to get a list of
Unique Addresses (in other words, just households). I also
need to display a Count Number.
 
Gerald

Thanks so much for your response

Why is the "1" in parentheses after "Count"? What does the "1" stand for . . . (you'll have to excuse me - I'm really rusty at this)

Sand

----- Gerald Stanley wrote: ----

For part 1, try something along the lines o
SELECT DISTINCT HouseNum, AddressDir, AddressStreet
AddressCit
FROM et

For part
SELECT Count(1) FRO
(SELECT DISTINCT HouseNum, AddressDir, AddressStreet
AddressCit
FROM etc

Hope This Help
Gerald Stanley MCS
-----Original Message----
Hello
Click to expand...
AddressStreet, AddressCity and I need to get a list of
Unique Addresses (in other words, just households). I als
need to display a Count Number
 
Hi,



use *, don't use 1. That is an old trick that has no reason to be used
in recent databases. It was used to count something you would be sure that
was "not null", since COUNT( fieldName ) just count the not-null values.
The more standard syntax is to use COUNT(*), and can even be faster, since
it can be optimized in some circumstances where COUNT( expression ) is not
necessary.

If you SELECT COUNT(*) FROM somewhere without GROUP BY, you will get the
total number of records in "somewhere", if you add a GROUP BY, COUNT(*)
return the number of records, in each group.


SELECT Address, COUNT(*)
FROM addresses
GROUP BY address

will give you AND unique address, and how many time they where mentioned in
addresses, with just one query.




Hoping it may help,
Vanderghast, Access MVP






Sandy said:
Gerald -

Thanks so much for your response.

Why is the "1" in parentheses after "Count"? What does the "1" stand for
Click to expand...
.. . . (you'll have to excuse me - I'm really rusty at this).
 
Back
Top