Question Help Please.. Invoice / Ref Numbers

[hullstorage]

I have been trying to get an answer for this for sometime now with no success any help would be much appreciated. I have tried to solve this with Northwind Database but also with no success.

This is what i have:

Query with the following fields

date, customer, collect from town, delivery town, customer ord. no, cost, inoviced yes/no

what i do is each day enter a new set of records for a given customer and at the end of each month recall these records using a query which use the value from the invoiced field that = no

date cust from to ord.no cost invoiced
========================================
01/11/08 DHL, HULL, LEEDS, 12234, £25.00, NO
13/11/08 DHL, HULL, LEEDS, 12234, £25.00, NO
16/11/08 DHL, HULL, LEEDS, 12234, £25.00, NO
19/11/08 DHL, HULL, LEEDS, 12234, £25.00, NO
28/11/08 DHL, HULL, LEEDS, 12234, £25.00, NO

What i am trying to do is give these records 1 invoice number for all of these records from that last invoice number available, which i would probably use dmax() from some sort of invoices table where these will then be stored. I would also then need this to update the field invoiced to = yes so these would not be shown on the following months recall.

Or has somebody an easier soloution or sample database they could kindly supply me.

Many thanks for help

simon

 

[gemma-the-husky]

what i do, is actually process using a record set

'first have a key that contains the invoicing sequence you want
'then either open the table directly on that key (or use a query, sorted on the correct parameters)

then i have a 2 stage sequence
1 - allocate numbers
2 - prepare invoices, based on the invoice number

so to allocate the numbers, in pseudocode

activeinvoicenumber = "whatevermechanism you use to determine startnumber for invoice run"
startinv = activeinvoicenumber
open the recordset

while not rst.eof
   if we are not on the same invoice then
      activeinvoicenumber= activeinvoicenumber+1   
      anything else you need ot do with a new invoice
   end if
   rst.edit
   rst!invno = activeinvoicenumber
   rst!invoicedflag = true 'or set this at a later processing stage
   rst!invoicedate = whateverdate
   rst.update
   rst.movenext
end if

lastinv=activeinvoicenumber

'so now you also have start and last invoice numbers for the invoice run, and you can now use this range to manage the new invoices - produce invoices, audit trails etc

hope this helps