If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Selecting customers with more than 1 contract
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 than 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 table. 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 |
#2
|
|||
|
|||
Selecting customers with more than 1 contract
Hi KMD
Try setting up a query with your table and select the Customer_ID twice - one with COUNT and 1 in the condition and the other with GROUP BY. If you like you don't need to display the column with COUNT. Result is that you'll receive in this query the Customer_ID (once) of all who have more than 1 contract. Table1 = Your Table with the data a = Field with Customer_ID SELECT Count(Table1.a) AS CountOfa, Table1.a FROM Table1 GROUP BY Table1.a HAVING (((Count(Table1.a))1)); Bernd |
Thread Tools | |
Display Modes | |
|
|