Count Distinct Values In Several Columns or Fields

[HSL]

I have a table that has 4 different columns for each customer id:

Address1
Address2
Address3
Address4

I am trying to get a count of unique values across these four columns/fields.

Any ideas on how to do this?

 

[Allen Browne]

Hmm. So customer 1 could have an address in Address1, and customer 2 could
have the same address in Address2, but you want the number of distinct
addresses across the 4 fields for all customers?

1. Create a new query.
2. Switch it to SQL View.
3. Paste this in, changing Table1 to the name of your table:
SELECT Address1 AS TheAddress FROM Table1
WHERE Address1 Is Not Null
UNION
SELECT Address2 AS TheAddress FROM Table1
WHERE Address2 Is Not Null
UNION
SELECT Address3 AS TheAddress FROM Table1
WHERE Address3 Is Not Null
UNION
SELECT Address4 AS TheAddress FROM Table1
WHERE Address4 Is Not Null;

4. Run the query, to see how many you get.

Ultimately, this is not a relational design. Whenever you see repeating
fields (such as Address1, Address2, ...), it should be broken down into
another table where one customer can have many addresses (a one-to-many
relation.)

Since you are asking about distinct addresses, you may actually have a
many-to-many relation between customers and addresses, which would need a
junction table between a customer table and an address table. Post a reply
if you need more information about that.

 

[scubadiver]

It maybe the case the number refers to each line of an address, not a
complete address.

Can you clarify?

 

[HSL]

Sorry about that...

There are four address fields (street address) for each customer Id. I am
trying to count how many distinct values are across these four address fields
for each customer. For example:

cust id address1 address2 address3 address4
00001 123 Main 122 Main 123 Main 124 Main

So for the above there are 3 distinct addresses.

Basically I brought in the address fields from 4 different customer tables
to identify any discrepancies between 4 tables that actually should contain
the same data but do not. This is a database clean up project.

Thanks in advance for the help.

 

[Ron2006]

First you should have done it more like this:

AddressTable
Fromid - tells which table it came from
Addressline - the address line

then do distince on the resulting table - simple as pie.


Given what you have you can also do this

Query 1
"Address1" literal
AddressA: Address1

Query 2
"Address2" literal
AddressA: Address2

Query 3
"Address3"
AddressA: Address3

Query 4
"Address4" literal
AddressA: Address4


Now create a union query composed of all 4 queries and use the
distinct.

Essentially we are trying to devise the better table design equivalent
from the 4 queries.

Ron