Calculating the sum of all fields in a row

  • Thread starter Thread starter Spidey3721
  • Start date Start date
S

Spidey3721

Newbie here - Wondering what the easiest way is:

I have a table with about 30 columns, all numbers - I set up a form where I
would like to have a control (text box) showing the sum of all these
numbers.

Do I have to add all of these up with the "+" operator or can I somehow use
a SUM function ?

Thank you for the help
 
If you are trying to add up 30 different fields (columns,
yes you would have to use the + to do it. You can use Sum
to add up all of the records in one field.

If you are needing to do this, I suspect that your design
may not be optimized but hard to tell with the info
supplied.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Thank you for the help - Maybe I am approaching it wrong.

I have the columns set up as different construction costs, such as roofing,
doors, drywall, plumbing, etc...

Then I have each row as a different job/project.

I have a form where I want to show, in a text box, the TOTAL job cost, which
would be all of the different categories (the columns, or fields) added
together.
 
If that is the case, a little redesign could help you
immensely down the road. This may be very simplistic as
there is usually more involved in this type of thing.

A better approach would be to have a table that holds the
project and the spefic info relevant to the project as a
whole. Each project has a unique ID. Then you would have a
table holding you job categories, roofing, doors, drywall,
etc. so that you could use this to populate a list to choose
from. Each category would have a unique ID. Next you would
have a job line item table that would hold the ProjectID,
the WorkCategoryID.

Now you would probably create an entry system where you go
to a Project form that would have a subform that would allow
you to add your cost line items that the project requires as
you need. If there is only one, you only put one record in
the subform. If there are 1,000 then you would put 1,000
records in the subform. No matter how many you have you can
now use Sum() to total them at will.

tblProjects
ProjectID - Autonumber - Primary Key
ProjectName - Text
ProjectOwner - Text
etc...

tblJobCategories
JobCategoryID - Autonumber - Primary Key
JobCategory - Text

tblProjectJobs
ProjectJobID - Autonumber - Primary Key
ProjectFK - LongInteger - ForeignKey to Project table
JobCategoryID - LongInteger - ForeignKey to JobCategory
table
Amount - The charge for this job section

I would also wonder if you would want to subdivide another
level to divide labor from materials. As I said this is a
very simple model.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
I think that I understand where you are going, but I'm still a little slow
to catch on. I grasp the idea of having a table for projects, with two
columns (fields?) - one being the ProjectID and the other being the name of
the project. Then I figure I may add a few others such as address, Start
date, etc for use in other forms.



Then I think I grasp the idea of the MASTER category table. I'm assuming
that this would be a master table with all possible cost categories
included.



Beyond that, I would make the table where the actual costs are stored - the
one you show as the tblProjectJobs - I do not understand the idea of the
Foreign key..



Furthermore - I am having trouble when I try to set up a form with the
subform that shows the cost inputs. If I base the subform on a query that
includes these tables I am having trouble being able to edit/add - I guess I
need to study up on the type of relationships.



Anyways - I thank you for this help - I think I am getting there.
 
The Primary/Foreign key concept is very central to good
'Relational' database design.

In your case for every project that you have it should have
a unique ProjectID that would be the Primary Key of the
Projects table. Now in the table that you have for the jobs
to be done for each project, if you have a field that will
store the ProjectID in that table, any jobs will now be
related to the Project they belong to by that ProjectID in
the jobs table. The JobID is the PrimaryKey for the Jobs
table and the ProjectID is the foreign key to the Projects
table.

When you have a properly set up form and subform, the main
Project form will fill in the ProjectID into the Jobs
subform automatically for you as you enter new jobs for the
project. When you open a certain project, it will then
display all of the related Jobs in the subform.

Clear as mud?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Okay - Thanks for the explanation - I have it all setup - I think I am on my
way - the only thing is:

in my subform - I have it based on a query that includes all neccessary
fields from these tables. I am finding that I have to include the ProjectID
in the the costcategory subform in order for the main form to be able to
tell the subform which cost line items to show, but is there a way to hide
this field from the subform so that the end user does not have to see a
meaningless autonumber ? Right now, I am setting the VISIBLE property of
that textbox (in the subform) to NO, but it is still showing up...
 
Actually, it just needs to be in the query and not actually
on the form. I don't understand why Visible = No is not
hiding it though. It should.

Glad to hear that you have things working now.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
I am actually noticing a few qwerky things like that - bugs, if you will.

One thing that I have been curious about - I have Access 2002, but when my
files are open, the file name in the main database window always says:

FILENAME : Database (Access 2000 file format)

Any idea what that is all about ?
 
Spidey3721 said:
I am actually noticing a few qwerky things like that - bugs, if you will.

One thing that I have been curious about - I have Access 2002, but when my
files are open, the file name in the main database window always says:

FILENAME : Database (Access 2000 file format)

Any idea what that is all about ?
Click to expand...

Access 2002 uses the 2000 file format by default.
 
Okay - Thanks for the explanation - I have it all setup - I think I am on my
way - the only thing is:

in my subform - I have it based on a query that includes all neccessary
fields from these tables. I am finding that I have to include the ProjectID
in the the costcategory subform in order for the main form to be able to
tell the subform which cost line items to show, but is there a way to hide
this field from the subform so that the end user does not have to see a
meaningless autonumber ? Right now, I am setting the VISIBLE property of
that textbox (in the subform) to NO, but it is still showing up...
Click to expand...

Is the subform in Datasheet mode, or Continuous Form view? I'd suggest
the latter...

Elsewhere in the thread: Access2002 can use either A2000 or A2002
format, and defaults to the former. Almost anything you want to do can
be done in either format database (an exception is creation of a .mde
file; you must convert to 2002 format to do so).
 
Back
Top