Currently I have a form with a combobox where I selected the company name. On that form the subform then displays all records that involve that company (address, city, state, zip, and other fields).
I need to somehow clean up the data and make it only display the record if it has a different address and give me a new column counting how many times that address would have showed up if I had not made it only display each address once.
In essence go from this....
To This (Taking away the dupilicate record that has the same address)
(The 2 & 1 being how many times that address appears next to the company)
CT, the way GROUP BY works is it summarizes duplicate values. If you Group by Company and Address (and only have them in the Select along with any aggregate functions), then you will get one row per unique Company/Address value. However, if you add a third column City then you have to consider three columns for unique value combinations (Company/Address/City). The more columns you add the more Unique the rows will get and less summarization will occur.
If you just want a count of the distinct Company / Address values then:
SELECT company-col, address-col, COUNT(*) AS CNT
GROUP BY company-col, address-col
In the above example, the unique combination is Company1, Address1, Count 2. Now, which City do you want displayed (City1 or City2) and how do you determine this. NOTE: you will need to make this determination for all the extra columns (unless you want to base in on one column like City and just accept the rest of the columns.