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
|
|||
|
|||
Designing Database with existing data
I am trying to plan for a database that allready contains repeating fields
and existing data. eg. Spec 1 Spec 2 Spec 3 RSpec1 RSpec2 MembID 1 4 15 16 20 001 1 3 17 20 19 002 There are 200 records in the members table. This is an extract for a table where membID is the key field. Each member can have many specializations but in a particular orders as you see but the same specialization can be shared by different members. This means we have a many to many relation, we need to convert it to 2 one to many relations. This is the key for what the specialisations mean: Code Description 0. Nothing 1. Accounting Profession 2. Accounting Theory 3. Financial Accounting and Reporting 4. International Accounting 5. Auditing 6. Finance Theory 7. Business Finance 8. Financial Markets 9. Information Systems 10. Computing 11. Education 12. Government, and Public Sector 13. Industries and specific organisations 14. Financial Institutions 15. Management Accounting 16. International Management Accounting 17. Methodology and Methods 18. Quantitative Methods and OR 19. Taxation 20. Other How do i account for 1NF, create a suitable design and link the text description with the codes?? |
#2
|
|||
|
|||
Designing Database with existing data
It appears that you have two tasks here -- splitting out the repeated
fields, and setting up a lookup query. Mani wrote: I am trying to plan for a database that allready contains repeating fields and existing data. eg. Spec 1 Spec 2 Spec 3 RSpec1 RSpec2 MembID 1 4 15 16 20 001 1 3 17 20 19 002 There are 200 records in the members table. This is an extract for a table where membID is the key field. Each member can have many specializations but in a particular orders as you see but the same specialization can be shared by different members. For the moment, I'll assume that all 5 specializations are of the same type. If not, you can do something like adding another field to distinguish them, but all of them appear to reference your table of specialization codes. What I might do is to set up a Table containing [MembID] -- such as 002 for the 2nd record in your example [Type] -- such as "Spec 3" for the 3rd field [code] -- such as 17 for [MembID]=002 and [Type]=Spec 3 I might well add an Autonumber field to these 3 fields to serve as a primary key (some database operations depend on having primary keys defined). This means we have a many to many relation, we need to convert it to 2 one to many relations. I'm not sure about this, didn't see a many to many relation, at least not in the normalized version of the Table. This is the key for what the specialisations mean: Code Description 0. Nothing 1. Accounting Profession 2. Accounting Theory 3. Financial Accounting and Reporting 4. International Accounting 5. Auditing 6. Finance Theory 7. Business Finance 8. Financial Markets 9. Information Systems 10. Computing 11. Education 12. Government, and Public Sector 13. Industries and specific organisations 14. Financial Institutions 15. Management Accounting 16. International Management Accounting 17. Methodology and Methods 18. Quantitative Methods and OR 19. Taxation 20. Other How do i account for 1NF, create a suitable design and link the text description with the codes?? Your Table of specializations is already in a good format -- the codes are unique. In your Queries, just link the [code] field of your Table of members ("many" side) to the [code] field of the specializations Table (this is the "one" side), and that should do it. If you do it cleverly enough, your Forms and Reports may be able to hide those raw code values, displaying just the verbal descriptions. (Unless, of course, your users need to see the numeric codes as well, such as for dealing with paper records.) -- Vincent Johns Please feel free to quote anything I say here. |
#3
|
|||
|
|||
Designing Database with existing data
If you are asking "how can I keep the existing table structure", the
short answer is: you don't. Convert it to a normalized structure using these three tables: tblMember: MembID PK autonumber FName text etc tblSpecialization SpecializationID PK autonumber Description text tblMemberSpecialization MembIDfk longInteger SpecializationIDfk longInteger SortOrder Integer This structure will allow each member to have several specializations, and for you to control the sorting order of each member's specializations. For extracting the data from your current table, I would run a series of append queries. Each one would obtain one specialization field from the existing table plus the memberID. It would be appended to the tblMemberSpecialization. You could also "force" a value for the sortOrder field during the append operations by entering a value of "10" for the first run of the append query, "20" for the second run, etc. Using widely-spaced SortOrder values allows you to make changes manually more easily in the future. On Sun, 13 Nov 2005 05:07:01 -0800, "Mani" wrote: I am trying to plan for a database that allready contains repeating fields and existing data. eg. Spec 1 Spec 2 Spec 3 RSpec1 RSpec2 MembID 1 4 15 16 20 001 1 3 17 20 19 002 There are 200 records in the members table. This is an extract for a table where membID is the key field. Each member can have many specializations but in a particular orders as you see but the same specialization can be shared by different members. This means we have a many to many relation, we need to convert it to 2 one to many relations. This is the key for what the specialisations mean: Code Description 0. Nothing 1. Accounting Profession 2. Accounting Theory 3. Financial Accounting and Reporting 4. International Accounting 5. Auditing 6. Finance Theory 7. Business Finance 8. Financial Markets 9. Information Systems 10. Computing 11. Education 12. Government, and Public Sector 13. Industries and specific organisations 14. Financial Institutions 15. Management Accounting 16. International Management Accounting 17. Methodology and Methods 18. Quantitative Methods and OR 19. Taxation 20. Other How do i account for 1NF, create a suitable design and link the text description with the codes?? ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
PST file has reached maximum size | Jeff C | General Discussion | 2 | October 6th, 2005 01:35 PM |
Unable to have multiple queries feeding a single report | PZ Straube | Setting Up & Running Reports | 15 | June 15th, 2005 08:16 AM |
Adding more source data to existing scatter plot | Tom | Charts and Charting | 1 | March 21st, 2005 10:03 PM |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |