Queiy

  • Thread starter Thread starter Juan
  • Start date Start date
J

Juan

This might be a dumb question but, I am creating this database that has
copier information, billing information, department information, etc.
Every month we get a listing of all the copiers out there with the
monthly click count (copies). We charge them a base and "per click"
charge every month. I created the first months billing report which
includes the summation of base + "click charge". Sometime the base or
the click charge might change after the contract is up with them. But
what I want to do is be able to run this query (Billing Report) every
month but say that this month we charge them $200 base and next month
we are going to charge them $250. I dont want the information
(calculations) to change for last month. Is there any way to freeze
that outcome?
 
Juan said:
This might be a dumb question but, I am creating this database that has
copier information, billing information, department information, etc.
Every month we get a listing of all the copiers out there with the
monthly click count (copies). We charge them a base and "per click"
charge every month. I created the first months billing report which
includes the summation of base + "click charge". Sometime the base or
the click charge might change after the contract is up with them. But
what I want to do is be able to run this query (Billing Report) every
month but say that this month we charge them $200 base and next month
we are going to charge them $250. I dont want the information
(calculations) to change for last month. Is there any way to freeze
that outcome?
Click to expand...

It for sure is not a dumb question...

A lot depends on your business rules...

for example, in my business we "freeze the
outcome" in PO tables...

Another tact might be to create a table
(say "BillingCharges") where charges
are recorded for each entity (customer?)
when they change

CustID StartDate BaseChg ClickChg
1 1/1/2006 200 .05
....
1 6/1/2006 250 .05

A billing report would to me summon up
a "billing date" along with all the other info
like maybe a CustID in a main query.

Join that main query to BillingCharges
on CustID.

Then use a correlated subquery

as criteria on BillingCharges.StartDate

to pull correct
BaseChg and ClickChg for each specific
CustID and BillingDate....

something like:

(SELECT
Max(t.StartDate)
FROM
BillingCharges As t
WHERE
t.CustID = mainquery.CustID
AND
t.StartDate <= mainquery.BillingDate)

so as I am looking at a record
for a specific CustID
for a certain BillingDate
in the main query,
give me the BaseChg and ClickChg
from a record in BillingCharges
for that CustID

whose StartDate is the latest StartDate
before or equal to the BillingDate
 
Back
Top