Need to Normalize (Repost)

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

I have been advised to use an "ORDER by Clause" but am not sure how to set
it all up with regard to an Append Query.

Thanks for taking up this challenge!

 

[Jim Burke in Novi]

As far as I know, you really shouldn't worry about the 'order' within the
database. If you need to be able to order them after they've been brought in,
you need another field in the table to keep track of that, and populate that
field as the records are being brought in. You may need to change your
process of importing the data in order to do that. You'd need to have some
VBA intervention so that you could look at the field name from Word and strip
off the number at the end. So you'd end up with fields called Observation,
Recomendation, and OrderID or something like that. Maybe I'm off here and
someone else knows a better way?

 

[John W. Vinson]

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.

A "Normalizing Union Query" is the ticket here. Import into your wide-flat
table as you're doing; then construct a Query in the SQL window like

SELECT <whatever common fields you want, e.g. the unique ID>, Observation1,
Recommendation1, (1) AS SeqNo FROM Catch_all_table
WHERE Recommendation1 IS NOT NULL OR Observation1 IS NOT NULL
UNION ALL
SELECT <whatever common fields you want, e.g. the unique ID>, Observation2,
Recommendation2, (2) AS SeqNo FROM Catch_all_table
WHERE Recommendation1 IS NOT NULL OR Observation2 IS NOT NULL
UNION ALL
SELECT <whatever common fields you want, e.g. the unique ID>, Observation1,
Recommendation1, (1) AS SeqNo FROM Catch_all_table
WHERE Observation1 IS NOT NULL OR Observation2 IS NOT NULL
UNION ALL
<etc>

Base an Append query on this UNION query to populate a new table.

This assumes that the new table has a SeqNo field (that's essential if you
want to maintain the order) and that each Observation is logically paired with
the corresponding Recommendation.

 

[Eric]

Thanks John, this is exactly what I need. I have never built a Union Query.
The example you gave looks like what you want me to use, I just need some
help in making up the code in the SQL window? Also how do I incorporate with
the Append Query?

Here is the strucure:

Flat table (Catch ALL)
Observation1 (1-20)
Recommendation 1 (1-20)



New Table
SeqNo (new field)
Observation
Recommendation.

If the code you posted can be pasted, then I appoligize and will try it.

Thanks John

 

[John W. Vinson]

Thanks John, this is exactly what I need. I have never built a Union Query.
The example you gave looks like what you want me to use, I just need some
help in making up the code in the SQL window? Also how do I incorporate with
the Append Query?

Here is the strucure:

Flat table (Catch ALL)
Observation1 (1-20)
Recommendation 1 (1-20)



New Table
SeqNo (new field)
Observation
Recommendation.

If the code you posted can be pasted, then I appoligize and will try it.

Thanks John

You'll need two queries, a UNION query and then an Append query based on it.
In the SQL window type

SELECT (1) AS SeqNo, [Observation1], [Recommendation1]
FROM [Catch All]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT (2), [Observation2], [Recommendation2]
FROM [Catch All]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
< etc etc through all 20>

Save this query as (say) uniNormalize. Then create a new query based on
uniNormalize, and make it an Append query into your new table.

Two concerns: this will convert each record in [Catch All] into 20 records in
the new table. However, there is nothing in New Table to indicate *which*
record in Catch All the record came from! Does Catch All have some sort of
unique key or identifier? If so, include it in each SELECT statement. If not,
you may have to create one first. Secondly, I'm assuming that you want a new
record if there is non-null data in either Observation or Recommendation - if
these will never be NULL, or if there will always be a Recommendation if there
is an Observation, or if there should never be a Recommendation UNLESS there
is an Observation, you may need to tweak the WHERE clause.

 

[Eric]

John I think this is going to be great, but I'm getting a sytex error.

The Catch all Table is named "Review". In the "Review" Table, [Review_ID]
is the unique field (Primary Key). There can be nulls in either Observation
or Recommendation, so I think your statement is correct as it is. The
"SeqNo" is a field that I will use in the new Field upon append.

Here is the code as transcribed from your code.

SELECT (1) AS SeqNo, [Observation1], [Recommendation1]
FROM [Review]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT (2) AS SeqNo, [Observation2], [Recommendation2]
FROM [Review]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
SELECT (3) AS SeqNo, [Observation3], [Recommendation3]
FROM [Review]
WHERE [Observation3] IS NOT NULL OR [Recommendation3] IS NOT NULL
UNION ALL
SELECT (4) AS SeqNo, [Observation4], [Recommendation4]
FROM [Review]
WHERE [Observation4] IS NOT NULL OR [Recommendation4] IS NOT NULL
UNION ALL

I think I am missing a reference to the Review! Table, and I think the SeqNo
will only be used in the Append Query.

Also you said that the two Union/Append will work together. How can I check
the union to verify that it works prior to building the Append?

Thanks for your help, I'm sure I can get there with a little more help.

--
Eric the Rookie

Thanks John, this is exactly what I need. I have never built a Union Query.
The example you gave looks like what you want me to use, I just need some
help in making up the code in the SQL window? Also how do I incorporate with
the Append Query?

Here is the strucure:

Flat table (Catch ALL)
Observation1 (1-20)
Recommendation 1 (1-20)



New Table
SeqNo (new field)
Observation
Recommendation.

If the code you posted can be pasted, then I appoligize and will try it.

Thanks John

You'll need two queries, a UNION query and then an Append query based on it.
In the SQL window type

SELECT (1) AS SeqNo, [Observation1], [Recommendation1]
FROM [Catch All]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT (2), [Observation2], [Recommendation2]
FROM [Catch All]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
< etc etc through all 20>

Save this query as (say) uniNormalize. Then create a new query based on
uniNormalize, and make it an Append query into your new table.

Two concerns: this will convert each record in [Catch All] into 20 records in
the new table. However, there is nothing in New Table to indicate *which*
record in Catch All the record came from! Does Catch All have some sort of
unique key or identifier? If so, include it in each SELECT statement. If not,
you may have to create one first. Secondly, I'm assuming that you want a new
record if there is non-null data in either Observation or Recommendation - if
these will never be NULL, or if there will always be a Recommendation if there
is an Observation, or if there should never be a Recommendation UNLESS there
is an Observation, you may need to tweak the WHERE clause.

 

[Eric]

John, one other thing.... The field [Review_ID] is The Primary Key for both
the Review Table (Catch all) and the new Table.

Thanks
--
Eric the Rookie

Thanks John, this is exactly what I need. I have never built a Union Query.
The example you gave looks like what you want me to use, I just need some
help in making up the code in the SQL window? Also how do I incorporate with
the Append Query?

Here is the strucure:

Flat table (Catch ALL)
Observation1 (1-20)
Recommendation 1 (1-20)



New Table
SeqNo (new field)
Observation
Recommendation.

If the code you posted can be pasted, then I appoligize and will try it.

Thanks John

You'll need two queries, a UNION query and then an Append query based on it.
In the SQL window type

SELECT (1) AS SeqNo, [Observation1], [Recommendation1]
FROM [Catch All]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT (2), [Observation2], [Recommendation2]
FROM [Catch All]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
< etc etc through all 20>

Save this query as (say) uniNormalize. Then create a new query based on
uniNormalize, and make it an Append query into your new table.

Two concerns: this will convert each record in [Catch All] into 20 records in
the new table. However, there is nothing in New Table to indicate *which*
record in Catch All the record came from! Does Catch All have some sort of
unique key or identifier? If so, include it in each SELECT statement. If not,
you may have to create one first. Secondly, I'm assuming that you want a new
record if there is non-null data in either Observation or Recommendation - if
these will never be NULL, or if there will always be a Recommendation if there
is an Observation, or if there should never be a Recommendation UNLESS there
is an Observation, you may need to tweak the WHERE clause.

 

[Eric]

John, I don't want to be a pest, but here is the structure of both tables:

Table: Review (Catch All)
[Review_ID] (Primary Key)
[Observation1]
[Recommendation1]
[Observation2]
[Recommendation2]
etc.

New Table: ReviewOB
[Review_ID] (Primary Key)
[SeqNo] (New Field)
[Observation]
[Recommendation]

Hope this helps.

--
Eric the Rookie

Thanks John, this is exactly what I need. I have never built a Union Query.
The example you gave looks like what you want me to use, I just need some
help in making up the code in the SQL window? Also how do I incorporate with
the Append Query?

Here is the strucure:

Flat table (Catch ALL)
Observation1 (1-20)
Recommendation 1 (1-20)



New Table
SeqNo (new field)
Observation
Recommendation.

If the code you posted can be pasted, then I appoligize and will try it.

Thanks John

You'll need two queries, a UNION query and then an Append query based on it.
In the SQL window type

SELECT (1) AS SeqNo, [Observation1], [Recommendation1]
FROM [Catch All]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT (2), [Observation2], [Recommendation2]
FROM [Catch All]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
< etc etc through all 20>

Save this query as (say) uniNormalize. Then create a new query based on
uniNormalize, and make it an Append query into your new table.

Two concerns: this will convert each record in [Catch All] into 20 records in
the new table. However, there is nothing in New Table to indicate *which*
record in Catch All the record came from! Does Catch All have some sort of
unique key or identifier? If so, include it in each SELECT statement. If not,
you may have to create one first. Secondly, I'm assuming that you want a new
record if there is non-null data in either Observation or Recommendation - if
these will never be NULL, or if there will always be a Recommendation if there
is an Observation, or if there should never be a Recommendation UNLESS there
is an Observation, you may need to tweak the WHERE clause.

 

[John W. Vinson]

John, one other thing.... The field [Review_ID] is The Primary Key for both
the Review Table (Catch all) and the new Table.

It CANNOT be the primary key for the new table! There will be (up to) 20
records for each Review_ID value, and the primary key must, by definition, be
unique. You may want to use a two-field primary key consisting of Review_ID
and SeqNo. If there are other fields other than the review ID, observations
and recommendations, you'll want two tables - one for the fields that pertain
to an individual Review related one to many to this new table of observations
and recommendations. My proposed query can be modified to:

SELECT [Review_ID], (1) AS SeqNo, [Observation1] AS Observation,
[Recommendation1] AS Recommendation
FROM [Review]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT [Review_ID], (2) AS SeqNo, [Observation2], [Recommendation2]
FROM [Review]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
SELECT [Review_ID], (3) AS SeqNo, [Observation3], [Recommendation3]
FROM [Review]
WHERE [Observation3] IS NOT NULL OR [Recommendation3] IS NOT NULL
UNION ALL
SELECT [Review_ID], (4) AS SeqNo, [Observation4], [Recommendation4]
FROM [Review]
WHERE [Observation4] IS NOT NULL OR [Recommendation4] IS NOT NULL
UNION ALL
....
SELECT [Review_ID], (20) AS SeqNo, [Observation20], [Recommendation20]
FROM [Review]
WHERE [Observation20] IS NOT NULL OR [Recommendation20] IS NOT NULL ;

The SeqNo field does indeed need to be in the UNION query - it's the only
thing that distinguishes Observation1 from Observation18!

You can save the union query as uniNormalize and simply open it by
doubleclicking, or clicking on the datasheet view icon from design view to see
the data. I have no idea what you mean about needing a reference to the Review
table - that's what the FROM [Review] clause does.

Your Append query would be something like

INSERT INTO Observations(Review_ID, SeqNo, Observation, Recommendation)
SELECT Review_ID, SeqNo, Observation, Recommendation
FROM uniNormalize;

 

[Eric]

Thanks again John, I'm still getting an error:

(Invalid SQL statement, expected 'DELETE', INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE',

Not sure what its wanting here.

It CANNOT be the primary key for the new table! There will be (up to) 20
records for each Review_ID value, and the primary key must, by definition, be
unique. You may want to use a two-field primary key consisting of Review_ID
and SeqNo. If there are other fields other than the review ID, observations
and recommendations, you'll want two tables - one for the fields that pertain
to an individual Review related one to many to this new table of observations
and recommendations.

I have made sure that the [Review_ID] is not a primary key in the
Observation table. There are plenty of other "Review" related fields in the
Chatch all, but I have already segregated them out to other tables and will
be using this union method on all of them to normalize the database.

So once I get past the errors, I see that I simply use the Append query to
call up the union query data... is that right?

Thanks John, when we get this working I'll have to send you the winning
lotto numbers.

--
Eric the Rookie

John, I don't want to be a pest, but here is the structure of both tables:

Table: Review (Catch All)
[Review_ID] (Primary Key)
[Observation1]
[Recommendation1]
[Observation2]
[Recommendation2]
etc.

New Table: ReviewOB
[Review_ID] (Primary Key)
[SeqNo] (New Field)
[Observation]
[Recommendation]

Hope this helps.

--
Eric the Rookie

Thanks John, this is exactly what I need. I have never built a Union Query.
The example you gave looks like what you want me to use, I just need some
help in making up the code in the SQL window? Also how do I incorporate with
the Append Query?

Here is the strucure:

Flat table (Catch ALL)
Observation1 (1-20)
Recommendation 1 (1-20)



New Table
SeqNo (new field)
Observation
Recommendation.

If the code you posted can be pasted, then I appoligize and will try it.

Thanks John

You'll need two queries, a UNION query and then an Append query based on it.
In the SQL window type

SELECT (1) AS SeqNo, [Observation1], [Recommendation1]
FROM [Catch All]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT (2), [Observation2], [Recommendation2]
FROM [Catch All]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
< etc etc through all 20>

Save this query as (say) uniNormalize. Then create a new query based on
uniNormalize, and make it an Append query into your new table.

Two concerns: this will convert each record in [Catch All] into 20 records in
the new table. However, there is nothing in New Table to indicate *which*
record in Catch All the record came from! Does Catch All have some sort of
unique key or identifier? If so, include it in each SELECT statement. If not,
you may have to create one first. Secondly, I'm assuming that you want a new
record if there is non-null data in either Observation or Recommendation - if
these will never be NULL, or if there will always be a Recommendation if there
is an Observation, or if there should never be a Recommendation UNLESS there
is an Observation, you may need to tweak the WHERE clause.

 

[John W. Vinson]

John, I don't want to be a pest, but here is the structure of both tables:

Table: Review (Catch All)
[Review_ID] (Primary Key)
[Observation1]
[Recommendation1]
[Observation2]
[Recommendation2]
etc.

New Table: ReviewOB
[Review_ID] (Primary Key)
[SeqNo] (New Field)
[Observation]
[Recommendation]

Hope this helps.

Again:

Review_ID *CANNOT POSSIBLY BE* the primary key of ReviewOB.

A primary key is, by definition, unique in the table; there can only be one
record with a given value of the field.

Your ReviewOB table will have up to 20 records for each Review_ID. Twenty, not
one!!!

You will need to make Review_ID and SeqNo a joint two-field primary key by
clicking both fields in table design view (so they darken indicating that they
are selected) and then clicking the key icon.

 

[John W. Vinson]

I have made sure that the [Review_ID] is not a primary key in the
Observation table. There are plenty of other "Review" related fields in the
Chatch all, but I have already segregated them out to other tables and will
be using this union method on all of them to normalize the database.

Please post the SQL view of your query and indicate what specific problem
you're having.

So once I get past the errors, I see that I simply use the Append query to
call up the union query data... is that right?

Since the Append query is based on the Union query, there's not really any
"calling up" to be done - when you run the append it will populate the new
observations table with all the records. You won't see the data going in,
it'll just *be* there when it's done. There will be no need to specifically
invoke the union query (unless you want to).

Thanks John, when we get this working I'll have to send you the winning
lotto numbers.

<g> I'll pass thanks... just pay it forward, help somebody else with something
that you're good at and that they need.

 

[John W. Vinson]

Thanks again John, I'm still getting an error:

(Invalid SQL statement, expected 'DELETE', INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE',

Please post the entire SQL you're trying to run.

 

[Eric]

John here is the SQL Statement:

SELECT [Review_ID], (1) AS SeqNo, [Observation1] AS Observation,
[Recommendation1] AS Recommendation
FROM [Review]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT [Review_ID], (2) AS SeqNo, [Observation2], [Recommendation2]
FROM [Review]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
SELECT [Review_ID], (3) AS SeqNo, [Observation3], [Recommendation3]
FROM [Review]
WHERE [Observation3] IS NOT NULL OR [Recommendation3] IS NOT NULL
UNION ALL
SELECT [Review_ID], (4) AS SeqNo, [Observation4], [Recommendation4]
FROM [Review]
WHERE [Observation4] IS NOT NULL OR [Recommendation4] IS NOT NULL
UNION ALL;

--
Eric the Rookie

John, I don't want to be a pest, but here is the structure of both tables:

Table: Review (Catch All)
[Review_ID] (Primary Key)
[Observation1]
[Recommendation1]
[Observation2]
[Recommendation2]
etc.

New Table: ReviewOB
[Review_ID] (Primary Key)
[SeqNo] (New Field)
[Observation]
[Recommendation]

Hope this helps.

Again:

Review_ID *CANNOT POSSIBLY BE* the primary key of ReviewOB.

A primary key is, by definition, unique in the table; there can only be one
record with a given value of the field.

Your ReviewOB table will have up to 20 records for each Review_ID. Twenty, not
one!!!

You will need to make Review_ID and SeqNo a joint two-field primary key by
clicking both fields in table design view (so they darken indicating that they
are selected) and then clicking the key icon.

 

[John W. Vinson]

John here is the SQL Statement:

SELECT [Review_ID], (1) AS SeqNo, [Observation1] AS Observation,
[Recommendation1] AS Recommendation
FROM [Review]
WHERE [Observation1] IS NOT NULL OR [Recommendation1] IS NOT NULL
UNION ALL
SELECT [Review_ID], (2) AS SeqNo, [Observation2], [Recommendation2]
FROM [Review]
WHERE [Observation2] IS NOT NULL OR [Recommendation2] IS NOT NULL
UNION ALL
SELECT [Review_ID], (3) AS SeqNo, [Observation3], [Recommendation3]
FROM [Review]
WHERE [Observation3] IS NOT NULL OR [Recommendation3] IS NOT NULL
UNION ALL
SELECT [Review_ID], (4) AS SeqNo, [Observation4], [Recommendation4]
FROM [Review]
WHERE [Observation4] IS NOT NULL OR [Recommendation4] IS NOT NULL
UNION ALL;

Leave off the last UNION ALL. There needs to be a UNION ALL *between* each
pair of select statements, but not one at the end.

For "prime time" you'll need all 20 SELECT statements, not just four of
course.