Nearly identical records in a table.

[James O]

Hey All,

Quick question I hope. I have a table filled with customer names and their
data. Each record for a customer is nearly identical except for some dollar
amounts and a creation date field.

What I need is a list of all our customers but I just want one record for
each customer. Thank you so much for any help you can offer.

James O

 

[Michel Walsh]

You can use an UNION query. Have to type in SQL view:

SELECT customerName, someOtherField FROM table1
UNION ALL
SELECT customerFIeldName, againAnotherField FROM table2


Note that the number of fields in each list, must be the same, and the
fields must be datatype compatible (ie, the data type of someOtherField from
table1 should roughly match the one of againAnotherField from table2) but do
not need to be of the same name.


Save that query, say, under the name qu.

Next, make a total query like:

SELECT customerName, LAST(someOtherField)
FROM qu
GROUP BY customerName



If you have more fields, in qu,, use LAST for each of them except for
'customerName':

SELECT customerName, LAST(someOtherField), LAST(someOtherField2),
LAST(someOtherField3)
FROM qu
GROUP BY customerName




Vanderghast, Access MVP

 

[John Spencer]

SELECT DISTINCT CustomerNames
FROM YourTable

In query design view (query grid)
-- Add (display) only the customer names field
-- Double-click in a blank area in the top half of the grid
-- in the Query properties, set unique Values to Yes

Distinct values combines records where the fields that are displayed are
alike. So if you have five records for John Spencer, you will only see one row
with the value John Spencer.

IF you add the date field, then you will see one row for each unique
combination of John Spencer and the dates associated with John Spencer.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County