Normalization

[Eric]

I have a Word template that exports into Access. The form fields from Word
are several memo fields. The fields have to be bookmarked with a unique name
so they can export to the corresponding fields in Access. In order to
accomplish the bookmark's unique name, In Word, I have several common fields
identified with a number, here is an example:

Observation1
Recomendation1
Observation2
Recommendation2 ... etc.

This works great for the word template and exporting them over to a
“Catch_all_Table†in Access.

I then append from the Catch_all_Table out to the other tables accordingly
and remove the records from the Catch_all_Table. This all works well.

I want to normalize the other tables when I run the append query. I want
the new tables to only have the two needed fields, (Observations and
Recommendations) instead of 20 Observation fields and 20 Recommendation
fields.

So when I append, all Observations (1-20 fields) from the Catch_all_Table to
the new tables, they will populate to only the two fields and merely be
listed under one Observation Field. One of the problems is that these
Observations and Recommendations must maintain the order originally
identified by the Word Template i.e. (1-20)

So somehow, when I append I need to automatically assign Observation13 to
the 13th Observation (of that record) in the new table.

Thanks for taking up this challenge!


Eric the Rookie

 

[Tom van Stiphout]

On Thu, 14 Aug 2008 06:07:04 -0700, Eric

If you need to maintain the order, you will need a third field in your
table. Later you can use that for your ORDER BY clause.

-Tom.
Microsoft Access MVP

 

[Eric]

ok, thanks for the interest. Can you elaborate on the how and where to use
ue ORDER By clause.

Please explain as much as you can for me..... as my title reads, I'm still
wet behind the ears.

Thanks

 

[Eric]

I looked up the ORDER BY Clause information and somewhat uderstand your
suggesstion.

Ok, so I add a new field to the table say: "Priority" Now I have:

Observation
Recommendation
Priority (number field) i.e. 1 - 2 - 3

How do I set up my append query to pull Obsevation1, Observation2 etc. into
the single Observation field? And how does the new "Priority" field play
into all of this?

Thanks, and please stay with me here.