Designing a Query or Report to list all records including duplicat

[Jon H]

Hi,

I have been given a database of Company names, addresses and associated
phone numbers. They require a simple report sorted by Company , but in the
instance that 2 or more companies have the same phone number they want one
line of the recordset showing all company names associated with that number.

Any company name listed in a duplicate is not to appear again further down,
even if the sorting might normally put it there.

To clarify,
Alpha Manufacturing
Bravo Manufacturing
Charlie Manufacturing all have the same phone number.

If we weren't worried about the duplicate phone numbers then Alpha would
appear first, and the other two would each be a long way down. What we want
is something like:
Alpha Manufacturing 123 Smith Street 555-1234
Beta Manufacturing
Charlie Manufacturing
Alpha Shoe Store etc etc

Beta and Charlie would not appear again.

Any help would be greatly appreciated.

Thanks

 

[NetworkTrade]

beware the user's who call it a "simple" report....

one simple yet imperfect method: make a duplicates query based on tel

then make a no match query between the whole list compared to the duplicates
query result

therefore you have a duplicates list and a list of everyone except those
duplicates

but to do it the way you describe you need to add a numbering column ID for
the whole list in alphabetical order and then use the ID for an sql statement
to join on tel value - and that type advanced sql statement isn't something I
have at my finger tips though maybe an MVPer might....

 

[John Spencer]

A solution might me to use a query that looks like the following

SELECT B.FirstName as SpecialSortOrder,
A.CompanyName, A.Address, A.PhoneNumber
FROM YourTable as A INNER JOIN
(SELECT PhoneNumber, Max(CompanyName) as FirstName
FROM YourTable
GROUP BY PhoneNumber) as B
On A.PhoneNumber = B.PhoneNumber

Now Sort by SpecialSortOrder and then CompanyName in the Report Sorting and
Grouping Dialog.

Set the Hide Duplicates property for Address and Phone Number control to Yes.

If that doesn't quite work I have an alternative solution. Where you print the
first record of the group in a group header and any additional records in the
details section (simply hide the first record of each group).

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County