workaround for non normalized table

[Guest]

I've inherited a database that relies heavily on a non normalized table.
Until I can convince the general manager that I can normalize the data
without losing any records, I've got to have a workaround for certain
situations. Namely, I need to ensure that data is not being badly reproduced
at various stages of our operations. I would therefore like to reference the
information directly from the main table in order to populate certain
information in related tables. In this case, the main table uses [Order
Number] as its primary key, and ties it to a bunch of information like
material type, alloy, form, etc., but there are up to four part numbers for
every order number. When we process these orders, these part numbers can get
separated and so we will have multiple parts from the same order in various
stages of production throughout the shop. I need a query that will return
all of the part numbers for every order number in a common field. In other
words, in its simplest form the query would have two fields, Order Number and
Part Number, and for every Order Number would produce four records in the
Part Number field. I'm not worried about null records at this time. Not
being an adept in SQL, I thought I'd ask the big brains here at the
newsgroup. Your help will be appreciated!

 

[John Spencer]

Sounds like a good place to use a union query as pseudo table. The problem
with a UNION query is that no indexes will be available when you use the
Union query in other queries and any query where you do use it is probably
(almost definitely) not going to be updatable.

SELECT [Order number], PartNo1 as PartNo
FROM [MainTable]
WHERE PartNo1 is Not Null
UNION ALL
SELECT [Order number], PartNo2
FROM [MainTable]
WHERE PartNo2 is Not Null
UNION ALL
SELECT [Order number], PartNo3
FROM [MainTable]
WHERE PartNo3 is Not Null
UNION ALL
SELECT [Order number], PartNo4
FROM [MainTable]
WHERE PartNo4 is Not Null

Save that query as qOrderParts and use it in your other queries. OR you
could build a table with the relevant structure and use the Union query as
the source of an append query. The problem with that is that the data in
the new table will almost never be in synch with the actual data and you
will have to delete all the records from the table before you do an append.

As you've mentioned the real solution is to redesign the table structure.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

BINGO! Thank you for being so specific with your syntax! You have saved me
a huge amount of time... The only changes I made where to ORDER BY [Order
Number] and to set all four part numbers as [Part Number] as you did for
PartNo1. It even filters out the nulls! I love it! I'm guessing that when
the time comes to normalize this table that I would use a method similar to
this in a Make-Table Query? Have you any suggestions for ensuring and double
checking that I don't lose any data?
--
Why are you asking me? I dont know what Im doing!

Jaybird

Sounds like a good place to use a union query as pseudo table. The problem
with a UNION query is that no indexes will be available when you use the
Union query in other queries and any query where you do use it is probably
(almost definitely) not going to be updatable.

SELECT [Order number], PartNo1 as PartNo
FROM [MainTable]
WHERE PartNo1 is Not Null
UNION ALL
SELECT [Order number], PartNo2
FROM [MainTable]
WHERE PartNo2 is Not Null
UNION ALL
SELECT [Order number], PartNo3
FROM [MainTable]
WHERE PartNo3 is Not Null
UNION ALL
SELECT [Order number], PartNo4
FROM [MainTable]
WHERE PartNo4 is Not Null

Save that query as qOrderParts and use it in your other queries. OR you
could build a table with the relevant structure and use the Union query as
the source of an append query. The problem with that is that the data in
the new table will almost never be in synch with the actual data and you
will have to delete all the records from the table before you do an append.

As you've mentioned the real solution is to redesign the table structure.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've inherited a database that relies heavily on a non normalized table.
Until I can convince the general manager that I can normalize the data
without losing any records, I've got to have a workaround for certain
situations. Namely, I need to ensure that data is not being badly
reproduced
at various stages of our operations. I would therefore like to reference
the
information directly from the main table in order to populate certain
information in related tables. In this case, the main table uses [Order
Number] as its primary key, and ties it to a bunch of information like
material type, alloy, form, etc., but there are up to four part numbers
for
every order number. When we process these orders, these part numbers can
get
separated and so we will have multiple parts from the same order in
various
stages of production throughout the shop. I need a query that will return
all of the part numbers for every order number in a common field. In
other
words, in its simplest form the query would have two fields, Order Number
and
Part Number, and for every Order Number would produce four records in the
Part Number field. I'm not worried about null records at this time. Not
being an adept in SQL, I thought I'd ask the big brains here at the
newsgroup. Your help will be appreciated!

 

[John Spencer]

Yes, I would use the above method.

Also, you might consider one small modification. IF you have any records
with the part no being repeated in two (or more fields) you can change the
UNION ALL to just UNION. That will eliminate duplicates (if that is what
you want).

When it comes time to normalize, I would build the table with the fields I
wanted and then use the Union query in an append query to add the records to
the normalized tabl

IF you wanted you could use an unmatched query against the Order Number and
Part1 and then against Order Number and Part2., etc.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BINGO! Thank you for being so specific with your syntax! You have saved
me
a huge amount of time... The only changes I made where to ORDER BY [Order
Number] and to set all four part numbers as [Part Number] as you did for
PartNo1. It even filters out the nulls! I love it! I'm guessing that
when
the time comes to normalize this table that I would use a method similar
to
this in a Make-Table Query? Have you any suggestions for ensuring and
double
checking that I don't lose any data?
--
Why are you asking me? I dont know what Im doing!

Jaybird

Sounds like a good place to use a union query as pseudo table. The
problem
with a UNION query is that no indexes will be available when you use the
Union query in other queries and any query where you do use it is
probably
(almost definitely) not going to be updatable.

SELECT [Order number], PartNo1 as PartNo
FROM [MainTable]
WHERE PartNo1 is Not Null
UNION ALL
SELECT [Order number], PartNo2
FROM [MainTable]
WHERE PartNo2 is Not Null
UNION ALL
SELECT [Order number], PartNo3
FROM [MainTable]
WHERE PartNo3 is Not Null
UNION ALL
SELECT [Order number], PartNo4
FROM [MainTable]
WHERE PartNo4 is Not Null

Save that query as qOrderParts and use it in your other queries. OR you
could build a table with the relevant structure and use the Union query
as
the source of an append query. The problem with that is that the data in
the new table will almost never be in synch with the actual data and you
will have to delete all the records from the table before you do an
append.

As you've mentioned the real solution is to redesign the table structure.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've inherited a database that relies heavily on a non normalized
table.
Until I can convince the general manager that I can normalize the data
without losing any records, I've got to have a workaround for certain
situations. Namely, I need to ensure that data is not being badly
reproduced
at various stages of our operations. I would therefore like to
reference
the
information directly from the main table in order to populate certain
information in related tables. In this case, the main table uses
[Order
Number] as its primary key, and ties it to a bunch of information like
material type, alloy, form, etc., but there are up to four part numbers
for
every order number. When we process these orders, these part numbers
can
get
separated and so we will have multiple parts from the same order in
various
stages of production throughout the shop. I need a query that will
return
all of the part numbers for every order number in a common field. In
other
words, in its simplest form the query would have two fields, Order
Number
and
Part Number, and for every Order Number would produce four records in
the
Part Number field. I'm not worried about null records at this time.
Not
being an adept in SQL, I thought I'd ask the big brains here at the
newsgroup. Your help will be appreciated!

 

[Guest]

Thank You! These are all excellent suggestions. You have saved me much
hand-wringing! I will do it!
--
Why are you asking me? I dont know what Im doing!

Jaybird

Yes, I would use the above method.

Also, you might consider one small modification. IF you have any records
with the part no being repeated in two (or more fields) you can change the
UNION ALL to just UNION. That will eliminate duplicates (if that is what
you want).

When it comes time to normalize, I would build the table with the fields I
wanted and then use the Union query in an append query to add the records to
the normalized tabl

IF you wanted you could use an unmatched query against the Order Number and
Part1 and then against Order Number and Part2., etc.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BINGO! Thank you for being so specific with your syntax! You have saved
me
a huge amount of time... The only changes I made where to ORDER BY [Order
Number] and to set all four part numbers as [Part Number] as you did for
PartNo1. It even filters out the nulls! I love it! I'm guessing that
when
the time comes to normalize this table that I would use a method similar
to
this in a Make-Table Query? Have you any suggestions for ensuring and
double
checking that I don't lose any data?
--
Why are you asking me? I dont know what Im doing!

Jaybird

Sounds like a good place to use a union query as pseudo table. The
problem
with a UNION query is that no indexes will be available when you use the
Union query in other queries and any query where you do use it is
probably
(almost definitely) not going to be updatable.

SELECT [Order number], PartNo1 as PartNo
FROM [MainTable]
WHERE PartNo1 is Not Null
UNION ALL
SELECT [Order number], PartNo2
FROM [MainTable]
WHERE PartNo2 is Not Null
UNION ALL
SELECT [Order number], PartNo3
FROM [MainTable]
WHERE PartNo3 is Not Null
UNION ALL
SELECT [Order number], PartNo4
FROM [MainTable]
WHERE PartNo4 is Not Null

Save that query as qOrderParts and use it in your other queries. OR you
could build a table with the relevant structure and use the Union query
as
the source of an append query. The problem with that is that the data in
the new table will almost never be in synch with the actual data and you
will have to delete all the records from the table before you do an
append.

As you've mentioned the real solution is to redesign the table structure.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've inherited a database that relies heavily on a non normalized
table.
Until I can convince the general manager that I can normalize the data
without losing any records, I've got to have a workaround for certain
situations. Namely, I need to ensure that data is not being badly
reproduced
at various stages of our operations. I would therefore like to
reference
the
information directly from the main table in order to populate certain
information in related tables. In this case, the main table uses
[Order
Number] as its primary key, and ties it to a bunch of information like
material type, alloy, form, etc., but there are up to four part numbers
for
every order number. When we process these orders, these part numbers
can
get
separated and so we will have multiple parts from the same order in
various
stages of production throughout the shop. I need a query that will
return
all of the part numbers for every order number in a common field. In
other
words, in its simplest form the query would have two fields, Order
Number
and
Part Number, and for every Order Number would produce four records in
the
Part Number field. I'm not worried about null records at this time.
Not
being an adept in SQL, I thought I'd ask the big brains here at the
newsgroup. Your help will be appreciated!

 

[Guest]

A small problem... I've modified the query you gave me to include several
other fields on a form. I'm trying to populate these fields by selecting a
specific instance of the [Order Number] that is produced by the query.
However, only the values of the FIRST instance of {Order Number] are
populating these fields, no matter which one I select. (I'm using the UNION
query as my row source for a combo box.) How do I specify the data to be
displayed?

---Why are you asking me? I dont know what Im doing!

Jaybird

Thank You! These are all excellent suggestions. You have saved me much
hand-wringing! I will do it!
--
Why are you asking me? I dont know what Im doing!

Jaybird

Yes, I would use the above method.

Also, you might consider one small modification. IF you have any records
with the part no being repeated in two (or more fields) you can change the
UNION ALL to just UNION. That will eliminate duplicates (if that is what
you want).

When it comes time to normalize, I would build the table with the fields I
wanted and then use the Union query in an append query to add the records to
the normalized tabl

IF you wanted you could use an unmatched query against the Order Number and
Part1 and then against Order Number and Part2., etc.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

BINGO! Thank you for being so specific with your syntax! You have saved
me
a huge amount of time... The only changes I made where to ORDER BY [Order
Number] and to set all four part numbers as [Part Number] as you did for
PartNo1. It even filters out the nulls! I love it! I'm guessing that
when
the time comes to normalize this table that I would use a method similar
to
this in a Make-Table Query? Have you any suggestions for ensuring and
double
checking that I don't lose any data?
--
Why are you asking me? I dont know what Im doing!

Jaybird


:

Sounds like a good place to use a union query as pseudo table. The
problem
with a UNION query is that no indexes will be available when you use the
Union query in other queries and any query where you do use it is
probably
(almost definitely) not going to be updatable.

SELECT [Order number], PartNo1 as PartNo
FROM [MainTable]
WHERE PartNo1 is Not Null
UNION ALL
SELECT [Order number], PartNo2
FROM [MainTable]
WHERE PartNo2 is Not Null
UNION ALL
SELECT [Order number], PartNo3
FROM [MainTable]
WHERE PartNo3 is Not Null
UNION ALL
SELECT [Order number], PartNo4
FROM [MainTable]
WHERE PartNo4 is Not Null

Save that query as qOrderParts and use it in your other queries. OR you
could build a table with the relevant structure and use the Union query
as
the source of an append query. The problem with that is that the data in
the new table will almost never be in synch with the actual data and you
will have to delete all the records from the table before you do an
append.

As you've mentioned the real solution is to redesign the table structure.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've inherited a database that relies heavily on a non normalized
table.
Until I can convince the general manager that I can normalize the data
without losing any records, I've got to have a workaround for certain
situations. Namely, I need to ensure that data is not being badly
reproduced
at various stages of our operations. I would therefore like to
reference
the
information directly from the main table in order to populate certain
information in related tables. In this case, the main table uses
[Order
Number] as its primary key, and ties it to a bunch of information like
material type, alloy, form, etc., but there are up to four part numbers
for
every order number. When we process these orders, these part numbers
can
get
separated and so we will have multiple parts from the same order in
various
stages of production throughout the shop. I need a query that will
return
all of the part numbers for every order number in a common field. In
other
words, in its simplest form the query would have two fields, Order
Number
and
Part Number, and for every Order Number would produce four records in
the
Part Number field. I'm not worried about null records at this time.
Not
being an adept in SQL, I thought I'd ask the big brains here at the
newsgroup. Your help will be appreciated!