How do I total entries for a row

[karle]

Here is my problem:

I am keeping track of the number of Quality Assurance and Quality Control
reviews for multiple projects. There are 35 different parameters that get
checked for each project.
I have five different tables:

1 for Project Information which contains the Project Engineer's name,
contact information, project number (unique id), project description etc.

1 for QC reviews planned which contains the project number and the 35
parameters mentioned (36 columns total). The project engineer would input
how many reviews are to be performed on each parameter for his job. The
project numbers can not be duplicated in this table.

1 for QC reviews performed which contains the project number and the 35
parameters in which the date the review was performed is entered. In this
table project numbers can be duplicated to allow for multiple review dates.

The other 2 tables are set up exactly like the QC tables only for QA.

I need to sum the QC/QA reviews planned for each project (row) as well as
count the dates reviews were performed for each project (which could be
multiple rows due to the multiple review dates for each parameter). Then I
need to calculate the percentage of planned/reviewed to determine how well
the project engineer is following our QC/QA Plan.

Any help/advise/direction would be greatly appreciated.

 

[Golfinray]

I is difficult to total a row (record) because access is made to be
horizontal, that is total on fields (columns.) I would try turning those rows
in a crosstab query and see if you can total then. It is possible that you
might have to have a separate query to total each row or rearrange your table.

 

[karle]

Thanks for the input. However, crosstab queries only allow 3 fields to be
used for the row headings. I have 35. The projects show up as column
headings (which is right) but the summation of QC planned is incorrect for
the 3 fields. I did not even try for QC Reviewed which has multiple rows
with dates.

I'll admit I'm new to ACCESS but I can see the potential of the program if I
can get it to do what I want it to.

 

[scubadiver]

You seem to have a reasonable understanding of normalisation. Instead of
having a table with 35 columns, you need to have a separate table with 35
records then you can append the parameters to each project (then you could
compare projects for an individual parameter if you wished).

 

[karle]

I'll admit as I have said before I am new to ACCESS. I have a simple
understanding of tables, forms and reports (probably the easiest) but queries
and code are giving me fits.

I think it seems like a reasonable solution, although I am not sure about
how to append. However, when reviews are performed a new record would need
to be created for the additional date. I know I have not explained what I am
trying to accomplish very well, and that makes it hard to come up with a
solution, so here goes.

In the QC or QA Review Planned tables there are 36 columns. One field is for
the Client ID Number (key, can not be duplicated) and the other 35 are for
each individual parameter that might or might not be reviewed. It depends on
whether that action is taking place on the project or not. In these tables 1
parameter might have the value of 3 and a different parameter might be 1. I
want to add up the values for each parameter for each unique Client ID Number.

In the QC or QA Reviewed tables there are 36 columns. One field is for the
Client ID Number (key, can be duplicated) and the other 35 are for each
individual parameter that might or might not need to be reviewed. If a
parameter has a 3 in the Reviewed Planned table then there should be 3 dates
for that parameter for the Client ID Number (3 records would be created). I
have it set up in forms so it will not create a new record unless there is a
date already entered. I know it creates a waste of space, but that is how I
have it set up now. Once I get more proficient with ACCESS I will change it.
I want to count the dates for each Client ID Number (which could be in
multiple records). If it could not be done at one time then maybe a subtotal
(each record) then a total. Then I would compare the total planned with
total reviewed.

I apologize for the long post. I thought there might be an
expression/equation that I could enter into a 37th column/field that would do
this for me and then all I would need to do is show the 37th field on a
report.

 

[John Spencer]

As indicated elsewhere you have a design problem. You can use VBA to get the
sum. If you paste the attached function into a VBA module and save the module
with a name OTHER THAN fRowSum, you should be able to do something like the
following (as long as you don't exceed the length limit for the "cell".

Field: TheTotal:
fRowSum(Field1,Field2,Field3,...Field27,fRowSum(Field28,...Field35))

The nesting is needed because you have more than 29 fields to add.

The other VBA solution would be to write a custom function (or functions) that
would use a primary key field to get the appropriate record and then in the
function would step through the appropriate fields and sum them.

------------------------- Code Below --------------------------------

Public Function fRowSum(ParamArray Values()) As Variant
'====================================================================
' Procedure : fRowSum
' Created : 12/6/2006
' Author : John Spencer
' Purpose : Sum a group of numbers passed in,
' handles numbers and text strings that are all numeric
' or that can be interpreted as numbers (1e3 is 1000)
'
' Max of 29 arguments can be passed to a function in Access SQL
' workaround is to nest fRowSum or to add multiple calls together.
'====================================================================

Dim i As Integer, dSum As Variant

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dSum = dSum + Val(Values(i))
End If
Next i

fRowSum = dSum
End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County