Count number of Invoices from Invoice Detail File

Ann Ryden

New Member
Joined
Sep 12, 2006
Messages
9
I have an Invoice Detail file that contains records by the following:

Invoice Line Item
Customer
Product Class
Product
Lot Number
Invoice Date

I wrote a report that summarizes the sales by Product Class by Month.

Is there a way to calculate/count how many invoices were processed in a month? The Invoice Detail file can list an invoice number more than once depending on how many products and lot numbers are on the Invoice.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I would think you could count the invoice file, rather than the invoice detail file. At least I would assume the invoice file would have the invoice number and invoice date, so my looking at one month of data, you could just count those invoice numbers for that month. To do this, use a Summary Query, group by month, select the month you want, and then count "Count(*)" (on the Total row as Expression)

If you can only use the invoice detail table, then first do a summary sort grouping by invoice number so you only have one record per invoice number, then go to back to the first paragraph here.

HTH,
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Ann,

Another way to go might be to create a report based on Invoice Detail.
While stepping through the Wizard, group by Invoice and Product Class: also by Month if required -- that would be the top grouping.

There is a point during the wizard where you can nominate summary calculations. Click the button, select the fields ot summarise by and whether you want Sum or Count.

The resulting report may look a bit messy but you can clean it up -- and you will have a printed summary by the categories you requested, as well as a grand total

Denis
 

Ann Ryden

New Member
Joined
Sep 12, 2006
Messages
9
I already have the report by Product Class by Month for Return Dollars.

Example:

Nails
Jan -120
Feb -400
Mar -100

Total Nails -620

So, I created a second query from the Invoice Header file with Invoice by Month and did a Count (*) Expression. When I run the query I get the correct invoices processed if 89. Then, in the report, on the Total Nails Line I added a DLookup("Invoices","qryAllInvoices'). However, when I run the report I keep getting 1 instead of 89 invoices processed.

What do I do with the second query. Can I get the DLookup to work. How to I show the 89 Invoices in the report? I suspect the problem is with the count expression but I am not sure.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Ann,
The DLookup function wants the field name you are looking for data in, in the first parameter of the function. What did you name the Count(*) column in the query? If you left it for Access to name, then the column would be "Expr1". I'm assuming you named the query "qryAllInvoices". I would suggest another name, because you are really doing a count of invoices for only a specified month.
HTH,
 

Ann Ryden

New Member
Joined
Sep 12, 2006
Messages
9
The field name is as follows:

Invoices: Count(*)

This is why I wrote the DLookup in the report as follows:

DLookup("Invoices","qryAllInvoices')

Any suggestions? Was I correct putting "Invoices" first?

Thanks, Ann
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top