You really must not have the Total Hours Attended field in your table,
believe me, so delete it. Storing data derived from other data is very bad
design, and very risky as the integrity of the data is immediately put at
risk.
All you need to do is create query based on the table. Add all the fields
form the table to the query. Then in the 'field' row of a blank column in
query design view put:
[Total Hours Attended]: Nz([1G hours attended],0)+Nz([2G hours
attended],0)…..+Nz([10G hours attended],0)
The Nz function is used here to take account of any Nulls.
If you wish you can sort the query by one or more columns to control the
order in which the rows are returned. Save this query and use it as the
RecordSource of your data input form instead of the table. You'll find that
as you enter data into the form the total hours will be automatically
computed and shown in the Total Hours Attended column. The important thing
is that the value in this computed column is always derived from the values
in the other ten columns, so whatever changes you make to the data in any of
those columns the total will always be correct. If you *stored* the values
in a column in the table on the other hand, there is nothing to stop changes
being made to one or more of the values, but not being reflected in the
total, i.e. an update anomaly, which is bad news.
While the above will work, its worth pointing out that your table structure
is actually not a very good one. As a rule of thumb a well designed
relational database is likely to have a larger number of tall skinny tables
rather than a smaller number of short fat ones. A good design would, instead
of having the 10 hours attended columns in the main table, would have them as
separate *rows* in a related table. This would mean you can have as few or
as many separate hours attended values per row of the main table rather than
being restricted to 10. It also makes aggregating the values much simpler as
you can use any of the usual aggregation operators, SUM, AVG, MIN, MAX etc,
in a query which joins the two tables. The table of Hours Attended would be
related to the main table by a foreign key long integer number column (not an
autonumber) as a foreign key referencing the autonumber primary key of the
main table.
For data input you'd use a form based on the main table with a subform (in
continuous form view) based on the Hours Attended table and linked to the
main form on the key fields. A computed control with a ControlSource of
=Sum([Hours Attended]) in the subform's footer would show the total hours for
the current main form's record.
Ken Sheridan
Stafford, England
HectorLPerez said:
first of all thank you for replying so quickly.. this problem has been
killing my coworker and I for 2 full days now...
for this particular project, I need all the information for each new record
to be in one row.
Here is what I did:
1. I created a New Table in Design View and added all the fields I needed.
Aside from the many other fields I created for this project, there are about
10 different fields named : 1G hours attended, 2G hours attended.. and so on.
then I made a field named Total Hours Attended.
2. Using that field list, I created a form.
3. then I created a query to total all the Hours Attended fields and used
that query as the control source in the Total Hours field in Table Design
View.
4. Since each new record has an auto number assigned, I added the auto
number field to the query and used the auto number as the primary key. I
thought this would link the two but it didnt work.
What DOES show up.. when I look at the table view, is a drop down box that
lets me choose from the different totals that the query has added up.
I hope this makes some sense.
:
You've done it in a query, which is where it should be done, so base the form
on the query, not the table, if you want to see the totals in the form in
datasheet view. Whatever you do don't add a column to the table for storing
the totals; that would introduce redundancy and leave the table open to the
risk of update anomalies.
Ken Sheridan
Stafford, England
:
I have a form that I am inputting numbers into. the numbers automatically
show up in the right places when I switch to the table view. but I cannot
figure out how to make a field that totals up the numbers from several diff
fields and then shows them in the table view like the other numbers show up.
I made a query and it gives me the totals, but I cant get it into the Table
view. i hope someone can help!
