A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Selecting customers with more than 1 contract



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2004, 12:41 AM
external usenet poster
 
Posts: n/a
Default 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  
Old April 27th, 2004, 12:41 PM
BerHav
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.