Removing Duplicates with multi level queries

[John]

I am working on normalizing a poorly designed database for a friends
contruction co. using Access 2003 or 2007.

I have a table with with each job info, the contractor info, and the owner
info. Obviously this needs to be broken out into at least 3 tables.

I have started with the Owner info. Since these Owners are multiple repeat
customers, there are many records with the same owner name, some have
addresses, some don't (even though the address should be the same for each
owner)

My goal is to create a query that pulls out all the owner data into a new
table, but I want distinct names only. The field in the table I need are
Owner, Owner Address, Owner Contact, Owner Phone, Is Fed or State.

The problem is the sql: Select Distinct table.owner, table.[Owner Address],
table.[Owner Contact], table.[Owner Phone], table.[Fed/State]
with this sampling of data creates 3 records


Owner = Joe; Address = 123 Main St; Fed/State = F
Owner = Joe; Address = NULL; Fed/State = F
Owner = Joe; Address = 123 Main St; Fed/State = NULL

Obviously this is one owner who should have one address and one Fed/State
Status and the new table should reflect that... one record. . I know I need
to create a few sub queries, but am having a hard time understanding the set
theory behind it. I am kinda new to this.

Any help would be appreciated.

John

 

[John Spencer]

You can try using an aggregate query to combine the records. No guarantee
that you will get the results you want.

SELECT Owner
, Max([Owner Address]) as OwnerAddress
, Max([Owner Contact]) as OwnerContact
, Max([Owner Phone]) as OwnerPhone
, Max([Fed/State]) as FedState
FROM SomeTable

Unless you have some specific way to identify which of the values you need,
this may be the best you can do.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..