A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2004, 03:49 PM
external usenet poster
 
Posts: n/a
Default Create table with field names based on the value of a field in another table.

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
  #2  
Old May 20th, 2004, 02:37 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default Create table with field names based on the value of a field in another table.

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...

--
Good luck

Jeff Boyce
Access MVP

  #3  
Old May 21st, 2004, 03:23 PM
Bob Hansel
external usenet poster
 
Posts: n/a
Default Create table with field names based on the value of a field in another table.

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
  #4  
Old May 22nd, 2004, 02:44 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default Create table with field names based on the value of a field in another table.

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.

--
Good luck

Jeff Boyce
Access MVP

  #5  
Old May 24th, 2004, 01:55 PM
external usenet poster
 
Posts: n/a
Default Create table with field names based on the value of a field in another table.

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




-----Original Message-----
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...

--
Good luck

Jeff Boyce
Access MVP

.

  #6  
Old May 24th, 2004, 02:32 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default Create table with field names based on the value of a field in another table.

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".

--
Good luck

Jeff Boyce
Access MVP

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.