Retrieving fieldnames stored in one table and use them in another

[Guest]

Greetings all,

I am trying to accomplish a dynamic top ten reporting system. The data will
reside in a table tblEvaluation which will hold record ID, company ID, date,
and thirty-two checkbox fields. I have designed a query to pull only the
fields that will be tracked from tblEvaluation, that are checkboxes, and have
them populate in a combo box in a Top Ten Selection form which is bound to
tblTopTen table. The tblTopTen will consist of TopTenID, TopTen1,
TopTen2,…,TopTen10. My goal now is to create multiple queries to pull the
user selected Top Ten fields from tblTopTen and use them against the
tblEvaluation table.

How can I accomplish this task?

 

[Duane Hookom]

Consider normalizing your table structure. There is a sample of how this
can/should be done at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Thank you for the reply Duane. I have viewed the 'Employee Evaluation'
program thinking this is what you are asking me to review to normalize my
tables.

First, I would like to make sure that I have explained this correctly. I
would like to store ten field named in a table (tblTopTen) that can be used
in queries for reports and charts. I will than pull the field names stored
as data in tblTopTen and use them against tblEvaluation. Example:
tblTopTen.TopTen1 has a value of eval01 which is a field name in the table
tblEvaluation (tblEvaluation.eval1).

Are we on the same thought process?

 

[Duane Hookom]

If you have fields with names like Eval1, Eval2,... then we are not on the
same thought process.
Perhaps you need to provide some sample data like what are the actual values
stored in tblTopTen?


Duane Hookom
MS Access MVP

 

[Guest]

I will attempt to display the two tables below, tblEvaluation and tblTopTen.
There is no relation to the tblEvaluation and tblTopTen. TblTopTen is only
used to hold the top ten fieldnames from tblEvaluation table. My thought is,
I would like to dynamically create a report/chart from the data in
tblEvaluation. Maybe, I have not designed this correctly. Hopefully, I have
expalined this a little clearer.

tblEvaluation:
EvaluationID CompanyID Date eval1
1 1 1/1/2006 0
2 1 1/15/2006 0
3 4 1/18/2006 -1
4 2 2/2/2006 0
5 3 2/10/2006 0
6 1 2/11/2006 -1
7 4 2/22/2006 0
8 2 3/15/2006 0
9 3 4/1/2006 0

eval2 eval3 eval4 eval5
0 0 0 -1
0 0 0 0
0 0 0 -1
0 0 0 0
0 0 -1 0
-1 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0

eval6 eval7 eval8 eval9
-1 0 0 -1
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 -1
0 0 0 0
0 -1 0 0
0 0 0 0
-1 0 0 0

eval10 eval11 eval12 eval13
0 0 0 0
0 0 0 0
-1 0 0 -1
0 0 -1 0
0 0 0 0
0 -1 0 0
0 0 0 0
0 0 0 0
0 -1 0 0

eval14 eval15 eval16 eval17
-1 0 0 -1
0 0 -1 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 -1
-1 0 0 0
0 -1 0 0
0 0 -1 0

eval18 eval19 eval20
0 0 0
0 -1 -1
-1 0 0
0 0 0
0 0 -1
-1 -1 0
0 0 0
0 0 -1
0 0 0


tblTopTen:
TopTenID TopTen1 TopTen2 TopTen3
1 eval5 eval7 eval8

TopTen4 TopTen5 TopTen6 TopTen7
eval9 eval12 eval13 eval16

TopTen8 TopTen9 TopTen10
eval18 eval19 eval20

 

[Duane Hookom]

I think you table structure is wrong. There were two applications/demos from
the link I sent earlier that demonstrate a more flexible solution. Both the
employee evaluation and "At Your Survey" should provide some direction.

I'm wouldn't go any further with your spreadsheet approach.

Duane Hookom
MS Access MVP