Designing Database with existing data

[Mani]

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

 

[Vincent Johns]

It appears that you have two tasks here -- splitting out the repeated
fields, and setting up a lookup query.

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

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

[QUOTE]
This means we have a many to many relation, we need to convert it to 2 one
to many relations.[/QUOTE]

I'm not sure about this, didn't see a many to many relation, at least
not in the normalized version of the Table.
[QUOTE]
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??[/QUOTE]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Jack MacDonald]

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.

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

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security