Count Problem

[CanWest]

I have a report that is based on Two tables. One is the Client table and another is a Telphone Table.

The tables have a one to many relationship via the ClientID Field. I have built a small report that shows all of the clients and their telephone numbers. I have have the report so that it only displays one row for each client with only the first telephone number which is exactly what I want.

The problem lies in this expression count([ClientID])

It counts the client ID for every phone number. So lets say I had 15 clients in the report and three of them had 2 phone numbers. The count would be 18 and that is not correct. I am at a loss as to what to do

 

[Trevor G]

In a query can you set it up to group based on ID and Telephone number so your report should give you a correct total.

I have just simulated creating a table as follows:

tblClient1
ClientID (Primary Key), ClientName

tblClient2
ClientID, TelephoneNumber

I have related them through the ID fields.

I added several clients and then several numbers, repeating the same number several times.

Then created the query to show Client and TelephoneNumber which gives me a long list, then group by Client and Group TelephoneNumber, which gives me a result showing the Client Name and the individual telephoneNumber once.

SQL Code is

SELECT tblClient1.Client, tblClient2.TelephoneNumber
FROM tblClient1 INNER JOIN tblClient2 ON tblClient1.ClientID = tblClient2.clientID
GROUP BY tblClient1.Client, tblClient2.TelephoneNumber;

I have attached a basic sample, which will give you a queryshowing Client and Telephone Numbers I have included a count of how many times the telephone number is shown.

Hope this helps

 

[CanWest]

I am having a little problem explaining my problem. The sample db you posted still shows the same problem mine does. I ony want the each client to show once regardless of how many telephone numbers they have. The only number I want to show is their first one if they have more than one

 

[Trevor G]

OK in the sample I haven't created a report, so if you created a report based on the sample query you could then use a Group based on Client name, you see the client once and the numbers below in the data section.

That would solve seeing the client name more than once.

I have added a report with the grouping. See attached

 

[DCrake]

In your report you need to set the hide duplicates property to True that way you won't get that.. Alternatively set up a group header in your report and group by client name and just have the telephone numbers in the detail section.

 

[Trevor G]

see attached as David has proposed. New report same name as query (just to show not common practise), open in design view then select the field Client then move down the properties until hide duplicates change from No to Yes