Query to find multiple records for Customer_ID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of customers, which includes contracts
they hold with our firm.
Most customers hold one only contract, however some hold
two and three and a few as high as 5.

This is a flat database and therefore one only table
holds all the data so each contract creates record in the
table with
Duplicate information related to the customer. (A
customer lookup table or similar is planned for the
future.)

I would like to generate a report based on a query that
selects only those customers that have more that one
contract. therefore more than one record in the table.

All I can think of at this time is that the 'Count
Function' must be used in some way the check if
the 'Customers_ID' exists more than once in the data. If
this is true, then the query must select all records with
that 'Customer_ID' so these and only these records will
be included in the report.

All Comments and ideas welcomed.. Thank you.KMD
 
You would need to use two queries or a query with a subquery. I'd try the
following query with a subquery. Obviously, you need to substitute your Table
and Field names in the SQL statement.

SELECT *
FROM FlatTable
WHERE Customer_ID In
(SELECT Customer_id
FROM FlatTable
GROUP BY Customer_ID
HAVING Count(Customer_ID) > 1)
 
Back
Top