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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|