Count Invoices (1 Viewer)

P

pekajo

Registered User.

Local time

Today, 19:29

Joined

Jul 25, 2011

Messages

133

• Jul 17, 2019

• #1

Hi,
Can you help me. I need to count the number of invoices to a client for a report. eg
fldclient fldInvoice#
1 101
1 110
1 123
2 113
2 115


count client 1 has 3 invoices
client 2 has 2 invoices
etc

The dcount does not appear to be the answer.

Thanks for any help
Peter

 

June7

AWF VIP

Local time

Today, 01:29

Joined

Mar 9, 2014

Messages

5,470

• Jul 17, 2019

• #2

If you want report to list detail records as well as summary calcs, set report to group on fldClient and use Count() aggregate function in group header or footer section.

 

Minty

AWF VIP

Local time

Today, 10:29

Joined

Jul 26, 2013

Messages

10,371

• Jul 17, 2019

• #3

A simple grouped query will do this;

Code:Copy to clipboard

SELECT fldClient, Count(fldInvoice#) as NoOfInvoices
From YourTable
Group By fldClient

As a word of advice remove any special characters from your field names like # % etc.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 17:29

Joined

May 7, 2009

Messages

19,230

• Jul 17, 2019

• #4

or you can use Group (group by fldClient) on your report and do the count.