+1 in each row of query

[elaspina]

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 :)

[jmprescott]

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

[ChipR]

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.

[elaspina]

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?

[ChipR]

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.

[ADezii]

@elaspina
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.

[ADezii]

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:
   

   Dim MyDB As DAO.Database
   Dim rstInvoice As DAO.Recordset
   Dim lngLastInvoiceID As Long
   Dim lngCounter As Long
    
   lngCounter = 0      'Initialize
    
   Set MyDB = CurrentDb
   Set rstInvoice = MyDB.OpenRecordset("tblInvoicesNew", dbOpenDynaset)
    
   'Assuming your Original Table is named tblInvoices and contains a
   'Field named [InvoiceID], and contains Records (simplified Version)
   lngLastInvoiceID = DMax("[InvoiceID]", "tblInvoices")
    
   With rstInvoice
     Do While Not .EOF
       lngCounter = lngCounter + 1
         .Edit
           ![InvoiceID] = lngLastInvoiceID + lngCounter
         .Update
           .MoveNext
     Loop
   End With
    
   rstInvoice.Close
   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.