Create table with field names based on the value of a field in another table.

[simon_minder]

Hi all

I need to dynamically create a table with field names
that are taken from values of a field in another table.
This table has a field called 'Benchmark Types', and each
value in this field becomes the name of a field in the
new table.

Regards

Simon

 

[Jeff Boyce]

Simon

While an Excel spreadsheet can only use multiple columns to show "types", it
is not considered good relational design to build tables whose fieldnames
are "types of" something, or are "repeating values" (for example, "January",
"February", ... -- these are repeating monthnames).

Could you describe your underlying business need? Perhaps the 'group's
readers could offer a different approach than the one you've decided you
need...

 

[Bob Hansel]

I seem to have the same problem. I am creating an access
to, among many other things, keep attendance of the
members of a civic service club and compute various
attendance percentages. I, of course, have a table that
includes the members names. I have created a new form
that uses the ActiveX Calendar control and allows the user
to input the dates of all club functions. This form
creates another table that has all events and the dates of
these events. Now I would like to create another new
table that has the members names in rows and the dates of
these events in columns. I can from that table create a
form with all of the members names and all of the events
and the user can just check those events each member
attended.

So, I am asking the same question. How can I create a
table that uses data from another table as the names of
the fields?

Bob

 

[Jeff Boyce]

Bob

My point is, I believe, to NOT use "type" or repeating values (i.e.,
data/row values from one table) as the field names of another.

If you are saying that you have person, event, and person-at-event data, use
three tables. Isn't this a little like a hotel reservation, where you have
person, room and person-reserving-room? That third table only contains the
personID, the eventID, and any info specific to person-at-event. Note that
this would NOT include date-of-event, as that is a characteristic of the
event. Note that person address or phone number is NOT part of
person-at-event, as these are characteristics of a person.

But with a table structure like this, you can retrieve the address of a
person at a specific event by joining the tables in a query.

And you can use the "unmatched" query wizard to help you get a list of folks
who were NOT at a particular event.

Also note - you would NOT need to store a person_NOT-at-event record -- this
can be derived from the unmatched query mentioned above.

 

[simon_minder]

Hi Jeff

I will try to explain my problem with an example:

I have a table A and a table B in Microsoft Access 2003.

In table A I would like to store Benchmark ID's (e. g.
Benchmark Name 1, Benchmark Name 2, etc.). I have
designed a form where the end users can enter additional
Benchmark Names (ID's).

In table B I would like to store procentages per
Benchmark (colum titel) and Portfolio (row titel). For
this screen I will design a form as well (or may include
it in the frist on).

Summary
=======

The user should be able to add a Benchmark ID (witch
should be added on table A) and enter a procentage for
the new Benchmark to the current and new Portfolios
(table B).

If you have any further questions please feel free to
contact me.

Regards

Simon

 

[Jeff Boyce]

Simon

I believe I understand what you've described ... and I'm still suggesting
that this is not a good use of the capabilities of Access.

The "row & column" orientation is necessary in spreadsheets, but not in a
relational database.

I'll suggest checking on "normalization" for more ideas, but here's one...

You could create a table that stores, as fields, BenchmarkID, PortfolioID,
and "raw value", and have Access compute "percentages".