Adding contents of multiple cells

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

Guest

I have a dababase created by someone who is no longer available, and I'm
trying to learn how to use it in order to get an immediate job done. I am
not familiar with the terminology, so I do not understand what I am looking
at in the help index.

Can someone give me plain English help that will let me fix this immediate
problem, please.

This database contains rows of cells with numerical values that must be
added together to return a "Total Due" for each record. Each record will
have return a different "total due" value, depending on the amounts shown in
the various cells included.

As it is now set up, the returned value does not calculate correctly, and I
don't know where to look to find out what cells are included in that returned
value.
 
Flying Fingers,

The Total Due should be calculated in a Query. Can you please give some
more information about the fields in this table, and some examples of
the calculations required?
 
Thanks. This database is used to create monthly water bills for a community
association. For example, the columns are for water, sewer, maintenance,
other, which are added together for a subtotal; plus rent to get a total
amount due.

Each month, the amounts in the cells may change based on usage, except that
rent changes once a year. The rent has now increased by $10, but I cannot
get the total amount due to pickup the increase.

Does this help you understand my problem?
 
Flying Fingers

It is unfortunate that this database appears to have been incorrectly
designed. Separate columns for water, sewer, etc is the kind of thing
that is applicable in a spreadsheet or some such, but not in a database.
Still, I suppose you are stuck with it now?

Do you know where these calculations are taking place? E.g. as a
calculated field in the query that the form is based on, or in an
unbound control on the form itself? On the basis of the information you
have supplied so far, I imagine it is something like this....
Total Due: [water]+[sewer]+[maintenance]+[other]+[rent]

If you still need more help with this, please give the names of the
fields in the affected table(s), the SQL of the query that the form is
based on, and the details of how the form relates to the data.
 
Thanks for your tip; however, I do not know where the calculation takes
place. There are so many different forms and tables and....I don't know
enough about Access to even tell you what it is that I'm looking at. There is
no one locally whom I can call to get help with this. I'm not looking for
free tutoring, but I certainly do wish I could find someone to teach me the
basics. In the meantime, I've got this dilemma--how to get the job done now.
Thank you for trying.

Steve Schapel said:
Flying Fingers

It is unfortunate that this database appears to have been incorrectly
designed. Separate columns for water, sewer, etc is the kind of thing
that is applicable in a spreadsheet or some such, but not in a database.
Still, I suppose you are stuck with it now?

Do you know where these calculations are taking place? E.g. as a
calculated field in the query that the form is based on, or in an
unbound control on the form itself? On the basis of the information you
have supplied so far, I imagine it is something like this....
Total Due: [water]+[sewer]+[maintenance]+[other]+[rent]

If you still need more help with this, please give the names of the
fields in the affected table(s), the SQL of the query that the form is
based on, and the details of how the form relates to the data.

--
Steve Schapel, Microsoft Access MVP


Flying said:
Thanks. This database is used to create monthly water bills for a community
association. For example, the columns are for water, sewer, maintenance,
other, which are added together for a subtotal; plus rent to get a total
amount due.

Each month, the amounts in the cells may change based on usage, except that
rent changes once a year. The rent has now increased by $10, but I cannot
get the total amount due to pickup the increase.

Does this help you understand my problem?
Click to expand...
Click to expand...
 
Flying Fingers,

Open the form concerned in design view, and find the textbox which is
supposed to show the Total Due. Right-click on it, and select
Properties from the pop-up menu which should appear. In the Property
sheet, find the Control Source property. What is the entry there?

Then click on the little square area at the top-left of the form, where
the top and left rulers almost meet. The Property sheet should then
show the properties of the form itself. Find the Record Source
property... what is the entry in there?

Close the form design, and go back to the Database Window, and look in
the list of tables and the list of queries until you find the object
with the same name as is entered in the Record Source of the form. If
it's a query, open it in design view, and then select SQL from the View
menu. Copy/Paste the SQL of the query into your reply post.
 
Textbox showing total due: AmountDue
Control Source property: AmountDue
Record Source property: SELECT Billing.BillingID, Billing.BillingClient,
Billing.Lot, Billing.Tenant, Billing.Previous, Billing.Present,
Billing.Usage, Billing.[1k], Billing.Water, Billing.Storm, Billing.Sewer,
Billing.Recycle, Billing.Subtotal, Billing.RV, Billing.MH, Billing.AmountDue,
Billing.Remarks, Billing.[Reading Date], Billing.Collected, Billing.Other,
Billing.BillingComment FROM Billing WHERE
(((Billing.BillingClient)=Forms!SelectClientForm!BillingClientList));
 
Flying Fingers,

Great, thanks a lot for this, it helps a lot. Now, the next question...
Is 'Billing' a table or a query? If it's a query, same as before, can
you post back with the SQL.
 
Billing is a table. Thank you.

You're going above and beyond, and I do appreciate it.

Steve Schapel said:
Flying Fingers,

Great, thanks a lot for this, it helps a lot. Now, the next question...
Is 'Billing' a table or a query? If it's a query, same as before, can
you post back with the SQL.

--
Steve Schapel, Microsoft Access MVP


Flying said:
Textbox showing total due: AmountDue
Control Source property: AmountDue
Record Source property: SELECT Billing.BillingID, Billing.BillingClient,
Billing.Lot, Billing.Tenant, Billing.Previous, Billing.Present,
Billing.Usage, Billing.[1k], Billing.Water, Billing.Storm, Billing.Sewer,
Billing.Recycle, Billing.Subtotal, Billing.RV, Billing.MH, Billing.AmountDue,
Billing.Remarks, Billing.[Reading Date], Billing.Collected, Billing.Other,
Billing.BillingComment FROM Billing WHERE
(((Billing.BillingClient)=Forms!SelectClientForm!BillingClientList));
Click to expand...
Click to expand...
 
Flying Fingers

I was afraid you were going to say that. Not about "going above and
beyond". :-) I mean about the table. It means, as I suspected before,
that there are design flaws which makes things more difficult all round.
It also means that the AmountDue is being calculated "somewhere" and
then written to the table, which means it becomes trickier to track
down. The "somewhere" could be a macro or a VBA procedure, and as a
guess it probably happens on a form event, and you have already
mentioned you have many forms. Anyway, let's start with the form you
looked at before when you gave me the Record Source. Open that form in
design view, and then select Code from the View menu. This will open
the VB Editor window with the form's module. Have a look through any
code there, and see if you can see mention of AmountDue. If so,
copy/paste the procedure that contains it and put it in your post here.
Let's see where that leads us.
 
Back
Top