By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

help building a db for monthly billing?

P: n/a
I'm trying to build a database that will handle the monthly billing needs of
a small company. I'm charting everything out and here's what I see:

table for customers
sub table to track payments received.

No biggie, right? Well, here's my problem. I don't know how to tell access
to modify everyone's account balance each month. And I can't just always
assume that their monthly bill is $16 just because their balance is $16. If
I do that then when people pay a few months in advance or if a check bounced
there's no way to track it.

I'm not asking for the source code to build this (yet). I'm just trying to
figure out how to get started. Is this something I could build into a query
or a macro, or maybe a module?

I've searched Google and Google Groups and I've still not found anything
that can spell this out for me. If anyone has any links to articles on this,
it would be a HUGE help.

Oh, and if I'm going at this all wrong, please let me know.

Thanks,
-jeremy
Nov 12 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
To do what you want, you are obviously going to have to keep a record of
payments, as well -- splitting out just a "billing database" is trying to be
"too granular". And, unless your business requirements are trivially simple,
perhaps even if they are, you are likely to be better off searching and
finding yourself a package, Access or other, for this function.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
I'm trying to build a database that will handle the monthly billing needs of a small company. I'm charting everything out and here's what I see:

table for customers
sub table to track payments received.

No biggie, right? Well, here's my problem. I don't know how to tell access
to modify everyone's account balance each month. And I can't just always
assume that their monthly bill is $16 just because their balance is $16. If I do that then when people pay a few months in advance or if a check bounced there's no way to track it.

I'm not asking for the source code to build this (yet). I'm just trying to
figure out how to get started. Is this something I could build into a query or a macro, or maybe a module?

I've searched Google and Google Groups and I've still not found anything
that can spell this out for me. If anyone has any links to articles on this, it would be a HUGE help.

Oh, and if I'm going at this all wrong, please let me know.

Thanks,
-jeremy

Nov 12 '05 #2

P: n/a
Well, I'm not sure that my requirements are 'trivially' simple, but the end
program must appear to be. This client has already tried Quickbooks and some
program called Billing Clerk and both have features he's not wanting
(especially Quickbooks) and missing some that he does.

Tracking the payments doesn't appear to be much of a problem. Making Access
'remember' to bill everyone each month does. Is there any way to write a
script (module, macro, etc.) that will 'bill' everyone's account on, say,
the 20th of each month? Or, instead of using a set date, tie it to a button
on the switchboard?

-jeremy

"Larry Linson" <bo*****@localhost.net> wrote in message
news:vA*******************@nwrddc03.gnilink.net...
To do what you want, you are obviously going to have to keep a record of
payments, as well -- splitting out just a "billing database" is trying to be "too granular". And, unless your business requirements are trivially simple, perhaps even if they are, you are likely to be better off searching and
finding yourself a package, Access or other, for this function.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
I'm trying to build a database that will handle the monthly billing needs
of
a small company. I'm charting everything out and here's what I see:

table for customers
sub table to track payments received.

No biggie, right? Well, here's my problem. I don't know how to tell

access to modify everyone's account balance each month. And I can't just always
assume that their monthly bill is $16 just because their balance is $16.

If
I do that then when people pay a few months in advance or if a check

bounced
there's no way to track it.

I'm not asking for the source code to build this (yet). I'm just trying to figure out how to get started. Is this something I could build into a

query
or a macro, or maybe a module?

I've searched Google and Google Groups and I've still not found anything
that can spell this out for me. If anyone has any links to articles on

this,
it would be a HUGE help.

Oh, and if I'm going at this all wrong, please let me know.

Thanks,
-jeremy


Nov 12 '05 #3

P: n/a
You would likely "bill" everyone by running a report that prints the invoice
information, and that would be based on a query. For it to "automatically"
do so on a given date, the database would have to be open, and you'd have to
be testing the date in a Timer event. Or, you could use a Windows scheduler
to open it with a specific macro that would run the billing report, on that
date. But it would be quite simple to put a command button on the
switchboard to open the billing report, or a form into which the user could
insert criteria, if desired.

On the other hand, perhaps you want to bill by e-mail -- you can interact
with Outlook, or other e-mail programs or libraries to send e-mails. In that
case, your button could open a Form that would run the code to create the
e-mails, or could itself run the code if no user interaction is required.

I know that's not very specific, but that's about as specific as I can be
from what you've described.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Well, I'm not sure that my requirements are 'trivially' simple, but the end program must appear to be. This client has already tried Quickbooks and some program called Billing Clerk and both have features he's not wanting
(especially Quickbooks) and missing some that he does.

Tracking the payments doesn't appear to be much of a problem. Making Access 'remember' to bill everyone each month does. Is there any way to write a
script (module, macro, etc.) that will 'bill' everyone's account on, say,
the 20th of each month? Or, instead of using a set date, tie it to a button on the switchboard?

-jeremy

"Larry Linson" <bo*****@localhost.net> wrote in message
news:vA*******************@nwrddc03.gnilink.net...
To do what you want, you are obviously going to have to keep a record of
payments, as well -- splitting out just a "billing database" is trying to
be
"too granular". And, unless your business requirements are trivially simple,
perhaps even if they are, you are likely to be better off searching and
finding yourself a package, Access or other, for this function.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
I'm trying to build a database that will handle the monthly billing needs
of
a small company. I'm charting everything out and here's what I see:

table for customers
sub table to track payments received.

No biggie, right? Well, here's my problem. I don't know how to tell

access to modify everyone's account balance each month. And I can't just always assume that their monthly bill is $16 just because their balance is $16. If
I do that then when people pay a few months in advance or if a check

bounced
there's no way to track it.

I'm not asking for the source code to build this (yet). I'm just
trying to figure out how to get started. Is this something I could build into a

query
or a macro, or maybe a module?

I've searched Google and Google Groups and I've still not found

anything that can spell this out for me. If anyone has any links to articles on

this,
it would be a HUGE help.

Oh, and if I'm going at this all wrong, please let me know.

Thanks,
-jeremy



Nov 12 '05 #4

P: n/a
Okay, here's a idea off the top of my head.

What if I setup something (still not sure if macro or module) to run on
startup to check the current date and compares it to a date stored in a
table. If the difference is more than X number of days it will cycle through
the client table and add the monthly fee (pulled from another field) to the
balance field. Then it would replace the date in the table with the current
date.

Is that feasible?

-jeremy
"Larry Linson" <bo*****@localhost.net> wrote in message
news:DM*******************@nwrddc01.gnilink.net...
You would likely "bill" everyone by running a report that prints the invoice information, and that would be based on a query. For it to "automatically"
do so on a given date, the database would have to be open, and you'd have to be testing the date in a Timer event. Or, you could use a Windows scheduler to open it with a specific macro that would run the billing report, on that date. But it would be quite simple to put a command button on the
switchboard to open the billing report, or a form into which the user could insert criteria, if desired.

On the other hand, perhaps you want to bill by e-mail -- you can interact
with Outlook, or other e-mail programs or libraries to send e-mails. In that case, your button could open a Form that would run the code to create the
e-mails, or could itself run the code if no user interaction is required.

I know that's not very specific, but that's about as specific as I can be
from what you've described.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Well, I'm not sure that my requirements are 'trivially' simple, but the

end
program must appear to be. This client has already tried Quickbooks and

some
program called Billing Clerk and both have features he's not wanting
(especially Quickbooks) and missing some that he does.

Tracking the payments doesn't appear to be much of a problem. Making

Access
'remember' to bill everyone each month does. Is there any way to write a
script (module, macro, etc.) that will 'bill' everyone's account on, say,
the 20th of each month? Or, instead of using a set date, tie it to a

button
on the switchboard?

-jeremy

"Larry Linson" <bo*****@localhost.net> wrote in message
news:vA*******************@nwrddc03.gnilink.net...
To do what you want, you are obviously going to have to keep a record of payments, as well -- splitting out just a "billing database" is trying

to
be
"too granular". And, unless your business requirements are trivially

simple,
perhaps even if they are, you are likely to be better off searching and finding yourself a package, Access or other, for this function.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
> I'm trying to build a database that will handle the monthly billing

needs
of
> a small company. I'm charting everything out and here's what I see:
>
> table for customers
> sub table to track payments received.
>
> No biggie, right? Well, here's my problem. I don't know how to tell

access
> to modify everyone's account balance each month. And I can't just

always > assume that their monthly bill is $16 just because their balance is $16. If
> I do that then when people pay a few months in advance or if a check
bounced
> there's no way to track it.
>
> I'm not asking for the source code to build this (yet). I'm just trying
to
> figure out how to get started. Is this something I could build into a query
> or a macro, or maybe a module?
>
> I've searched Google and Google Groups and I've still not found

anything > that can spell this out for me. If anyone has any links to articles on this,
> it would be a HUGE help.
>
> Oh, and if I'm going at this all wrong, please let me know.
>
> Thanks,
> -jeremy
>
>



Nov 12 '05 #5

P: n/a
TC
Jeremy

You must start by designing the table structure to hold your data. Two
tables is waaaaaaaaay below what you will probably need. For example, a
simple Invoice "record" would usually need at least 4 tables: invoice
header, invoice line, product table, customer table & maybe others.
Programming issues (like macros versus modules) are irrelevant until you
decide the table designs.

I suggest yopu read the following article before you proceed:

http://support.microsoft.com/support...es/Q100139.ASP
HTH,
TC

Jeremy Weiss <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Okay, here's a idea off the top of my head.

What if I setup something (still not sure if macro or module) to run on
startup to check the current date and compares it to a date stored in a
table. If the difference is more than X number of days it will cycle through the client table and add the monthly fee (pulled from another field) to the balance field. Then it would replace the date in the table with the current date.

Is that feasible?

-jeremy
"Larry Linson" <bo*****@localhost.net> wrote in message
news:DM*******************@nwrddc01.gnilink.net...
You would likely "bill" everyone by running a report that prints the invoice
information, and that would be based on a query. For it to "automatically"
do so on a given date, the database would have to be open, and you'd have to
be testing the date in a Timer event. Or, you could use a Windows scheduler
to open it with a specific macro that would run the billing report, on

that
date. But it would be quite simple to put a command button on the
switchboard to open the billing report, or a form into which the user

could
insert criteria, if desired.

On the other hand, perhaps you want to bill by e-mail -- you can

interact with Outlook, or other e-mail programs or libraries to send e-mails. In

that
case, your button could open a Form that would run the code to create the e-mails, or could itself run the code if no user interaction is required.
I know that's not very specific, but that's about as specific as I can be from what you've described.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Well, I'm not sure that my requirements are 'trivially' simple, but the
end
program must appear to be. This client has already tried Quickbooks
and some
program called Billing Clerk and both have features he's not wanting
(especially Quickbooks) and missing some that he does.

Tracking the payments doesn't appear to be much of a problem. Making

Access
'remember' to bill everyone each month does. Is there any way to write
a script (module, macro, etc.) that will 'bill' everyone's account on,

say, the 20th of each month? Or, instead of using a set date, tie it to a

button
on the switchboard?

-jeremy

"Larry Linson" <bo*****@localhost.net> wrote in message
news:vA*******************@nwrddc03.gnilink.net...
> To do what you want, you are obviously going to have to keep a record of
> payments, as well -- splitting out just a "billing database" is
trying to
be
> "too granular". And, unless your business requirements are trivially
simple,
> perhaps even if they are, you are likely to be better off searching and > finding yourself a package, Access or other, for this function.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Jeremy Weiss" <jw*****@hotmail.com> wrote in message
> news:vn************@corp.supernews.com...
> > I'm trying to build a database that will handle the monthly
billing needs
> of
> > a small company. I'm charting everything out and here's what I see: > >
> > table for customers
> > sub table to track payments received.
> >
> > No biggie, right? Well, here's my problem. I don't know how to tell access
> > to modify everyone's account balance each month. And I can't just always
> > assume that their monthly bill is $16 just because their balance is $16.
> If
> > I do that then when people pay a few months in advance or if a

check > bounced
> > there's no way to track it.
> >
> > I'm not asking for the source code to build this (yet). I'm just

trying
to
> > figure out how to get started. Is this something I could build

into a > query
> > or a macro, or maybe a module?
> >
> > I've searched Google and Google Groups and I've still not found anything
> > that can spell this out for me. If anyone has any links to
articles on > this,
> > it would be a HUGE help.
> >
> > Oh, and if I'm going at this all wrong, please let me know.
> >
> > Thanks,
> > -jeremy
> >
> >
>
>



Nov 12 '05 #6

P: n/a
TC,

Thanks for the article. However I'm still not sure I agree/understand that I
need some of the tables you mentioned. I do have a table for client_type
(residential, commercial, etc.) but there are no 'products' per say.

You lost me when you said a table for invoice header. What is that for? I've
always built the invoice as a report, why use a table? Same with invoice
line? What is that?

The tables I currently have are: clients, payments, client_type, company
info, and switchboard items.

-jeremy
"TC" <a@b.c.d> wrote in message news:1064726145.887688@teuthos...
Jeremy

You must start by designing the table structure to hold your data. Two
tables is waaaaaaaaay below what you will probably need. For example, a
simple Invoice "record" would usually need at least 4 tables: invoice
header, invoice line, product table, customer table & maybe others.
Programming issues (like macros versus modules) are irrelevant until you
decide the table designs.

I suggest yopu read the following article before you proceed:

http://support.microsoft.com/support...es/Q100139.ASP
HTH,
TC

Jeremy Weiss <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Okay, here's a idea off the top of my head.

What if I setup something (still not sure if macro or module) to run on
startup to check the current date and compares it to a date stored in a
table. If the difference is more than X number of days it will cycle through
the client table and add the monthly fee (pulled from another field) to

the
balance field. Then it would replace the date in the table with the

current
date.

Is that feasible?

-jeremy
"Larry Linson" <bo*****@localhost.net> wrote in message
news:DM*******************@nwrddc01.gnilink.net...
You would likely "bill" everyone by running a report that prints the

invoice
information, and that would be based on a query. For it to "automatically" do so on a given date, the database would have to be open, and you'd have
to
be testing the date in a Timer event. Or, you could use a Windows

scheduler
to open it with a specific macro that would run the billing report, on

that
date. But it would be quite simple to put a command button on the
switchboard to open the billing report, or a form into which the user

could
insert criteria, if desired.

On the other hand, perhaps you want to bill by e-mail -- you can interact with Outlook, or other e-mail programs or libraries to send e-mails. In that
case, your button could open a Form that would run the code to create the e-mails, or could itself run the code if no user interaction is required.
I know that's not very specific, but that's about as specific as I can be from what you've described.

Larry Linson
Microsoft Access MVP
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
> Well, I'm not sure that my requirements are 'trivially' simple, but the end
> program must appear to be. This client has already tried Quickbooks and some
> program called Billing Clerk and both have features he's not wanting
> (especially Quickbooks) and missing some that he does.
>
> Tracking the payments doesn't appear to be much of a problem. Making
Access
> 'remember' to bill everyone each month does. Is there any way to
write a > script (module, macro, etc.) that will 'bill' everyone's account on,

say,
> the 20th of each month? Or, instead of using a set date, tie it to a
button
> on the switchboard?
>
> -jeremy
>
> "Larry Linson" <bo*****@localhost.net> wrote in message
> news:vA*******************@nwrddc03.gnilink.net...
> > To do what you want, you are obviously going to have to keep a record
of
> > payments, as well -- splitting out just a "billing database" is trying to
> be
> > "too granular". And, unless your business requirements are
trivially > simple,
> > perhaps even if they are, you are likely to be better off searching and
> > finding yourself a package, Access or other, for this function.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> >
> > "Jeremy Weiss" <jw*****@hotmail.com> wrote in message
> > news:vn************@corp.supernews.com...
> > > I'm trying to build a database that will handle the monthly

billing > needs
> > of
> > > a small company. I'm charting everything out and here's what I see: > > >
> > > table for customers
> > > sub table to track payments received.
> > >
> > > No biggie, right? Well, here's my problem. I don't know how to tell > access
> > > to modify everyone's account balance each month. And I can't
just always
> > > assume that their monthly bill is $16 just because their balance

is $16.
> > If
> > > I do that then when people pay a few months in advance or if a check > > bounced
> > > there's no way to track it.
> > >
> > > I'm not asking for the source code to build this (yet). I'm just
trying
> to
> > > figure out how to get started. Is this something I could build

into
a
> > query
> > > or a macro, or maybe a module?
> > >
> > > I've searched Google and Google Groups and I've still not found
anything
> > > that can spell this out for me. If anyone has any links to

articles
on
> > this,
> > > it would be a HUGE help.
> > >
> > > Oh, and if I'm going at this all wrong, please let me know.
> > >
> > > Thanks,
> > > -jeremy
> > >
> > >
> >
> >
>
>



Nov 12 '05 #7

P: n/a
"Jeremy Weiss" <jw*****@hotmail.com> wrote in
news:vn************@corp.supernews.com:
TC,

Thanks for the article. However I'm still not sure I
agree/understand that I need some of the tables you mentioned.
I do have a table for client_type (residential, commercial,
etc.) but there are no 'products' per say.
Sure there are- say you are billing for ISP access
Your products would be.
ISP access for one business computer
ISP access for a residence.
If instead you are billing a daycare service. your products would
be:
Daycare service for Jane Doe for the period of October 2003.
Daycare service for Jane Doe for the period of Septamber 2003.
Daycare service for Agnes Doe for the period of October 2003.
Daycare service for Jane Doe for the period of Septamber 2003.
Daycare service for Bob Quintal,jr. for the period of October 2003.

You lost me when you said a table for invoice header. What is
that for? I've always built the invoice as a report, why use a
table? Same with invoice line? What is that?

Your billing the correct amount to each family depends on recording
the product (daycare service for 1 child) billed to each customer
(parent) for one or more children (you may also have a volume
discount applied for 2, 3, 4 children from a single parent). If you
map out what you are trying to do correctly, you get the structure
TC referred to.

So invoice header holds the parent info, and date of invoicing.
The invoice line holds the details for each child.

You build your report on these two tables.
The tables I currently have are: clients, payments,
client_type, company info, and switchboard items.

-jeremy
"TC" <a@b.c.d> wrote in message
news:1064726145.887688@teuthos...
Jeremy

You must start by designing the table structure to hold your
data. Two tables is waaaaaaaaay below what you will probably
need. For example, a simple Invoice "record" would usually
need at least 4 tables: invoice header, invoice line, product
table, customer table & maybe others. Programming issues
(like macros versus modules) are irrelevant until you decide
the table designs.



Nov 12 '05 #8

P: n/a
jw*****@hotmail.com (Jeremy Weiss) wrote in
<vn************@corp.supernews.com>:
What if I setup something (still not sure if macro or module) to
run on startup to check the current date and compares it to a date
stored in a table. If the difference is more than X number of days
it will cycle through the client table and add the monthly fee
(pulled from another field) to the balance field. Then it would
replace the date in the table with the current date.

Is that feasible?


Absolutely, but I'd say that you'd be much better off having not
just a payments table, but also a table for the billings
themselves. Why? Because if all you do is add to the balance, you
completely lose any history of when the changes were made and why
the charges were incurred. You should have a table of transactions
that reflect what the customer was billed and a table of
transactions reflecting what the customer has paid. Optionally, you
may want a many-to-many join table between the two so that you can
tell who paid what when and what they were paying when they did so.
That will also allow you to know which invoices are paid off and
closed, whereas simply tracking a balance will never be enough
information to know what actually happened.

I've built many billing systems. They are not exactly trivial, but
they also aren't all that hard. My structure is usually:

tblCustomer customer data
tblInvoice the billing header
tblInvoiceItems the items that make up the billing
tblPayment the payment header (check number, amount, date)
tblPaymentItems the table that joins the payment header to the
invoices that the payment header applies to

You may think you don't need tblInvoiceItems, but if you want to
anything other than just bill for one single thing, you'll want it.

As to generating invoices, it really needs to be a manual process
for a number of reasons. First, you don't want it to happen
automatically, since you want to make sure that all payments
received are applied *before* the invoices are generated. If you
don't do that, you'll have customers being billed for stuff they've
already paid. Second, it's just not a good idea to have anything
like this happen automatically. In my apps, the billings are
generated, then the person doing the billing reviews it and makes
adjustments to the billings before printing the invoices. Once
printed, the invoices (theoretically) cannot be changed/deleted.
Any adjustments need to be done with additional transactions.

Now, this kind of system is not that necessary for a cash business.
I'm currently designing such a system for a client that does not
track receivables as an asset, they simply cash the checks when
they arrive and book the income at that time (as with a standard
cash sale). They do generate monthly invoices, but their accounting
does not require that those be booked before payment is received.
The invoices are really used as nothing more than a notice to the
customer to pay their bill.

To see some examples from one of my older billing applications
(still in use by multiple customers), see:

http://www.bway.net/~dfassoc/Park/

Check out the links for "Create Monthly Billings" and "Applying
Payments." You may or may not need something that elaborate.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #9

P: n/a
TC
Further to what the others said:

The invoice-header table is for details that are common to the invoice as a
whole; for example, invoice date; customer ID.

The invoice-line table is for details that are specific to one line on the
invoice; for example, product code (if you are invoicing products for
example); quantity; discount rate (if you can discount different line-items
by different amounts);

The customers table is for details that are specific to the customer; for
example, contact details; date of birth, whatever.

You say that you build the invoice "as a report" - but the data on that
report must be stored in tables somewhere! The data that is common to the
invoice as a whole, should be stored in an invoice-header table. The data
that is specific to each invoice line, should be stored in an invoice-line
table, & so on. There is no way you could have a properly normalized invoice
structure with less than 3 or 4 tables (IMO).

HTH,
TC

Jeremy Weiss <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
TC,

Thanks for the article. However I'm still not sure I agree/understand that I need some of the tables you mentioned. I do have a table for client_type
(residential, commercial, etc.) but there are no 'products' per say.

You lost me when you said a table for invoice header. What is that for? I've always built the invoice as a report, why use a table? Same with invoice
line? What is that?

The tables I currently have are: clients, payments, client_type, company
info, and switchboard items.

-jeremy
"TC" <a@b.c.d> wrote in message news:1064726145.887688@teuthos...
Jeremy

You must start by designing the table structure to hold your data. Two
tables is waaaaaaaaay below what you will probably need. For example, a
simple Invoice "record" would usually need at least 4 tables: invoice
header, invoice line, product table, customer table & maybe others.
Programming issues (like macros versus modules) are irrelevant until you
decide the table designs.

I suggest yopu read the following article before you proceed:

http://support.microsoft.com/support...es/Q100139.ASP
HTH,
TC

Jeremy Weiss <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Okay, here's a idea off the top of my head.

What if I setup something (still not sure if macro or module) to run on startup to check the current date and compares it to a date stored in a table. If the difference is more than X number of days it will cycle

through
the client table and add the monthly fee (pulled from another field) to
the
balance field. Then it would replace the date in the table with the

current
date.

Is that feasible?

-jeremy
"Larry Linson" <bo*****@localhost.net> wrote in message
news:DM*******************@nwrddc01.gnilink.net...
> You would likely "bill" everyone by running a report that prints the
invoice
> information, and that would be based on a query. For it to

"automatically"
> do so on a given date, the database would have to be open, and you'd

have
to
> be testing the date in a Timer event. Or, you could use a Windows
scheduler
> to open it with a specific macro that would run the billing report,
on that
> date. But it would be quite simple to put a command button on the
> switchboard to open the billing report, or a form into which the user could
> insert criteria, if desired.
>
> On the other hand, perhaps you want to bill by e-mail -- you can

interact
> with Outlook, or other e-mail programs or libraries to send e-mails. In that
> case, your button could open a Form that would run the code to create the
> e-mails, or could itself run the code if no user interaction is

required.
>
> I know that's not very specific, but that's about as specific as I
can
be
> from what you've described.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Jeremy Weiss" <jw*****@hotmail.com> wrote in message
> news:vn************@corp.supernews.com...
> > Well, I'm not sure that my requirements are 'trivially' simple,
but the
> end
> > program must appear to be. This client has already tried
Quickbooks and
> some
> > program called Billing Clerk and both have features he's not
wanting > > (especially Quickbooks) and missing some that he does.
> >
> > Tracking the payments doesn't appear to be much of a problem. Making > Access
> > 'remember' to bill everyone each month does. Is there any way to

write
a
> > script (module, macro, etc.) that will 'bill' everyone's account on, say,
> > the 20th of each month? Or, instead of using a set date, tie it to a > button
> > on the switchboard?
> >
> > -jeremy
> >
> > "Larry Linson" <bo*****@localhost.net> wrote in message
> > news:vA*******************@nwrddc03.gnilink.net...
> > > To do what you want, you are obviously going to have to keep a

record
of
> > > payments, as well -- splitting out just a "billing database" is

trying
> to
> > be
> > > "too granular". And, unless your business requirements are

trivially > > simple,
> > > perhaps even if they are, you are likely to be better off searching and
> > > finding yourself a package, Access or other, for this function.
> > >
> > > Larry Linson
> > > Microsoft Access MVP
> > >
> > >
> > > "Jeremy Weiss" <jw*****@hotmail.com> wrote in message
> > > news:vn************@corp.supernews.com...
> > > > I'm trying to build a database that will handle the monthly

billing
> > needs
> > > of
> > > > a small company. I'm charting everything out and here's what I

see:
> > > >
> > > > table for customers
> > > > sub table to track payments received.
> > > >
> > > > No biggie, right? Well, here's my problem. I don't know how to

tell
> > access
> > > > to modify everyone's account balance each month. And I can't just > always
> > > > assume that their monthly bill is $16 just because their balance is
> $16.
> > > If
> > > > I do that then when people pay a few months in advance or if a

check
> > > bounced
> > > > there's no way to track it.
> > > >
> > > > I'm not asking for the source code to build this (yet). I'm

just > trying
> > to
> > > > figure out how to get started. Is this something I could build

into
a
> > > query
> > > > or a macro, or maybe a module?
> > > >
> > > > I've searched Google and Google Groups and I've still not found > anything
> > > > that can spell this out for me. If anyone has any links to

articles
on
> > > this,
> > > > it would be a HUGE help.
> > > >
> > > > Oh, and if I'm going at this all wrong, please let me know.
> > > >
> > > > Thanks,
> > > > -jeremy
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #10

P: n/a
Okay. After reading through all the newsgroup posts I could find that
contained the words billing, invoice, or payment and reading a couple MS
knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table called
PaymentItems and it would join the Payment and Invoice tables. This would
lead me to believe that my logic is flawed in the [Invoices] one to many
[Payments] relationship and that had I been thinking right that would have
been a many to many relationship which would have prompted me to create
another table. That, of course, is an assumption so someone correct me if
I'm wrong (again).

Now, on that PaymentItems table, what fields should there be? I'm sure
PaymentItems_ID and Invoices_ID, but what else?

And, just to cram yet another question into the same long post, how do I
'post' a payment. Meaning, when I key in a payment what needs to take place
on the backend to apply it to an account?

Thanks everyone for the help so for. Please bear with me will I learn this.

-jeremy

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
jw*****@hotmail.com (Jeremy Weiss) wrote in
<vn************@corp.supernews.com>:

I've built many billing systems. They are not exactly trivial, but
they also aren't all that hard. My structure is usually:

tblCustomer customer data
tblInvoice the billing header
tblInvoiceItems the items that make up the billing
tblPayment the payment header (check number, amount, date)
tblPaymentItems the table that joins the payment header to the
invoices that the payment header applies to

You may think you don't need tblInvoiceItems, but if you want to
anything other than just bill for one single thing, you'll want it.

As to generating invoices, it really needs to be a manual process
for a number of reasons. First, you don't want it to happen
automatically, since you want to make sure that all payments
received are applied *before* the invoices are generated. If you
don't do that, you'll have customers being billed for stuff they've
already paid. Second, it's just not a good idea to have anything
like this happen automatically. In my apps, the billings are
generated, then the person doing the billing reviews it and makes
adjustments to the billings before printing the invoices. Once
printed, the invoices (theoretically) cannot be changed/deleted.
Any adjustments need to be done with additional transactions.

Now, this kind of system is not that necessary for a cash business.
I'm currently designing such a system for a client that does not
track receivables as an asset, they simply cash the checks when
they arrive and book the income at that time (as with a standard
cash sale). They do generate monthly invoices, but their accounting
does not require that those be booked before payment is received.
The invoices are really used as nothing more than a notice to the
customer to pay their bill.

To see some examples from one of my older billing applications
(still in use by multiple customers), see:

http://www.bway.net/~dfassoc/Park/

Check out the links for "Create Monthly Billings" and "Applying
Payments." You may or may not need something that elaborate.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #11

P: n/a
TC
Hi Jeremy

Just a tip: with database table design issues, you need to state (or
propose) the primary key (PK) of each table. Choosing the right primary key
is a critical part of table design. A design might have the right tables,
but not the right primary keys for those tables.

HTH,
TC
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Okay. After reading through all the newsgroup posts I could find that
contained the words billing, invoice, or payment and reading a couple MS
knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table called
PaymentItems and it would join the Payment and Invoice tables. This would
lead me to believe that my logic is flawed in the [Invoices] one to many
[Payments] relationship and that had I been thinking right that would have
been a many to many relationship which would have prompted me to create
another table. That, of course, is an assumption so someone correct me if
I'm wrong (again).

Now, on that PaymentItems table, what fields should there be? I'm sure
PaymentItems_ID and Invoices_ID, but what else?

And, just to cram yet another question into the same long post, how do I
'post' a payment. Meaning, when I key in a payment what needs to take place on the backend to apply it to an account?

Thanks everyone for the help so for. Please bear with me will I learn this.
-jeremy

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
jw*****@hotmail.com (Jeremy Weiss) wrote in
<vn************@corp.supernews.com>:

I've built many billing systems. They are not exactly trivial, but
they also aren't all that hard. My structure is usually:

tblCustomer customer data
tblInvoice the billing header
tblInvoiceItems the items that make up the billing
tblPayment the payment header (check number, amount, date)
tblPaymentItems the table that joins the payment header to the
invoices that the payment header applies to

You may think you don't need tblInvoiceItems, but if you want to
anything other than just bill for one single thing, you'll want it.

As to generating invoices, it really needs to be a manual process
for a number of reasons. First, you don't want it to happen
automatically, since you want to make sure that all payments
received are applied *before* the invoices are generated. If you
don't do that, you'll have customers being billed for stuff they've
already paid. Second, it's just not a good idea to have anything
like this happen automatically. In my apps, the billings are
generated, then the person doing the billing reviews it and makes
adjustments to the billings before printing the invoices. Once
printed, the invoices (theoretically) cannot be changed/deleted.
Any adjustments need to be done with additional transactions.

Now, this kind of system is not that necessary for a cash business.
I'm currently designing such a system for a client that does not
track receivables as an asset, they simply cash the checks when
they arrive and book the income at that time (as with a standard
cash sale). They do generate monthly invoices, but their accounting
does not require that those be booked before payment is received.
The invoices are really used as nothing more than a notice to the
customer to pay their bill.

To see some examples from one of my older billing applications
(still in use by multiple customers), see:

http://www.bway.net/~dfassoc/Park/

Check out the links for "Create Monthly Billings" and "Applying
Payments." You may or may not need something that elaborate.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc


Nov 12 '05 #12

P: n/a
Thanks for the tip TC. I guess that means I should modify:

[Customers] one to many [Invoices]

into:

Customers.id (PK) one to many Invoices.customer_id (FK)

Did I catch that right? Or do I need to list the fields in each table?

-jeremy

"TC" <a@b.c.d> wrote in message news:<1064997910.881552@teuthos>...
Hi Jeremy

Just a tip: with database table design issues, you need to state (or
propose) the primary key (PK) of each table. Choosing the right primary key is a critical part of table design. A design might have the right tables,
but not the right primary keys for those tables.

HTH,
TC
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Okay. After reading through all the newsgroup posts I could find that
contained the words billing, invoice, or payment and reading a couple MS
knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table called
PaymentItems and it would join the Payment and Invoice tables. This would lead me to believe that my logic is flawed in the [Invoices] one to many
[Payments] relationship and that had I been thinking right that would have been a many to many relationship which would have prompted me to create
another table. That, of course, is an assumption so someone correct me if I'm wrong (again).

Now, on that PaymentItems table, what fields should there be? I'm sure
PaymentItems_ID and Invoices_ID, but what else?

And, just to cram yet another question into the same long post, how do I
'post' a payment. Meaning, when I key in a payment what needs to take

place
on the backend to apply it to an account?

Thanks everyone for the help so for. Please bear with me will I learn

this.

-jeremy

Nov 12 '05 #13

P: n/a
jw*****@hotmail.com (Jeremy Weiss) wrote in
<vn************@corp.supernews.com>:
Okay. After reading through all the newsgroup posts I could find
that contained the words billing, invoice, or payment and reading
a couple MS knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table
called PaymentItems and it would join the Payment and Invoice
tables. This would lead me to believe that my logic is flawed in
the [Invoices] one to many [Payments] relationship and that had I
been thinking right that would have been a many to many
relationship which would have prompted me to create another table.
That, of course, is an assumption so someone correct me if I'm
wrong (again).
Well, if there can be multiple payments per invoice, and multiple
invoices per payment (think about a single check that pays 2
invoices), then you definitely need a many-to-many.
Now, on that PaymentItems table, what fields should there be? I'm
sure PaymentItems_ID and Invoices_ID, but what else?
All it really needs is PaymentID and InvoiceID and Amount. It's an
open question whether or not it you need a separate AutoNumber
field as primary key. PaymentID and InvoiceID ought to suffice as I
can't think of a reason why you'd want to break out part of a check
into two chunks for a single invoice. Actually, now that I think
about it, yes, I guess you might want to record what's on a memo on
a check.

Consider:

Invoice is for $100 and a $15 late fee.

Customer writes a check for $105 and the memo says $90 + $15,
because the customer thinks the $100 should actually be $90. So,
you might apply it as two items, one for the late fee and one for
the $90 partial payment.

In that case, a unique index on PaymentID + InvoiceID wouldn't
work. It's still an open question whether or no you need a third
field for the PK or not, though. Supposedly, relational theory
requires a primary key. That could be gotten in a number of
artificial ways, such as adding sequence number and using a
3-column PK, but I don't see that this is preferable to just adding
an AutoNumber and making it the PK. I don't know that there's any
reason to have a PK, anyway, but, on the other hand, it's
conceivable you'd want to navigate to a unique record at some
point, and without a PK you can't do that.
And, just to cram yet another question into the same long post,
how do I 'post' a payment. Meaning, when I key in a payment what
needs to take place on the backend to apply it to an account?


In the UI, you need a list of open invoices with balances, a
mechanism for designating which invoices you are applying the
payment to (I use checkboxes) and unbound fields for entering the
payment header information (payment amount, payment date, check
number, credit card information, etc.). You'll need checks that
things add up, and you'll have to decide on your business rules in
regard to how you handle partial payments and unapplied cash.

In the background, once you click POST, you'll want to open up a
separate workspace and use transactions to do these things:

1. create the payment header. Grab the PaymentID. This is most
easily done with a recordset open with the dbAppendOnly flag.

2. with the PaymentID retrieved in #1, insert the payment items,
one for each invoice the payment is applied to, with the amount
apportioned for each invoice.

3. you may or may not want to maintain a running balance in the
invoice header. I recommend against this, though, as it's too easy
for it to get out of synch with the actual payments.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #14

P: n/a
some scenarios to think about
one cheque pays many invoices
one cheque pays partial invoice
one cheque pays all invoices and more (customer paid too much)
one cheque pays invoice, short of .01

posting a payment
enter customer id
show list of open invoices and $ amount
enter cheque #, cheque date, cheque amount
apply part of the payment to one or more invoices
if all invoices are paid, and payment amount > 0, apply to
customer's account
if payment amount is 0, all invoices paid except for .01, write it
off

a 'nice to have' button, apply payments to invoices (oldest first)
until the
payment is fully applied - reduces keying

and then you need to deal with errors like payment posted to wrong
customer, wrong cheque amount entered, wrong date entered ...
how to reverse it
how to deal with 'applied to customer account'
how to deal with 'write offs'

or you planning on producing customer statements ?
what about late payment penalties ?
what about credit memos ? debit memos ?
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message news:<vn************@corp.supernews.com>...
Okay. After reading through all the newsgroup posts I could find that
contained the words billing, invoice, or payment and reading a couple MS
knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table called
PaymentItems and it would join the Payment and Invoice tables. This would
lead me to believe that my logic is flawed in the [Invoices] one to many
[Payments] relationship and that had I been thinking right that would have
been a many to many relationship which would have prompted me to create
another table. That, of course, is an assumption so someone correct me if
I'm wrong (again).

Now, on that PaymentItems table, what fields should there be? I'm sure
PaymentItems_ID and Invoices_ID, but what else?

And, just to cram yet another question into the same long post, how do I
'post' a payment. Meaning, when I key in a payment what needs to take place
on the backend to apply it to an account?

Thanks everyone for the help so for. Please bear with me will I learn this.

-jeremy

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
jw*****@hotmail.com (Jeremy Weiss) wrote in
<vn************@corp.supernews.com>:

I've built many billing systems. They are not exactly trivial, but
they also aren't all that hard. My structure is usually:

tblCustomer customer data
tblInvoice the billing header
tblInvoiceItems the items that make up the billing
tblPayment the payment header (check number, amount, date)
tblPaymentItems the table that joins the payment header to the
invoices that the payment header applies to

You may think you don't need tblInvoiceItems, but if you want to
anything other than just bill for one single thing, you'll want it.

As to generating invoices, it really needs to be a manual process
for a number of reasons. First, you don't want it to happen
automatically, since you want to make sure that all payments
received are applied *before* the invoices are generated. If you
don't do that, you'll have customers being billed for stuff they've
already paid. Second, it's just not a good idea to have anything
like this happen automatically. In my apps, the billings are
generated, then the person doing the billing reviews it and makes
adjustments to the billings before printing the invoices. Once
printed, the invoices (theoretically) cannot be changed/deleted.
Any adjustments need to be done with additional transactions.

Now, this kind of system is not that necessary for a cash business.
I'm currently designing such a system for a client that does not
track receivables as an asset, they simply cash the checks when
they arrive and book the income at that time (as with a standard
cash sale). They do generate monthly invoices, but their accounting
does not require that those be booked before payment is received.
The invoices are really used as nothing more than a notice to the
customer to pay their bill.

To see some examples from one of my older billing applications
(still in use by multiple customers), see:

http://www.bway.net/~dfassoc/Park/

Check out the links for "Create Monthly Billings" and "Applying
Payments." You may or may not need something that elaborate.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #15

P: n/a
Roger, I don't need help counting all the problems. I need help _accounting_
for the problems. In other words, how do I build what's needed to handle all
these things?

-jeremy

"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...
some scenarios to think about
one cheque pays many invoices
one cheque pays partial invoice
one cheque pays all invoices and more (customer paid too much)
one cheque pays invoice, short of .01

posting a payment
enter customer id
show list of open invoices and $ amount
enter cheque #, cheque date, cheque amount
apply part of the payment to one or more invoices
if all invoices are paid, and payment amount > 0, apply to
customer's account
if payment amount is 0, all invoices paid except for .01, write it
off

a 'nice to have' button, apply payments to invoices (oldest first)
until the
payment is fully applied - reduces keying

and then you need to deal with errors like payment posted to wrong
customer, wrong cheque amount entered, wrong date entered ...
how to reverse it
how to deal with 'applied to customer account'
how to deal with 'write offs'

or you planning on producing customer statements ?
what about late payment penalties ?
what about credit memos ? debit memos ?
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message

news:<vn************@corp.supernews.com>...
Okay. After reading through all the newsgroup posts I could find that
contained the words billing, invoice, or payment and reading a couple MS
knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table called
PaymentItems and it would join the Payment and Invoice tables. This would lead me to believe that my logic is flawed in the [Invoices] one to many
[Payments] relationship and that had I been thinking right that would have been a many to many relationship which would have prompted me to create
another table. That, of course, is an assumption so someone correct me if I'm wrong (again).

Now, on that PaymentItems table, what fields should there be? I'm sure
PaymentItems_ID and Invoices_ID, but what else?

And, just to cram yet another question into the same long post, how do I
'post' a payment. Meaning, when I key in a payment what needs to take place on the backend to apply it to an account?

Thanks everyone for the help so for. Please bear with me will I learn this.
-jeremy

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
jw*****@hotmail.com (Jeremy Weiss) wrote in
<vn************@corp.supernews.com>:

I've built many billing systems. They are not exactly trivial, but
they also aren't all that hard. My structure is usually:

tblCustomer customer data
tblInvoice the billing header
tblInvoiceItems the items that make up the billing
tblPayment the payment header (check number, amount, date)
tblPaymentItems the table that joins the payment header to the
invoices that the payment header applies to

You may think you don't need tblInvoiceItems, but if you want to
anything other than just bill for one single thing, you'll want it.

As to generating invoices, it really needs to be a manual process
for a number of reasons. First, you don't want it to happen
automatically, since you want to make sure that all payments
received are applied *before* the invoices are generated. If you
don't do that, you'll have customers being billed for stuff they've
already paid. Second, it's just not a good idea to have anything
like this happen automatically. In my apps, the billings are
generated, then the person doing the billing reviews it and makes
adjustments to the billings before printing the invoices. Once
printed, the invoices (theoretically) cannot be changed/deleted.
Any adjustments need to be done with additional transactions.

Now, this kind of system is not that necessary for a cash business.
I'm currently designing such a system for a client that does not
track receivables as an asset, they simply cash the checks when
they arrive and book the income at that time (as with a standard
cash sale). They do generate monthly invoices, but their accounting
does not require that those be booked before payment is received.
The invoices are really used as nothing more than a notice to the
customer to pay their bill.

To see some examples from one of my older billing applications
(still in use by multiple customers), see:

http://www.bway.net/~dfassoc/Park/

Check out the links for "Create Monthly Billings" and "Applying
Payments." You may or may not need something that elaborate.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #16

P: n/a
TC
> Did I catch that right?

Not really :-)

For each table (independently), you need to ask, What is the primary key of
that table? You should be able to answer that question *regardless* of the
relationships involving that table. For example, you should be able to state
the primary key of the Invoices table, regardless of what relationships that
table might or might not be involved in. In the absence of that information,
no-one can possibly comment (reliably) on the structure of your database, or
the suitability of any SQL queries that you write for it.

Jeremy, the concept of primary keys is critically important when you are
designing a database. *Absolutely nothing else matters*, until you have got
that right. Here is a relevant article. I can't remember whether I suggested
it before, or not. If not, take some time to read it now:

http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC
Jeremy Weiss <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Thanks for the tip TC. I guess that means I should modify:

[Customers] one to many [Invoices]

into:

Customers.id (PK) one to many Invoices.customer_id (FK)

Did I catch that right? Or do I need to list the fields in each table?

-jeremy

"TC" <a@b.c.d> wrote in message news:<1064997910.881552@teuthos>...
Hi Jeremy

Just a tip: with database table design issues, you need to state (or
propose) the primary key (PK) of each table. Choosing the right primary

key
is a critical part of table design. A design might have the right tables,
but not the right primary keys for those tables.

HTH,
TC
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vn************@corp.supernews.com...
Okay. After reading through all the newsgroup posts I could find that
contained the words billing, invoice, or payment and reading a couple MS knowledgebase articles I've gotten this far:

[Customers]
[Invoices]
[Invoice_Details]
[Payments]

The relationships I've set up so far are:

[Customers] one to many [Invoices]
[Customers] one to many [Payments]
[Invoices] one to many [Invoice_Details]
[Invoices] one to many [Payments]

Now, if I'm reading it right, David said that I needed a table called
PaymentItems and it would join the Payment and Invoice tables. This

would lead me to believe that my logic is flawed in the [Invoices] one to many [Payments] relationship and that had I been thinking right that would have been a many to many relationship which would have prompted me to create another table. That, of course, is an assumption so someone correct me if I'm wrong (again).

Now, on that PaymentItems table, what fields should there be? I'm sure
PaymentItems_ID and Invoices_ID, but what else?

And, just to cram yet another question into the same long post, how do I 'post' a payment. Meaning, when I key in a payment what needs to take

place
on the backend to apply it to an account?

Thanks everyone for the help so for. Please bear with me will I learn

this.

-jeremy


Nov 12 '05 #17

P: n/a
I want to start off by saying thanks to everyone for helping me get this
far. I went back and looked at the first few posts I made and was amazed by
how much you all have taught me. Unfortunately I've still got a ways to go,
so I hope patience isn't wearing thin.

Below is the table structure that I currently have. It's a far cry from the
two tables I started with, but I'm thinking I'm still missing some things.

==============
Table: Customer_tbl

Columns:
----------
id (PK)
CustomerType_tbl_ID (FK)
FirstName
LastName
Company
Address
City
State
Zip
Phone
FAX
Notes
AccountBalance
BaseRate

Relationships
-------------
Customer_tbl.id <--> Invoice_tbl.Customer_tbl_ID
One-To-Many

Customer_tbl.id <--> Payment_tbl.Customer_tbl_ID
One-To-Many

===========
Table: CustomerType_tbl

Columns
---------
id (PK)
Type

Relationships
-------------
CustomerType_tbl.id <--> Customer_tbl.CustomerType_tbl_ID
One-To-Many

=============
Table: Invoice_tbl

Columns
---------
Id (PK)
Customer_tbl_ID (FK)
Date

Relationships
-------------
Customer_tbl.id <--> Invoice_tbl.Customer_tbl_ID
Relationship Type: One-To-Many

Invoice_tbl.id <--> InvoiceItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

Invoice_tbl.id <--> PaymentItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

==========
Table: InvoiceItems_tbl

Columns
---------
id (PK)
Invoice_tbl_ID (FK)
Discription
Amount
DiscountRate

Relationships
-------------
Invoice_tbl.id <--> InvoiceItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

===========
Table: Payment_tbl

Columns
---------
Id (PK)
Customer_tbl_ID (FK)
Date
CheckNumber
Meme

Relationships
-------------
Customer_tbl.id <--> Payment_tbl.Customer_tbl_ID
RelationshipType: One-To-Many

Payment_tbl.id <--> PaymentItems_tbl.Payment_tbl_ID
RelationshipType: One-To-Many

============
Table: PaymentItems_tbl

Columns
---------
id (PK)
Payment_tbl_ID (FK)
Invoice_tbl_ID (FK)
Amount

Relationships
-------------
Invoice_tbl.id <--> PaymentItems_tbl.Invoice_tbl_ID
RelationshipType: One-To-Many

Payment_tbl.id <--> PaymentItems_tbl.Payment_tbl_ID
RelationshipType: One-To-Many
===================
I'm thinking I need a field in the Invoice_tbl to denote if that invoice has
been paid but I'm not sure about that. Also, I added a field in the
Customer_tbl to keep the base monthly rate for that client and to also keep
up with account balace (since some customers pay months in advance. Will
this work or is there a better way to go about it all?

Again, thanks for all the help.

-jeremy
Nov 12 '05 #18

P: n/a
> I'm thinking I need a field in the Invoice_tbl to denote if that invoice has
been paid but I'm not sure about that. Also, I added a field in the
Customer_tbl to keep the base monthly rate for that client and to also keep
up with account balace (since some customers pay months in advance. Will
this work or is there a better way to go about it all?


if all invoices are always either paid in full or not paid, then you
could put a checkbox. If you receive multiple payments against a
single bill, you could have a child table of payments. Read around...
I know this has been answered a million times... check places like...
Allen Browne's website, Tony Toews's website (since he specializes in
accounting applications)...
Nov 12 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.