variable name question

  • Thread starter Thread starter Eliezer
  • Start date Start date
E

Eliezer

I have a bunch of tables with fields named something to the
effect of ABC1, ABC2, ABC3, etc.. I have to add the values
of all those together. The way it is currently being done
is basically


Dim rst As New ADODB.Recordset

TOTAL = TOTAL + rst!ABC1
TOTAL = TOTAL + rst!ABC2
....
TOTAL = TOTAL + rst!ABC46

rst!TOTAL = TOTAL


I was wondering if I could use a for loop, as such:


Dim CURRREC as String

For n = 1 To 46 Step 1
CURRREC = "ABC" + n
TOTAL = TOTAL + rst!CURRREC
Next n


(please correct me if my syntax is incorrect; I'm not
trained to be a VBA programmer.) Would this idea work? I
would think not; it seems that it would look for CURRREC ni
the table. How would I get it to recognize that CURRREC is
a variable? Thanks!
 
Unfortunately, your database design is causing this problem. You should
NEVER have fields like ABC1, ABC2, etc. To handle these, you should have a
related table where each value in in the same field (ABC) but in separate
RECORDS. This will allow the database to do a sum with a simple Sum()
function.

However, short of re-designing your database, you CAN use variables in your
field names using a slightly different syntax:
For n = 1 To 46 Step 1
TOTAL = TOTAL + rst("ABC" & n)
Next n

On my website, see sig below, is a small sample database called
NormalizeDenormalize.mdb which illustrates this AND how you can fix your
database to a normalized structure that won't require this sort of
programming.
 
Hello Roger -

Thanks! Just a syntax question; did you mean
TOTAL = TOTAL + rst("ABC" & n)
or
TOTAL = TOTAL + rst!("ABC" & n)
? Thanks again... this will help me a lot.

I'm not sure I understand why having fields such as that
are a problem, and I'm truly confused as to what effect
normalizing will have on the data. This is a table for a
survey, so let's use that as an example. Let's say person
one fills out the survey. So, Record1 has within it the
answers for fields survey1, survey2, and so on. Now I give
it to person 2, making a Record2 with survey1, survey2, and
so on. To me, this seems like an intelligent method of
storing this data; simple, intuitive, and it nicely exports
to other programs (SPSS, Excel, etc.) for analysis. What do
you propose is a better way?
 
TOTAL = TOTAL + rst("ABC" & n) 'no bang

As for the multiple field question, let me ask this: How many surveys are
there? 1, 2, 12, 85? How many fields do you need to create? If you create
12 and a new survey is added, you have to go back and modify your table
structure. Then you have to go back and modify your queries, table, reports
and so forth to compensate. On the other hand, do you create 100 survey
fields and leave most of them empty? On your form, have room on your form
for 100 fields when most of them will never be used?

Your design should allow you to add surveys without modifying the table
structure or have mostly empty fields. The way to do this is to store this
information in separate records in a related table. Then it is just a
matter of adding more *rows*, which the database is built to do without
modifying the table. Row are cheap and columns are expensive in database
terms.

Furthermore, this design allows you to use the functions built into and SQL
database engine to manipulate the data. What you are doing is
"spreadsheet-thinking" rather than "relational-thinking". It can be done,
but it is not using the tool properly or to it's best effect. It requires
you to use code (as the loop you suggest) to analyze the data rather than
the native tools like the Sum function built into SQL.

By and large, the rules of normalization exist because the WORK. As a
general rule it is wise to follow them, though we all break them
occassionally for specific purposes. But it better be a darn good reason
and then I only do it with fear and trembling.
 
Yours is a fairly standard (and quite respectable) method of saving
questionnaire results (I assume that is what you mean by "survey" -
you're not preparing to build an new highway!)

If your survey always and only has yes/no questions (and answers!),
there is an argument for keeping just the responses in a single table
with pointers to one table identifying the question number (and
possibly its text) and a second table identifying the questionnaire
(name of respondent or serial # of questionnaire).

The skeleton of the setup would look something like this:

tblQuestions
PriKeyQ Primary Key
SetNumber Identifier of questionnaire (set of questions)
QuestionText What it says

tblRespondents
PriKeyR Primary Key
RespondentInfo What it says

tblAnswers
PriKeyA Primary Key
QuestionPointer Link to PriKeyQ
RespondentPointer Link to PriKeyR
Response What it says

This sort of structure would facilitate the efficient use of quite
simple queries to ask complex questions about how people responded to
the survey. You would also use queries to convert the responses back
into a flat table for export to SPSS, etc.


Hello Roger -

Thanks! Just a syntax question; did you mean
TOTAL = TOTAL + rst("ABC" & n)
or
TOTAL = TOTAL + rst!("ABC" & n)
? Thanks again... this will help me a lot.

I'm not sure I understand why having fields such as that
are a problem, and I'm truly confused as to what effect
normalizing will have on the data. This is a table for a
survey, so let's use that as an example. Let's say person
one fills out the survey. So, Record1 has within it the
answers for fields survey1, survey2, and so on. Now I give
it to person 2, making a Record2 with survey1, survey2, and
so on. To me, this seems like an intelligent method of
storing this data; simple, intuitive, and it nicely exports
to other programs (SPSS, Excel, etc.) for analysis. What do
you propose is a better way?
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Well. Wow. Thanks for the sysnopsis on intelligent
database design. I am not the person in this department
who is in charge of creating databases, I'm just the one
to suffer the consequences when things are done less
efficiently. However, I doubt that the person creating the
databses has extensive training, based on other
observations. While I doubt you are saying anything that
should be too novel, it is to me, and I thank you for
that. I'll forward the link of this conversation to them.

Thanks a bunch for your help, both of you!
 
Back
Top