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" <david@marleycotenospam.fsnet.co.uk> wrote in message
news:bsh7fo$ah8$1@news5.svr.pol.co.uk...[color=blue]
> Allen
> Many thanks for your reply. I have things working as your notes apart[/color]
from the[color=blue]
> final step of sending the invoice number back from the invoice table to[/color]
invoice[color=blue]
> 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 <AllenBrowne@SeeSig.Invalid> wrote in message
> news:3fea8184$0$1721$5a62ac22@freenews.iinet.net.a u...[color=green]
> > If invoices are generated as a batch after the sales/hire event, they[/color][/color]
need[color=blue][color=green]
> > 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[/color][/color]
invoiced;[color=blue][color=green]
> > 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[/color][/color]
records[color=blue][color=green]
> > in #2.
> >
> > This is very similar to what you are doing with your temp table, except[/color][/color]
they[color=blue][color=green]
> > are permanent records. You may want to give the client an End Date[/color][/color]
(create[color=blue][color=green]
> > invoices up to this sale/hire date), but not a begin date: it must get[/color][/color]
all[color=blue][color=green]
> > 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[/color][/color]
field[color=blue][color=green]
> > to the main SalesHire table, and that field is Required (i.e. can't have[/color][/color]
a[color=blue][color=green]
> > 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[/color][/color]
invoice[color=blue][color=green]
> > is created. It's then dead-easy to identify which sales/hire record have[/color][/color]
not[color=blue][color=green]
> > been invoiced, and group them by ClientID, create the main Invoice[/color][/color]
record,[color=blue][color=green]
> > and update the Nulls with the new InvoiceID value.
> >
> > The Invoice table will have the BatchID as a foreign key. You can[/color][/color]
therefore[color=blue][color=green]
> > identify the invoices that are part of the last batch, and undo the[/color][/color]
batch if[color=blue][color=green]
> > desired. Likewise, it's very easy for the client to reprint any batch at[/color][/color]
any[color=blue][color=green]
> > time.
> >
> > Takes a bit of work, but it's a really robust, flexible, reliable[/color][/color]
system.[color=blue][color=green]
> >
> > --
> > 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" <david@marleycotenospam.fsnet.co.uk> wrote in message
> > news:bscr2p$f3f$1@news5.svr.pol.co.uk...[color=darkred]
> > > I am creating invoices for an app I am busy with.
> > > The transactions for the invoice come from 2 tables which store Sales[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > Facilities Hire.
> > >
> > > The current arrangement is that I create a temp table using append[/color]
> > queries to[color=darkred]
> > > get transactions from the 2 tables between selected dates. then draw[/color][/color][/color]
these[color=blue][color=green]
> > into[color=darkred]
> > > a report grouped by the Sales and Facilities Hire
> > > This all works fine.
> > > However the customer requires invoices (reports) to have consecutive[/color]
> > numbers and[color=darkred]
> > > also I need to record the date when this batch of transactions were[/color]
> > invoiced,[color=darkred]
> > > and subsequently settled.
> > >
> > > Trying to figure out the best way of making this happen.
> > > Any thoughts
> > > TIA
> > > David B[/color][/color][/color]
