If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count Distinct Values In Several Columns or Fields
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? |
#2
|
|||
|
|||
Count Distinct Values In Several Columns or Fields
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "HSL" wrote in message ... 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? |
#3
|
|||
|
|||
Count Distinct Values In Several Columns or Fields
It maybe the case the number refers to each line of an address, not a complete address. Can you clarify? "HSL" wrote: 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? |
#4
|
|||
|
|||
Count Distinct Values In Several Columns or Fields
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. "scubadiver" wrote: It maybe the case the number refers to each line of an address, not a complete address. Can you clarify? "HSL" wrote: 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? |
#5
|
|||
|
|||
Count Distinct Values In Several Columns or Fields
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 |
Thread Tools | |
Display Modes | |
|
|