+1 in each row of query

Hello

I am trying to write a query which will ultimately feed a report that will print out invoices for each of my clients. I currently have a query that pulls each client's name and amount due. I now need to add an invoice number. I want these to be sequential. Is there a way to get each row in the query to increase by 1? I know this is possible within the Report by using the Running Sum property, but I really need this is the query.

Would appreciate any insight!

Thanks :)

re: +1 in each row of query

Can you just add an autonumber field to the table? That would do it for you. Or did I misunderstand?

re: +1 in each row of query

If this is actually an invoice number, I think you would want it to be permanent, and therefore part of a table. You don't want to print an invoice for your customer, then next time you run the query, get a difference number assigned to the record.

re: +1 in each row of query

So question becomes: How do I get this autonumber to adjust each month if I have a static table? Currently I manually go through and change the invoice numbers by taking the last invoice number used the prior month and adding 1 to it and then type this sequentially all the way down my client list. Is there an update query I can run that can do this?

re: +1 in each row of query

I guess I'm not clear on exactly what your requirements are. What good is an invoice number if you are going to just change it?
If you are going to have an invoice number, it would normally refer to an object which is an invoice. Objects are represented by records which are stored in tables. This implies that you have a table of invoices.

re: +1 in each row of query

I do have a work-around solution that will automate the process of sequentially numbering the Invoices from the Last Entered, but it is a stretch. I'll just wait and see what, if any, solutions are provided to you.

re: +1 in each row of query

Assuming your Original Table is named tblInvoices and contains a Field uniquely identifying the Invoice Number named [InvoiceID]:

1. Change your SELECT QUERY to a MAKE TABLE QUERY, but do NOT include the [InvoiceID] Field. Name the newly created Table tblInvoicesNew.
2. Add a Field to tblInvoicesNew named [InvoiceID]. This Field must be a LONG INTEGER and NOT AutoNumber, or be a Primary Key.
3. Execute the following code:
   
   Expand|Select|Wrap|Line Numbers

   1. Dim MyDB As DAO.Database
   2. Dim rstInvoice As DAO.Recordset
   3. Dim lngLastInvoiceID As Long
   4. Dim lngCounter As Long
   5.  
   6. lngCounter = 0      'Initialize
   7.  
   8. Set MyDB = CurrentDb
   9. Set rstInvoice = MyDB.OpenRecordset("tblInvoicesNew", dbOpenDynaset)
   10.  
   11. 'Assuming your Original Table is named tblInvoices and contains a
   12. 'Field named [InvoiceID], and contains Records (simplified Version)
   13. lngLastInvoiceID = DMax("[InvoiceID]", "tblInvoices")
   14.  
   15. With rstInvoice
   16.   Do While Not .EOF
   17.     lngCounter = lngCounter + 1
   18.       .Edit
   19.         ![InvoiceID] = lngLastInvoiceID + lngCounter
   20.       .Update
   21.         .MoveNext
   22.   Loop
   23. End With
   24.  
   25. rstInvoice.Close
   26. Set rstInvoice = Nothing
4. tblInvoicesNew will now consist of Sequentially Number Invoice IDs starting from 1+ the Last [InvoiceID] in tblInvoices.
5. Make tblInvoicesNew the Record Source for your Report, and you should be in business.