dynamic calculated field in query

[tbmarlie]

Ok, I've been spinning my wheels. I thought this would be easier.

I'm creating a Make Table query from an existing table where I am
adding one field, "Completed", in the new table
The value that will go into this field will be the sum of the values
any field that has "Course" in the name of the field.
The values in the course fields are either going to be 0 or 1.
For example, Lets say I have the following course fields with their
corresponding values:

Field Name Value
Course1 1
Course2 1
Course3 0

So, in my table that I'm making the Field named "Completed" will have
a value of 2.

This is very easy query to create if I know that I'm always going to
have 3 courses.

My problem, though, is in creating the query to be dynamic enough so
that the Completed field will sum the course field values whether I
have 3 courses or 5 courses or 20 courses.

Thanks for any assistance.

 

[Bob Barrows]

Ok, I've been spinning my wheels. I thought this would be easier.

I'm creating a Make Table query from an existing table where I am
adding one field, "Completed", in the new table
The value that will go into this field will be the sum of the values
any field that has "Course" in the name of the field.
The values in the course fields are either going to be 0 or 1.
For example, Lets say I have the following course fields with their
corresponding values:

Field Name Value
Course1 1
Course2 1
Course3 0

Wait, is the design of the table? Or is it a table with two fields, "Field
Name" and "Value", whose rows contain the data you present? I'm going to
assume the latter. If you want quicker help next time, it will be better to
be explicit ab out your table name, field names etc. and when you present
sample data, present it in tabular for such as you would see in a datasheet
view in Access. That may be what you've done here, but I'm not sure, so I
might be wasting my time with my answer below.

So, in my table that I'm making the Field named "Completed" will have
a value of 2.

This is very easy query to create if I know that I'm always going to
have 3 courses.

My problem, though, is in creating the query to be dynamic enough so
that the Completed field will sum the course field values whether I
have 3 courses or 5 courses or 20 courses.

Thanks for any assistance.

First off, you should not be making a new table. You'l have to empty it and
refill it every time the data changes. Not good. Just create a saved query
that calculates the value.

The solution, given my assumption is correct, is a grouping query. I don't
know what other fields are involved, but the value you want to compute can
only be computed by aggregating.

SELECT SUM(Iif([Field Name] LIKE '*Course*' And [Value] = 1, 1, 0) As
Completed from table

 

[John W. Vinson]

Ok, I've been spinning my wheels. I thought this would be easier.

I'm creating a Make Table query from an existing table where I am
adding one field, "Completed", in the new table
The value that will go into this field will be the sum of the values
any field that has "Course" in the name of the field.
The values in the course fields are either going to be 0 or 1.
For example, Lets say I have the following course fields with their
corresponding values:

Field Name Value
Course1 1
Course2 1
Course3 0

So, in my table that I'm making the Field named "Completed" will have
a value of 2.

This is very easy query to create if I know that I'm always going to
have 3 courses.

My problem, though, is in creating the query to be dynamic enough so
that the Completed field will sum the course field values whether I
have 3 courses or 5 courses or 20 courses.

Thanks for any assistance.

The field [Completed] should simply NOT EXIST in any table. It's derived data.
If you have that value stored, it will be WRONG as soon as any course value is
changed.

Instead, it should be dynamically calculated on the fly in a query.

An additional issue is that you are "committing spreadsheet", if (as it
appears) you have one FIELD for each course. A properly normalized design for
a course-registration database has THREE tables:

Students
StudentID <primary key>
LastName
FirstName
<other biographical data>

Courses
CourseNo <primary key>
CourseName
<other info about the course as a thing in its own right>

Enrollment
EnrollmentID <autonumber primary key>
StudentID <who enrolled in the course>
CourseNo <which course is this student enrolled in>
<other info about THIS student's enrollment in THIS course, e.g. completion,
withdrawal, grade, etc.>

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

Ok, I've been spinning my wheels. I thought this would be easier.

I'm creating a Make Table query from an existing table where I am
adding one field, "Completed", in the new table
The value that will go into this field will be the sum of the values
any field that has "Course" in the name of the field.
The values in the course fields are either going to be 0 or 1.
For example, Lets say I have the following course fields with their
corresponding values:

Field Name Value
Course1 1
Course2 1
Course3 0

So, in my table that I'm making the Field named "Completed" will have
a value of 2.

This is very easy query to create if I know that I'm always going to
have 3 courses.

My problem, though, is in creating the query to be dynamic enough so
that the Completed field will sum the course field values whether I
have 3 courses or 5 courses or 20 courses.

Thanks for any assistance.

If, as John speculated, you are "committing spreadsheet", then you do have
more serious problems, as he says. However, I don't understand this
particular problem you are having. It should be a simple
SELECT <key field(s)>, [Course 1] + [Course 2] + ... {Course 20] AS
Completed
FROM table
What happens when you try that? Is the problem that some fields might
contain Nulls? If so, you need the use Nz for each field (which is another
problem with the "spreadsheet" design):
SELECT <key field(s)>, Nz([Course 1],0) + ...

The problem of course comes when new fields are added/removed due to there
being more/fewer courses, which is the real problem you are having due to
your "spreadsheet" design. The only way to make this dynamic in this
situation is to create a VBA subroutine that loops through the fields in the
table, generating a dynamic sql statement that it then assigns to the SQL
property of your querydef object. Of course, this approach has its own
issues, one of which being database bloat due to the retention of old
versions of the sql statement in system tables.

If you are indeed describing a "spreadsheet" design, I advise you to read
John's post as many times as it takes for you to understand the design he is
describing. And then redesign your database along the lines he advises.