Adding sequential number to report ?

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

Hi,
I'm trying to create a sequential no which is put onto each report at the
top grouping level.
The report is a set of invoices, and the top grouping level is basically the
company name, with a group containing info on the each items invoiced,
price,tax etc below that.
On the top grouping level, I want to put a number which starts at one for
the first invoice, and increments for each time the report creates a new
company name section. When I run the report next time, I want it to start
from the last number used + 1.
So
Company A Invoice No: 000004
xxxxx
xxxxx
xxxxx
new page
Company B Invoice No: 000004
xxxxx
xxxxx
xxxxx
etc
The report is working fine, creating all the invoices correctly but I'm
stuck on how to do this number.

BTW The number doesn't have to be sequential, but must be unique. I don't
need a record of the number for each invoice issued, just to know what the
last number used is, so I can start the new report on the next number.
Any ideas

Tony
 
Tony:

The issue you want to think about related to numbering an invoice is that
you should capture that number to invoice link within your database for
future reference rather than simply displaying it on a report and
incrementing each time the report it opened. (Although that could be done,
but its not very practical be cause any approach would require capturing the
last displayed invoice number and what then happens if you preview the
report repeatedly or re-run it because of an error, you end up with
different invoice numbers).

Normally what this involves is creating an invoice table within your db that
captures the key data from the invoice output (e.g. customer id, total
billed, billing date etc.), by using an append query into the table. In the
table, create an invoice number field that's an auto number field. Run your
append query to this table before outputting the current invoices in a
report.

Then of course add this table to your invoice report query and you should
have an invoice number at hand.
 
TonyB said:
Tony:

The issue you want to think about related to numbering an invoice is that
you should capture that number to invoice link within your database for
future reference rather than simply displaying it on a report and
incrementing each time the report it opened. (Although that could be done,
but its not very practical be cause any approach would require capturing the
last displayed invoice number and what then happens if you preview the
report repeatedly or re-run it because of an error, you end up with
different invoice numbers).

Normally what this involves is creating an invoice table within your db that
captures the key data from the invoice output (e.g. customer id, total
billed, billing date etc.), by using an append query into the table. In the
table, create an invoice number field that's an auto number field. Run your
append query to this table before outputting the current invoices in a
report.

Then of course add this table to your invoice report query and you should
have an invoice number at hand.
Click to expand...
Hi Steve,
I agree with you for a real world project you would do it the way you
described. I'm looking for a simple solution to a specific non real world
application. However it seems hard to do it the way I wanted. So I'll try to
see if I can grasp your solution. If I read you right, I could run an append
query on entry to the report, which will fill the invoice table with the
invoice data I want to store, and it will autoincrement the invoice no field
as a result with each invoice appended. Then the report can just use the
invoice no as part of a normal query which will read the new invoice numbers
as well as the data I'm putting onto the invoice ?
Regards
Tony
 
Tony:

You've got it 99%. The issue is to take your invoice header data, and
append that to the table before you run the report, not as part of the
report's process (say in the on open event).
 
Back
Top