Count number of Invoices from Invoice Detail File

[Ann Ryden]

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.

 

[VicRauch]

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]

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]

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]

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]

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