How to add an incrementing number to a report 2

[piperent]

What is the simplest method of adding an auto-incrementing number to a report. I need an invoice number to print on a report and can't seem to find a means of adding it to a report page, updating it by 1, then adding it to the next page. This has to be a trivial little bit of code, but I've spent 2 days trying to figure it out and I still haven't got a clue.

Give me some suggestions. I don't care how I have to go about starting off the sequencing, I just need some means of plugging a different number on each invoice without user intervention.

Any help will be greatly appreciated.

Thanks
JP

 

[Remou]

Hi
Have you looked at Running Sum?

If this is an Invoice number, surely it should be coming from a table?

 

[Lilliabeth]

=[Page] will number the pages
=1000+[Page] will begin the numbeing with 1001

 

[piperent]

I do have a table that holds what could be the next_avail number, but I could not figure how to update the silly thing for each invoice. Keep in mind that when I pull the invoices, they all come out in a single run (I might get 20 or 30 invoices in one run). But maybe your 'Running Sum' takes a value and increments it.

The =[Page] idea has some possibilities as long as I can change the starting value each time I run it. If that can be =start_value+[Page] where I can enter the start_value each time, it would fit the bill just fine.

I'll take a look at what I might do with both of these and report back.


Thanks to both of you for the input.

J Piper ;

 

[piperent]

Just a brief note to thank both Remou and Lillabeth for the help. I combined both of their approaches, combined them, and that solved the problem.

Since I already have a table containing the next_invoice number, I use it as my start invoice value, then I simply add that value to the page number to create a unique invoice number.

=[next_invoice]+[Page]

Works great. I knew it had to be something simple.

Thanks a lot.

J Piper

 

[lridlen]

Hi guys,

I have also been trying to do something similar to this for a couple of days! The difference is that I don't want to have to choose the start number each time, i want the next_invoice number to update each time so that the next time I run the report it will start from the latest number. Any ideas?

 

[Remou]

Can I draw your attention to a line in my post?
If this is an Invoice number, surely it should be coming from a table?

And this:
How can I update table data in a report?
thread703-1145897
In which piperent encounters some of the problems in taking the above approach.

It is possible to add a number from a lookup table to the page number, but you are quite likely to get invoices numbered differently each time. (I seem to remember that various government departments are quite touchy about invoice numbers.) Then you have the problem of updating the lookup table at the end of the run.

 

[lridlen]

Thanks for your reply Remou. This is a very simple database with two tables - a Dealer table and a commission table. All that is recorded is the commissions each dealer would make in a month. There are currently 4 dealers.

The report needs to show all the commissions sorted by each dealer. So it is a 4 page report - with a break at each dealer. Because I want to print all the dealer commissions reports in one hit, I want one unique number for each page.

I can't figure out how this can come from a table when I don't want the invoice number assigned to each commission transaction (which would make it very easy), only each separate dealer. The dealer table does not change unless a new dealer is added, so the invoice number cannot come from there.

Which is why I came to the conclusion that I needed to generate an auto number that sits on the report in the header area so is updated on each page.....

If there's a beter way, I'd love to know about it.
Thanks

 

[Remou]

I think we need another thread in Microsoft: Access Tables and Relationships Forum: forum700, which would open up the topic for discussion. Also, it is somewhat frowned upon in these fora to continue a thread on a new topic.

What I would suggest is an additional Invoice table:
tblInvoice
InvoiceID PrimaryKey
DealerID ForeignKey
<Other fields>

This would then related to the Commissions table (one to many):
tblCommission
CommissionID PrimaryKey
InvoiceID ForeignKey
<Other fields>

That is just a guess, feel free to shoot it down.

 

[lridlen]

Hi Remou,
sorry about the continued thread. This is the first time I have used this and not sure how to make a new thread and us both still follow it....

anyway, assigning the relationship to the commission table would give me a new invoice number for each commission entry and that's not what I want. I want a new invoice number per dealer - but the dealer's rarely change, so I can't make the relationship between dealer and tblinvoice.

the report is based on a query which includes both tblcommission and tbldealer, but is sorted by dealer with a page break for each new dealer entry. The commissions per dealer are then listed on their respective pages.

This is why I thought I needed an auto generated number on the report.

Louise

 

[Remou]

Hello Louise
If you go to Microsoft: Access Tables and Relationships Forum: forum700
And go to the bottom of the page, you will find a form to fill in. You can paste in:
How to add an incrementing number to a report
thread703-1144860
As a reference.

Otherwise we are not respecting piperent. See you there