New db

[Guest]

Hi peoples:
I'm creating a database to help track customer payments.
What I'm doing is, so you'll have a better understanding is our hometown
newspaper. Ok - some customers are going to want to pay for the ad in full
at once and some are going to want to make payments (on their weekly ads)
each month through the year.

My database needs to show me which advertisers currently have an ad running
and their payment history.

What I've done is set up 3 tables.

A customers table that has all the customer contact info on it.

A custoryhistory table that has their newspaper ad history in it.

And thirdly I'm trying to connect the payment table too all this so I can
see for example when customer1 has paid me for the current ad (and customer1
has a year long commitment) so he will be paying me 12 times for one entry on
the customerhistory table.

So I've created a customer form, with customerhistory as a subform, then
also put a paymenthistory subform in the customerhistorysubform. And it's a
little confusing with all those fields. Does anyone know a better way to
track this kind of info OR is there a template database already set up for
this on the microsoft site?

 

[louisjohnphillips]

Hi peoples:
I'm creating a database to help track customer payments.
What I'm doing is, so you'll have a better understanding is our hometown
newspaper. Ok - some customers are going to want to pay for the ad in full
at once and some are going to want to make payments (on their weekly ads)
each month through the year.

My database needs to show me which advertisers currently have an ad running
and their payment history.

What I've done is set up 3 tables.

A customers table that has all the customer contact info on it.

A custoryhistory table that has their newspaper ad history in it.

And thirdly I'm trying to connect the payment table too all this so I can
see for example when customer1 has paid me for the current ad (and customer1
has a year long commitment) so he will be paying me 12 times for one entry on
the customerhistory table.

So I've created a customer form, with customerhistory as a subform, then
also put a paymenthistory subform in the customerhistorysubform. And it's a
little confusing with all those fields. Does anyone know a better way to
track this kind of info OR is there a template database already set up for
this on the microsoft site?

Would not it be better, as a business practice, to create a single row
in the customerhistory table for each time the advertisement runs.

Having done so, a customer could run more than one advertisement in an
issue and pay for each individually. This would allow you to
partially rebate advertisements with errors, offer free re-runs of
advertisements or charge a different rate for depending on placement.

 

[John W. Vinson]

Hi peoples:
I'm creating a database to help track customer payments.
What I'm doing is, so you'll have a better understanding is our hometown
newspaper. Ok - some customers are going to want to pay for the ad in full
at once and some are going to want to make payments (on their weekly ads)
each month through the year.

I actually do the ad billing for a local newspaper using an Access database.

My database needs to show me which advertisers currently have an ad running
and their payment history.

What I've done is set up 3 tables.

A customers table that has all the customer contact info on it.

A custoryhistory table that has their newspaper ad history in it.

And thirdly I'm trying to connect the payment table too all this so I can
see for example when customer1 has paid me for the current ad (and customer1
has a year long commitment) so he will be paying me 12 times for one entry on
the customerhistory table.

So I've created a customer form, with customerhistory as a subform, then
also put a paymenthistory subform in the customerhistorysubform. And it's a
little confusing with all those fields. Does anyone know a better way to
track this kind of info OR is there a template database already set up for
this on the microsoft site?

My design is somewhat similar, but different in some details. I have a
Customer table of course, with CustomerID, name, address; it's related one to
many to a Contacts table for the (rather rare) cases where there are two or
more people who might get the ad bills. One is flagged as "paymaster" with a
yes/no field.

There's also an Invoices table related one-to-many to an Ads table. A customer
might be invoiced for one ad, or for a run of four ads, or for an entire year
of ads. The Ads table has a unit price for each issue's ad and there's a -
formally redundant - total price field on the invoice table; this allows for
special contract prices (e.g. four ads for the price of three). There's a
Payment field in the Invoice table. If a customer makes a partial payment (or
even an intentional overpayment to pay in advance) that just goes in the
table; I have reports to sum up the total price fields and the payments, to
see who owes money and who's overpaid.


John W. Vinson [MVP]

 

[Guest]

John - that's sort of what I'm struggling with right now.
I've created a form from the customers table with the customerhistory and
payments table embedded as subforms.

However I'm still trying to figure out how to have an entry for each
customer for each time their ad runs without having to view all of their
history.

 

[John W. Vinson]

John - that's sort of what I'm struggling with right now.
I've created a form from the customers table with the customerhistory and
payments table embedded as subforms.

However I'm still trying to figure out how to have an entry for each
customer for each time their ad runs without having to view all of their
history.

That's sort of what the Invoices table does in my app. I use a single Form
based on Invoices so I see only one invoice at a time; on that form is a
Subform displaying all of the ads (typically either one or four, just because
of how the paper does business) for that invoice.

I'm not sure what you mean by "have an entry for each customer each time their
ad runs" - the customer information is entered, once per customer, in a
Customers table, and that's the only place it's stored!

John W. Vinson [MVP]