Simple query, multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Alright, so I have been scouring the newsgroups trying to
find a solution to my problem, but it seems to escape me.
Here is what I have. There are two tables and I want to
report in a query a different value (from the second
table) than the one stored (in table one.)

TABLE1:
Vol01
Vol02
Vol03
Vol04
....and so forth, all the way to Vol40

In this field is stored a number (such as "14") which is
placed there when a date is selected from a combo box in a
form. The number references the field NightNum in Table2.

TABLE2:
NightNum
Day
Date
Points

What I need to do is create a query that will return the
points value for all 40 Vol## fields. This way I can list
them out, total them, etc.

I can make the points appear on a form (hide other columns
in a combo box), but because they are not stored, I can't
seem to make them calculate.

Any help short of creating 40 individual queries? Or if a
query isn't the way to go, any other ideas. I am at a
loss.

Thanks.
-paige
 
The problem lies in the fact that you have 40 columns doing the work of just
two.

Restructor table one to have just Vol# and VolAmt, and the query builder
will be your friend again.
 
I am not sure if that will solve my problem or not... you
see, each Volunteer may have multiple dates that they are
volunteering. We want to store each date, which is why I
have made as an option up to 40 fields.

And, since each date can have a different "point" value,
that is why there's a separate table for that
information. What I am saving is the NightNum key from
the dates table.

Maybe the solution you are suggesting works, but I don't
know how when there are potentially many references to the
dates table for each Volunteer.

I'm sure I've completely confused you... then again, I'm
probably the one who needs clarificaiton! :)

Thanks.
-paige
 
Back
Top