One table, dupe address, diff owners

[boethos]

I have a table with

John Doe, 123 main street
Bo Derek, 123 main street

in the same table. Name and Addr are in same table, different columns.

I'd like to check for dupe addressess with different name, indicating that the property may have been sold to Bo Derek from John Doe.

I can find dupes, but need to have it check both columns for differences
(show me records of addresses with two (or more) different owners)

Thank you.

 

[MarkK]

Try something like...

SELECT Count(*) As CountOf, Address
FROM Table
GROUP BY Address
HAVING Count(*) > 1

Mark

 

[plog]

1. Mark's solutions assumes that your records are unique. Meaning, Bo Derek doesn't have 2 records both with 123 Main Street as the address. If its possible you have duplicate records (not just addresses), then you will need a subquery:

SELECT [Name], [Addr] FROM YourTableNameHere GROUP BY [Name], [Addr]

Then you would use Mark's method on that query.

2. 'Name' is a poor choice for a field name. It's a reserved word and makes coding more difficult, I suggest you rename it.

3. Imperfect matching will skew your results:

A: Johnathan Derek, 123 Main Street
B: John Derek, 123 Main Street
C: Bo Derek, 541 Broadway
D: Larry Derek, 541 Broadway St.
E: Jim Derek, 541 Broadway Street

Using the above method (my sub and Mark's main query), you will find that A & B match (because they have the same address and different first names) and no others. Since A & B have different names they will be considered different owners and show in the final query. Also, to a computer; C, D & E are all unique addresses even though to a human we know they are the same. Since they are unique and have no exact matches, they will not show in the duplicate query you have built.

 

[boethos]

Try something like...

SELECT Count(*) As CountOf, Address
FROM Table
GROUP BY Address
HAVING Count(*) > 1

Mark

I''ll give it a try.

 

[boethos]

I used 'name' to keep it simple. It's actually different than that.

Bo Derek MIGHT have 2 records both with 123 Main Street.
-I'll try the subquery

If the address is the same, I do want to see Bo Derek, John Derek, Bubbles Derek, so on.

This is for billing, so I need to know if owner changed hands.

Thanks for the info. I"'ll give them a try.

 

[boethos]

The module barks on 'Count' and wants a Case statement.

What would be the full module code; after Option Compare Database?

Do I have to Dim anything?

Name is Both_name and Address is Both_addr.
I combined two tables for May and June billing (noting a column of which month the record is from) and checking to see if the owner changed from one month to the next. I can then just pull June entries for current owners.

Thus "Show me addresses where the owner changed" is my goal. If Bo Derek has dupe entries for the same property, I can pull it out finding dupes.

I"m not worried about fuzzy logic. We should see any discrepancies so we can standardize spelling.

Thanks, guys.

 

[boethos]

1.

3. Imperfect matching will skew your results:

A: Johnathan Derek, 123 Main Street
B: John Derek, 123 Main Street
C: Bo Derek, 541 Broadway
D: Larry Derek, 541 Broadway St.
E: Jim Derek, 541 Broadway Street

Using the above method (my sub and Mark's main query), you will find that A & B match (because they have the same address and different first names) and no others. Since A & B have different names they will be considered different owners and show in the final query. Also, to a computer; C, D & E are all unique addresses even though to a human we know they are the same. Since they are unique and have no exact matches, they will not show in the duplicate query you have built.

C,D,E may be problematic, since we are getting addresses from different departments and there is no standard format or syntax for entering that info.
That'll be my next task, cleaning up the inconsistencies.

 

[plog]

The module barks on 'Count' and wants a Case statement.

What module? Both our solutions are SQL only. You should be using a query object. Also,

We should see any discrepancies so we can standardize spelling.

You will not be seeing any discrepancies. If one and only one record has '541 Broadway' and one and only one record has '541 Broadway Street' neither will show up anywhere in your duplicate query, because they are not exact duplicates.

 

[boethos]

What module? Both our solutions are SQL only. You should be using a query object. Also,

ok

You will not be seeing any discrepancies. If one and only one record has '541 Broadway' and one and only one record has '541 Broadway Street' neither will show up anywhere in your duplicate query, because they are not exact duplicates.

How many characters deep does Access use for a sort? Can I change it? If it only looks at the first 15 char, then those three will show as the same address. I'd like to be sure it sorts the entire field.

 

[boethos]

I''ll give it a try.

Ok. I got that to work, but it only gives me a count of how many times it's duped. I need the table to show the addresses and owners after it selects duped addresses.

 

[plog]

Instead of piecemealing us there, how about you give us big picture what you want to accomplish. Sample data to demonstrate it would help as well. Provide 3 things:

A. An overview of what you hope to accomplish without any database jargon. Explain what you ultimately hope to achieve.

B. Sample starting data. Include table and field names and enough data to cover all cases.

C. Expected results of B. Show what you hope to end up with when you feed this system the data from B.

 

[boethos]

Instead of piecemealing us there, how about you give us big picture what you want to accomplish. Sample data to demonstrate it would help as well. Provide 3 things:

A. An overview of what you hope to accomplish without any database jargon. Explain what you ultimately hope to achieve.

B. Sample starting data. Include table and field names and enough data to cover all cases.

C. Expected results of B. Show what you hope to end up with when you feed this system the data from B.

See attached Access Database sample.

A&B will show as different names, only because they are input in different format.
C&D will show as different names so we might know that the property changed owners.
E&F Same.
G&H Possibly changed owners, but it should be selected since the names are different.
My goal is to find property that may have changed owners from one month to the next as stated before.

Expected results:
The table above will be my result; month (of the billing), name and address of property records that may have changed owners since last billing.

Thank you!

 

[plog]

I don't understand your data. Is the attached file the starting data or expected results?

Please give me both sets of data. The only explanation I need is the overview of what the ultimate goal is.

 

[boethos]

I don't understand your data. Is the attached file the starting data or expected results?

Please give me both sets of data. The only explanation I need is the overview of what the ultimate goal is.

I changed it to an access file. It has raw data; both addresses with new owners and addresses with different owners. I'd like to select (to a new table, or whatever) the addresses that may have changed owners from one month to the next so we can change who we bill to. Ultimate goal is an Access Report.

The whole file has 33,000 records in the combined (May & June) table.

 

[plog]

Last chance:

I want 2 sets of data:


A. Sample starting data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with when you feed this system the data from A.

 

[boethos]

Last chance:

I want 2 sets of data:


A. Sample starting data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with when you feed this system the data from A.

That's in the access database I sent.

 

[plog]

I see one table. I expect 2 sets of data.

 

[Pat Hartman]

Have you found the "find duplicates" query wizard? It will build the query for you if you are unfamiliar with SQl.

 

[boethos]

Last chance:

I want 2 sets of data:


A. Sample starting data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with when you feed this system the data from A.

I'm getting you a sample output table.

 

[boethos]

Have you found the "find duplicates" query wizard? It will build the query for you if you are unfamiliar with SQl.

Here's the db with both tables.

Even though addr like 1001 10th ave have names rearranged, we can handle that. We'll just standardize the format.

1002 12th Ave is what we are looking for - change of owner.
1010 10th ST is what we are looking for also. Change of owner.

The first table has 4k entries as a sample. In the interest of time I only limited the sample output to fewer.

Seems to be a lot of different spellings or formats for the same owner. Those aren't a problem.

FYI Street is different from Ave.