Grouping

[joeromero87]

Hi, I've got a question that relates to reporting. I've got a database of client contact information (email, address, phone, etc.). I'd like to run a report that returns all addresses, emails, phone numbers for each client (some clients have multiple of each) without duplication. For a client with 2 addresses and 2 emails, my query returns the following:

Client1	Address1	Email1
Client1	Address1	Email2
Client1	Address2	Email1
Client1	Address2	Email2

This is obviously not an ideal presentation. I've managed to accomplish the following with grouping:

Client1
	Address1
		Email1
		Email2
	Address2
		Email1
		Email2

But still, there is a duplication of the emails. I want a presentation like:

Client1
	Address1
	Address2
	Email1
	Email2

Is this possible? Any help would be appreciated.

-Joe

[orange]

I think an appropriate table structure would be

1 Client has 1 or many MailingAddresses
1 Client has 0 or more Email Addtresses

3 tables

tblClient
ClientID PK
ClientFirstName
ClientLastName
ClientDOB
other Client stuff

tblMailingAddress
MailingAddrID PK
MailingAddrLine1
MailingAddrLine2
MailingAddrCity
MailingAddrState
MailingAddrZip
ClientID FK


tblEmailAddress
emailAddr ID
emailAddr
ClientID FK

  Client ---> MailingAddress
     | 
     +------>EmailAddress

Good luck.

NOTE: Just saw Steve's post. His approach should work with your current structures.
Good point Steve re use the report sorting and grouping. Don't rely on the query.

[ssanfu]

For the report, you might be able to use a Union query.
Try:

SELECT DISTINCT Customers.Customer, Customers.Address , "A" AS X
FROM Customers
UNION 
SELECT DISTINCT Customers.Customer, Customers.Email, "E" AS X
FROM Customers
Order By Customers.Customer, X, Customers.Address;

The "A" AS X and "E" AS X are to be able to sort the addresses first, then the email addresses. Use in the report Sorting and Grouping....