time bound calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
Good evening

I have created a table which has 10 fields. I am using a data entry form to
enter the data in the table. Also I have created a report based on this
table. The data in some fields of the table are multiplication or division
of two or more fields in the table. Now, I want this to happen only in the
end of the month how to do that? please help.
Thank you
 
Hello,
Good evening

I have created a table which has 10 fields. I am using a data entry form to
enter the data in the table. Also I have created a report based on this
table. The data in some fields of the table are multiplication or division
of two or more fields in the table.
Click to expand...

Then these fields SHOULD NOT EXIST.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
Now, I want this to happen only in the
end of the month how to do that? please help.
Thank you
Click to expand...

What is it that you want to happen only at the end of the month?
You've described a table (an improperly normalized one at that), and a
report. You haven't described any event that is to "happen only in the
end of the month". Please explain.

John W. Vinson[MVP]
 
John Vinson said:
Then these fields SHOULD NOT EXIST.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


What is it that you want to happen only at the end of the month?
You've described a table (an improperly normalized one at that), and a
report. You haven't described any event that is to "happen only in the
end of the month". Please explain.

John W. Vinson[MVP]
Click to expand...


Thank you for the reply.


I wanted to have caluculaton only in the end of the month.
My rport contains the 12 fields for 12 months so when I click the preview
report command button in my form it gives the personal data plus result of
the calculation for 12 months. What I wanted is when I press the preview
report command button it should gives me a report which has the personal
information plus the rresult of the calculation only for past months not
coming month. the field for the coming months should read N/A

To make the things more clear lets say I wanted to have report on June 15,
so when I press the preview report or print report command button it should
give the report or print the report with the result till May (June is not
complete. and, the fields for the rest of th month should read N/A

Thank you
 
I wanted to have caluculaton only in the end of the month.
My rport contains the 12 fields for 12 months so when I click the preview
report command button in my form it gives the personal data plus result of
the calculation for 12 months. What I wanted is when I press the preview
report command button it should gives me a report which has the personal
information plus the rresult of the calculation only for past months not
coming month. the field for the coming months should read N/A

To make the things more clear lets say I wanted to have report on June 15,
so when I press the preview report or print report command button it should
give the report or print the report with the result till May (June is not
complete. and, the fields for the rest of th month should read N/A
Click to expand...

You'll have to give us some help here. How are your Tables
constructed? I fear that you've made a very common mistake and that
you have one *field* in the table for each month! That's spreadsheet
logic, not database logic.

Please describe the structure of your Tables; if the Report is based
on a Query, please post the SQL view of the query. The problem you're
facing is probably *not* due to the Report but instead to the design
of your database.

John W. Vinson[MVP]
 
fields in my table are: Name, Address, Post, Cost/month, Paid, Due, then
fields for 12 month
Click to expand...

That design is SIMPLY WRONG.

Is the field January for January 2005? 2006? 2007? 1992?

If you have a One (student) to Many (payments) relationship, *model it
as a one to many relationship*. A better table design would be

Students:
StudentID <Autonumber, Primary Key>
LastName
FirstName
Address <or split this into address, city, state, Zip>
Post
Cost/Month

Payments
StudentID <Long Integer, link to Students>
PaymentDate Date/Time
AmountPaid

The Report could be based on a Crosstab query selecting all the
records from Payments for each student.
report is not based on Query, It contains the fields from table (not all)

My purpose of creating this report is to have a hard copy in the end of
every month as a signed record. so by December the student and I myself will
have record in hard copy.

Thank you
Click to expand...

With your current design, you presumably have NULL values in the
months for which there is no payment? If so, you can set the Format
property of each month's textbox to

"$0.00";"-$0.00";"$0.00";"N/A"

to use different formats for positive, negative, zero and NULL values
respectively; and put another textbox on the form with a Control
SOurce

=NZ([Jan]) + NZ([Feb]) + NZ([Mar]) <etc through all twelve months>

using your fieldnames.

John W. Vinson[MVP]
 
John,
I most often read your replies not to check them for accuracy or learn
something new but to just hear you from the pulpit:

"Open your Book of Codd to the 3rd chapter of Normal Form verses 1-12. Let's
read together:
And so brother Boyce called thousands together and spoke 'Thou shalt not
create spreadsheet...'
"
;-) must be Friday...
Click to expand...

LOL!!!

Sounds more like Sunday morning actually... thanks Duane!

John W. Vinson[MVP]
 
Back
Top