It is possible to create the child records with no parent, i.e. the foreign
key InvoiceDetails.InvoiceID is Null, and you then write the code to collect
all the invoiced items for each client at the end of the period (month?) and
assign the foreign key value now they are invoiced.
If the fields of the existing record (work order?) are quite different form
the fields of the InvoiceDetail table, another approach would be to use a
foreign key between the two tables. If there is no record in InvoiceDetail
that references the work order, then your CreateInvoice() code will create
the record for both Invoice and InvoiceDetail when it is run.
Recurring billing is an interesting beast, as it sometimes has to cope with
vagaries where a client is given a month for free or is lapsed for a time,
or ... If you are happy with the idea that the payment is always due 1
period after their *last* regular payment, then the approach you suggest
will be fine. You can define fields for a Period (whole number) and a
PeriodType. PeriodType accepts valid values for DateAdd, such as "d", "m",
"yyyy". The next time the payment is due is then:
DateAdd([Period], [PeriodType], [DateOfLastRegularPayment])
where the date of last regular payment is typically retrieved with a
subquery. The value of this two-field approach is that it's very flexible,
e.g. allowing monthly payments regardless of the number of days in the
month.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
"William Roberts" <wi*********@hotmail.com> wrote in message
news:d6**************************@posting.google.c om...
I am desiging an invoicing program that will do recurring billing as
well as charging for any additional service at a given time. All
charges will be service oriented. I am particularly concerned with
being able to seperate uninvoiced charges and invoiced charges and
would appreciate advice on do's or dont's, or any Ideas for a better
way to do it..
I have the usual tables [Customers],[Invoices],
[InvoiceDetails],[Services]...
I plan on to use one table (InvoiceDetails) for both invoiced and
noninvoiced charges and using a field to mark a record when it is
invoiced, this field would then be used in my queries or I could just
assume that if a record has an invoice number and an invoice date that
it has been invoiced. What would be the best way to do this?
Also, for recurring billing I plan to generate a billing schedule in a
seperate table with a date field at a user specified frequency e.g.
monthtly, semi-anually ect.ect. using a customerID as a FK, Then I can
use code however I wish to access tohose billng dates. Is this a good
way to do this?
William Roberts
