Invoice numbering

[David B]

I am creating invoices for an app I am busy with.
The transactions for the invoice come from 2 tables which store Sales and
Facilities Hire.

The current arrangement is that I create a temp table using append queries to
get transactions from the 2 tables between selected dates. then draw these into
a report grouped by the Sales and Facilities Hire
This all works fine.
However the customer requires invoices (reports) to have consecutive numbers and
also I need to record the date when this batch of transactions were invoiced,
and subsequently settled.

Trying to figure out the best way of making this happen.
Any thoughts
TIA
David B

[Allen Browne]

If invoices are generated as a batch after the sales/hire event, they need
to be permanently stored - not just in a temp table.

The code to create the invoices will:
1. Get a batch number;
2. Get all sales/hire detail records that have not been previously invoiced;
3. Create an invoice for each client who has a record in #2.
4. Create detail records under each client's record for the detail records
in #2.

This is very similar to what you are doing with your temp table, except they
are permanent records. You may want to give the client an End Date (create
invoices up to this sale/hire date), but not a begin date: it must get all
uninvoiced records.

In a really simple system, it may be possible to use the SalesHireDetail
table as the InvoiceDetail as well. This table will have a foreign key field
to the main SalesHire table, and that field is Required (i.e. can't have a
detail record that is not part of a sales record). It will also have a
foreign key field to the Invoice table. This field is Null until an invoice
is created. It's then dead-easy to identify which sales/hire record have not
been invoiced, and group them by ClientID, create the main Invoice record,
and update the Nulls with the new InvoiceID value.

The Invoice table will have the BatchID as a foreign key. You can therefore
identify the invoices that are part of the last batch, and undo the batch if
desired. Likewise, it's very easy for the client to reprint any batch at any
time.

Takes a bit of work, but it's a really robust, flexible, reliable system.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bs**********@news5.svr.pol.co.uk...

I am creating invoices for an app I am busy with.
The transactions for the invoice come from 2 tables which store Sales and
Facilities Hire.

The current arrangement is that I create a temp table using append queries to get transactions from the 2 tables between selected dates. then draw these into a report grouped by the Sales and Facilities Hire
This all works fine.
However the customer requires invoices (reports) to have consecutive numbers and also I need to record the date when this batch of transactions were invoiced, and subsequently settled.

Trying to figure out the best way of making this happen.
Any thoughts
TIA
David B

[David B]

Allen
Many thanks for your reply. I have things working as your notes apart from the
final step of sending the invoice number back from the invoice table to invoice
detail table. I am trying an update query, latest sql is as follows.

UPDATE invoicetable INNER JOIN invoicedetail ON invoicetable.custid =
invoicedetail.customerid SET invoicedetail.invoicenum = [invoicenumber];

Get the impression my brain is not at full speed today
Any thoughts , on the sql not my brain

David b
Allen Browne <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

If invoices are generated as a batch after the sales/hire event, they need
to be permanently stored - not just in a temp table.

The code to create the invoices will:
1. Get a batch number;
2. Get all sales/hire detail records that have not been previously invoiced;
3. Create an invoice for each client who has a record in #2.
4. Create detail records under each client's record for the detail records
in #2.

This is very similar to what you are doing with your temp table, except they
are permanent records. You may want to give the client an End Date (create
invoices up to this sale/hire date), but not a begin date: it must get all
uninvoiced records.

In a really simple system, it may be possible to use the SalesHireDetail
table as the InvoiceDetail as well. This table will have a foreign key field
to the main SalesHire table, and that field is Required (i.e. can't have a
detail record that is not part of a sales record). It will also have a
foreign key field to the Invoice table. This field is Null until an invoice
is created. It's then dead-easy to identify which sales/hire record have not
been invoiced, and group them by ClientID, create the main Invoice record,
and update the Nulls with the new InvoiceID value.

The Invoice table will have the BatchID as a foreign key. You can therefore
identify the invoices that are part of the last batch, and undo the batch if
desired. Likewise, it's very easy for the client to reprint any batch at any
time.

Takes a bit of work, but it's a really robust, flexible, reliable system.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bs**********@news5.svr.pol.co.uk...

I am creating invoices for an app I am busy with.
The transactions for the invoice come from 2 tables which store Sales and
Facilities Hire.

The current arrangement is that I create a temp table using append

queries to

get transactions from the 2 tables between selected dates. then draw these

into

a report grouped by the Sales and Facilities Hire
This all works fine.
However the customer requires invoices (reports) to have consecutive

numbers and

also I need to record the date when this batch of transactions were

invoiced,

and subsequently settled.

Trying to figure out the best way of making this happen.
Any thoughts
TIA
David B

[Allen Browne]

Hmm. The 4th step will depend on a bunch of other details about your system
that I don't have.

It looks like your InvoiceDetail table has a CustomerID field? Normally it
would not: that would be in the main InvoiceTable only. So perhaps you are
using this temporarily until the InvoiceNum is assigned? If so, and
assuming you are generating just one invoice for the client for the period,
and you have created the main InvoiceTable record, you would select the
records where the InvoiceNum is blank in the related table, and the records
of the particular batch in the main table:

"WHERE (InvoiceDetail.InvoiceNum Is Null) AND (InvoiceTable.BatchID = " &
lngBatchID & ");"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bs**********@news5.svr.pol.co.uk...

Allen
Many thanks for your reply. I have things working as your notes apart from the final step of sending the invoice number back from the invoice table to invoice detail table. I am trying an update query, latest sql is as follows.

UPDATE invoicetable INNER JOIN invoicedetail ON invoicetable.custid =
invoicedetail.customerid SET invoicedetail.invoicenum = [invoicenumber];

Get the impression my brain is not at full speed today
Any thoughts , on the sql not my brain

David b
Allen Browne <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

If invoices are generated as a batch after the sales/hire event, they need to be permanently stored - not just in a temp table.

The code to create the invoices will:
1. Get a batch number;
2. Get all sales/hire detail records that have not been previously invoiced; 3. Create an invoice for each client who has a record in #2.
4. Create detail records under each client's record for the detail records in #2.

This is very similar to what you are doing with your temp table, except they are permanent records. You may want to give the client an End Date (create invoices up to this sale/hire date), but not a begin date: it must get all uninvoiced records.

In a really simple system, it may be possible to use the SalesHireDetail
table as the InvoiceDetail as well. This table will have a foreign key field to the main SalesHire table, and that field is Required (i.e. can't have a detail record that is not part of a sales record). It will also have a
foreign key field to the Invoice table. This field is Null until an invoice is created. It's then dead-easy to identify which sales/hire record have not been invoiced, and group them by ClientID, create the main Invoice record, and update the Nulls with the new InvoiceID value.

The Invoice table will have the BatchID as a foreign key. You can therefore identify the invoices that are part of the last batch, and undo the batch if desired. Likewise, it's very easy for the client to reprint any batch at any time.

Takes a bit of work, but it's a really robust, flexible, reliable system.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bs**********@news5.svr.pol.co.uk...

I am creating invoices for an app I am busy with.
The transactions for the invoice come from 2 tables which store Sales and Facilities Hire.

The current arrangement is that I create a temp table using append

queries to

get transactions from the 2 tables between selected dates. then draw

these into

a report grouped by the Sales and Facilities Hire
This all works fine.
However the customer requires invoices (reports) to have consecutive

numbers and

also I need to record the date when this batch of transactions were

invoiced,

and subsequently settled.

Trying to figure out the best way of making this happen.
Any thoughts
TIA
David B