Keeping a record of repeating invoices

[tom]

Hi,

I have built an Access DB for a child care business. Family table
stores the Family info. Room the different room names, capacity etc.
Price stores the various price codes and amounts. The Child table
records the childs DOB, start date, end date etc. etc. and of course
to which family he/she belongs, which room and which price category.
There is then a query and report that produces the invoices for each
child for a given month (month inputted). Of course invoices are
produced for only the children currently attending (start date < today

end date.

Now the problem I have is that I would like to record these invoices.
So that if needed I could reprint one. Also I would like to see all
invoices sent to a particular family, all invoices for a particular
month etc.I would also like to be able to indicate that a family has
paid and the payment date. However as it stands this info is not in a
table that I can query, it is the output of a query/report. I'm sure I
am doing something fundamentally wrong but cannot think what. As you
may have guessed I am very green to DB design.

Any help would be greatly appreciated

Thanks - Tom

[Larry Linson]

Maybe all you need is a table where you record identifying information on
the invoices created, then the ability to create another one that can
quickly be determined to be "DUPLICATE" or "REINVOICE"?

"tom" <to***@ses-astra.com> wrote in message
news:ae*************************@posting.google.co m...

Hi,

I have built an Access DB for a child care business. Family table
stores the Family info. Room the different room names, capacity etc.
Price stores the various price codes and amounts. The Child table
records the childs DOB, start date, end date etc. etc. and of course
to which family he/she belongs, which room and which price category.
There is then a query and report that produces the invoices for each
child for a given month (month inputted). Of course invoices are
produced for only the children currently attending (start date < today

end date.

Now the problem I have is that I would like to record these invoices.
So that if needed I could reprint one. Also I would like to see all
invoices sent to a particular family, all invoices for a particular
month etc.I would also like to be able to indicate that a family has
paid and the payment date. However as it stands this info is not in a
table that I can query, it is the output of a query/report. I'm sure I
am doing something fundamentally wrong but cannot think what. As you
may have guessed I am very green to DB design.

Any help would be greatly appreciated

Thanks - Tom

[Greg Fierro]

Dear Tom:

I can understand your frustration. First off, since you are just
starting, have you taken some classes or read some good books on
database design and/or Access development? I think you need to do this
first before you get more frustrated and just throw everything away. Or
worse, your system doesn't properly records an invoice and you loose
money! But since you asked the question, here are some thoughts.

If you want to print "historical" or "closed" invoices (closed = invoice
has been payed), you need a "Receivables Register". This would be a form
that lists all the invoices that you have not recieved payment for. You
would have a field to record the payment received agains the open
invoice. Very similar to the check register you have for your checking
account. Once invoices are closed, you now have the information for
reports that tell you who has payed and when they payed.

If you need to print "closed" invoices, it sounds like you already have
the data to do that. Remember, you never store the output of a report
because you can always recreate it from your data. All you need is the
mechanism to locate the old invoice and execute your report against this
record source.

You didn't mention if you were using VBA or macros to develop your
application. I think VBA is the way to go, but it is an area where the
developer will need to have some knowledge of programming concepts.

I just completed a large application that creates invoices, prints
invoices and shipping slips, you can brows all invoices and reprint
them. You can locate invoices by customer or invoice number, etc. Also,
there is a receivables register to record payments.

I could let you see the application, but I think it would just add more
to the confusion. I think what you need to do first is make sure that
your table design is sound. This is your foundation. Then, you can
build on this foundation gradually. Or, if this is too much, you could
just buy an application and forget about this nonsense and concentrate
on your primary business!

Good Luck!
Greg Fierro
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!