Data from Un-Normalised DB to Normalised DB

[Loggical via AccessMonster.com]

I have just started to create a normalised DB and need help in transferring
data from un-normalised DB; 10,000 + records in the un-normalised DB.

UN-Normalised DB
tblOccurrence with the following fields.

OccurrenceID
Date
Time
Details
EmployeeNumber1
Name1
EmployeeNumber2
Name2
EmployeeNumber3
Name3
ReportType1
Report1
ReportType2
Report2

How would I transfer all the data into a normalized DB given there are
multiple fields for Names and Reports? In the normalised DB I have the
following Tables where the data needs to be transferred to.

tblOccurrence
tblOccurrenceEmployee
tbleReportTypeUsed

These three tables are used in the frmOccurrence with the tblOccurrence being
the main table and the tblOccurrencReport and tblReportTypeUsed being
subforms. All three tables have Primary Keys and Enforce Referential
Integrity.
Any help would be grateful.

 

[Damian S]

Hi,

Do it one step at a time. First, use queries to extract Employees into a
table ( you may need to add an employee table).

Then, run another query to extract occurrences.

Then, run other queries to populate the OccurrenceEmployee table based on
EmployeeNumber1, then EmployeeNumber2, then EmployeeNumber3.

Lots of queries, each extracting a piece/set of data you need is the way to
proceed.

Does this makes sense to you?

Damian.

 

[John Vinson]

I have just started to create a normalised DB and need help in transferring
data from un-normalised DB; 10,000 + records in the un-normalised DB.

Good on ya! Glad to see someone come over from the Dark Side.

UN-Normalised DB
tblOccurrence with the following fields.

OccurrenceID
Date
Time
Details
EmployeeNumber1
Name1
EmployeeNumber2
Name2
EmployeeNumber3
Name3
ReportType1
Report1
ReportType2
Report2

How would I transfer all the data into a normalized DB given there are
multiple fields for Names and Reports? In the normalised DB I have the
following Tables where the data needs to be transferred to.

tblOccurrence
tblOccurrenceEmployee
tbleReportTypeUsed

These three tables are used in the frmOccurrence with the tblOccurrence being
the main table and the tblOccurrencReport and tblReportTypeUsed being
subforms. All three tables have Primary Keys and Enforce Referential
Integrity.
Any help would be grateful.

Are the EmployeeNumber and Name fields redundant - i.e. if
EmployeeNumber1 is 415 and Name1 is "Joe Baltz", is 415 *always*
associated with Joe? You'll need SOME sort of normalizing UNION
queries. Just at a guess:

qryOccurances
SELECT OccuranceID, [Date] + [Time] AS OccuranceDateTime, Details
FROM OLD_tblOccurance
INTO tblOccurance;

(note combining the Date and Time fields into a single date/time
field)

uniEmployees
SELECT EmployeeNumber1, Name1
FROM OLD_tblOccurance
WHERE EmployeeNumber1 IS NOT NULL
OR Name1 IS NOT NULL
UNION
SELECT EmployeeNumber2, Name2
FROM OLD_tblOccurance
WHERE EmployeeNumber2 IS NOT NULL
OR Name2 IS NOT NULL
UNION
SELECT EmployeeNumber3, Name3
FROM OLD_tblOccurance
WHERE EmployeeNumber3 IS NOT NULL
OR Name3 IS NOT NULL

This UNION query will remove duplicates (though if 415 is both Joe
Baltz and Alicia Fernandez, you'll get 415 in the result twice);
you'ld then run

INSERT INTO Employees (EmployeeNumber, EmployeeName)
SELECT EmployeeNumber1, Name1
FROM uniEmployees;

Finally you'ld populate the other table (or tables) in the same way.
It's not at all clear to me how the ReportType relates to the Employee
- do all three (well, one to three) employees get associated with each
report?

John W. Vinson[MVP]

 

[Loggical via AccessMonster.com]

John, you talk about Dark Side, it’s so dark here it’s creepy.

The relationship concerning ReportType: is related to all employees
associated with that particular occurrence. OccurrenceID and ReportTypeID. I
can have only one occurrence with multiple EmployeeNumbers and multiple
ReportTypeUsed (I can have reports from different departments and each
department has its own ReportType abbreviation, (SMS IR, HDR IR).
The actual table is like the following:
tblReportTypeUsed
ReportTypeID
ReportType
ReportNumber

Is there a reason behind joining the date and time? My reason for having them
separated is I personally find it a lot easier to input the data into two
separate fields. Is this a good idea or a bad idea?

John yes the EmployeeNumber is redundant, (EmployeeNumber is always
associated with the same employee). But with every occurrence the data
entered into the EmployeeNumber field and Name1 field would change.

If by using the Unioun Query you are suggesting, will I still be able to
associate every Employee with a particular occurrence? Given that there are
multiple names associated with a particular occurrence. If I had six
Employees associated with a particular occurrence they would all need to have
the same OccurrenceEmployeeID to work in the Normalised DB.
Do I need to turn off Enforce Referential Integrity before running the Union
Query?

I have just started to create a normalised DB and need help in transferring
data from un-normalised DB; 10,000 + records in the un-normalised DB.

Good on ya! Glad to see someone come over from the Dark Side.

UN-Normalised DB
tblOccurrence with the following fields.

[quoted text clipped - 27 lines]

Integrity.
Any help would be grateful.

Are the EmployeeNumber and Name fields redundant - i.e. if
EmployeeNumber1 is 415 and Name1 is "Joe Baltz", is 415 *always*
associated with Joe? You'll need SOME sort of normalizing UNION
queries. Just at a guess:

qryOccurances
SELECT OccuranceID, [Date] + [Time] AS OccuranceDateTime, Details
FROM OLD_tblOccurance
INTO tblOccurance;

(note combining the Date and Time fields into a single date/time
field)

uniEmployees
SELECT EmployeeNumber1, Name1
FROM OLD_tblOccurance
WHERE EmployeeNumber1 IS NOT NULL
OR Name1 IS NOT NULL
UNION
SELECT EmployeeNumber2, Name2
FROM OLD_tblOccurance
WHERE EmployeeNumber2 IS NOT NULL
OR Name2 IS NOT NULL
UNION
SELECT EmployeeNumber3, Name3
FROM OLD_tblOccurance
WHERE EmployeeNumber3 IS NOT NULL
OR Name3 IS NOT NULL

This UNION query will remove duplicates (though if 415 is both Joe
Baltz and Alicia Fernandez, you'll get 415 in the result twice);
you'ld then run

INSERT INTO Employees (EmployeeNumber, EmployeeName)
SELECT EmployeeNumber1, Name1
FROM uniEmployees;

Finally you'ld populate the other table (or tables) in the same way.
It's not at all clear to me how the ReportType relates to the Employee
- do all three (well, one to three) employees get associated with each
report?

John W. Vinson[MVP]

 

[Loggical via AccessMonster.com]

Damian it does make sense, but making sense and actually for me to get it to
work is two different things. Thanks for your help.

Hi,

Do it one step at a time. First, use queries to extract Employees into a
table ( you may need to add an employee table).

Then, run another query to extract occurrences.

Then, run other queries to populate the OccurrenceEmployee table based on
EmployeeNumber1, then EmployeeNumber2, then EmployeeNumber3.

Lots of queries, each extracting a piece/set of data you need is the way to
proceed.

Does this makes sense to you?

Damian.

I have just started to create a normalised DB and need help in transferring
data from un-normalised DB; 10,000 + records in the un-normalised DB.

[quoted text clipped - 30 lines]

Integrity.
Any help would be grateful.