split records

  • Thread starter Thread starter Pommy_g
  • Start date Start date
P

Pommy_g

Hi, can anyone help with my problem.

I have a table with the following feilds:

Range/color1/qnt of size50 color1/qnt of size75 color 1/ qnt of
size100 color 1

all the way up to colour 20, (yeh i kno its a lot) there will only be
one range field though. so in total there will be 81 fiels.

Can someone please tell me how i would go about splitting the records
into smaller records set out like so

Range/colour/qnt of size 50/qnt of size 75/qnt of size 100

Appreciate any help

thanks
 
Dear Pommy:

The technique is well know as a normalizing UNION.

I strongly recommend you add one more column to the result, that being
a column representing the ColorNumber from which the information came.
This could be part of the unique key to the new table. Without this,
you would have no obvious candidate for the primary key. However, if
you omit this, you might want to construct the PK on Range / Color.
This would prevent you from having the same color twice for the same
Range, if that is desired. However, as your existing data has no
protection against such duplicates, it might then fail when you try to
put this in a table.

SELECT Range, 1 AS ColorNumber, color1 AS Color,
[qnt of size50 color1] AS [qnt of size50],
[qnt of size75 color1] AS [qnt of size75],
[qnt of size100 color1] AS [qnt of size100]
FROM YourTable
WHERE color1 IS NOT NULL
UNION ALL
SELECT Range, 2 AS ColorNumber, color2 AS Color,
[qnt of size50 color2] AS [qnt of size50],
[qnt of size75 color2] AS [qnt of size75],
[qnt of size100 color2] AS [qnt of size100]
FROM YourTable
WHERE color2 IS NOT NULL

Continue adding the section form UNION ALL to the end for 3-20. But,
before you go too far, take a look at what it is doing.

I have attempted to remove unused colors from each with the WHERE
clause. I do not know for sure whether an unused color is defined as
one where the color# is null, or perhaps there is some other way.

Adapt this by checking how unused colors are defined. Also, put in
the actual name of YourTable.

Once you see the results are what you want, create an append query to
put these rows into a new table. (I recommend against using make
table queries, as you loose control of column types.)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
thanks for the reply, the only problem is that i am not very familiar
with vba and all that, where am i supposed to put the code? i tried
putting it in as a module but it doesn;t seem to recognise the values
(field names) that i change, it comes up with "compile error" however
it does recognise the table.

thanks
 
Dear Pommy:

As the code I gave is SQL, not VBA, it is not intended to work in a
module, but it is the code for a query.

Open a new query (Create Query in Design View) and close the Show
Table window without selecting any table or query on which to base the
new query. Click SQL in the upper left corner, and paste in the SQL
code I wrote. Change "YourTable" to the actual table name. Correct
any column name spellings as well, then run it. You can add the other
18 colors later, just get this much working for now.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
briliant! worked a treat - thanks a lot. I forgot to put the [] round
the field names (they had spaces in them - bad practice i know). Could
u please confirm where i need to put the UNION ALL text, i got a bit
confused as when i put it on the very last line - it wouldn't work,
but when i took it off it did.

thanks v.much!!!
 
Dear Pommy:

A UNION query is constructed by writing a set of SELECT queries, each
of which has columns in the same order and with identical or
compatible datatypes. Each SELECT must be proper and functional.
Next they must all return the same number of columns.

The words UNION or UNION ALL are then placed between the SELECT
queries, and the result of all of them are placed into the same result
set. When you do not use ALL, it tries to match up and eliminate
duplicates of all columns returned. When you do not expect there to
be duplicates, or when you want to preserve duplications, use ALL.
For your purposes, I used ALL because it can be much faster, not
spending the time trying to find duplications.

This does not mean there cannot be duplicates given your data.
Assuming for the moment that Range was unique in the original table,
you could still have a Color entered twice in the existing data. In
the revised data, you now have the ability to define a unique index or
the primary key on Range / Color (ignoring the ColorNumber I added,
which would make it unique in any case) thereby preventing a Color
being added twice for any Range.

The ability to easily constrain the data to prevent such duplication
is a big advantage of the structure for the data that results from
this UNION query. In fact, such queries are called "Normalizing Union
Queries" because the greatly improve the structure of the data. To
constrain the data to prevent duplication of colors in 20 sets of
columns within each row would take (19) * (20) / 2 = 190 different
constraints. In addition, if you ever want to add a 21st color to one
of the Ranges, you'd have to add 4 new columns (and 20 more
constraints). With the normalized data, you don't have to change the
table structure or its constraints! That's the difference between
good design and bad - good design is extensible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


briliant! worked a treat - thanks a lot. I forgot to put the [] round
the field names (they had spaces in them - bad practice i know). Could
u please confirm where i need to put the UNION ALL text, i got a bit
confused as when i put it on the very last line - it wouldn't work,
but when i took it off it did.

thanks v.much!!!

Tom Ellison said:
Dear Pommy:

As the code I gave is SQL, not VBA, it is not intended to work in a
module, but it is the code for a query.

Open a new query (Create Query in Design View) and close the Show
Table window without selecting any table or query on which to base the
new query. Click SQL in the upper left corner, and paste in the SQL
code I wrote. Change "YourTable" to the actual table name. Correct
any column name spellings as well, then run it. You can add the other
18 colors later, just get this much working for now.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...
 
thankyou very much it was a great help


Tom Ellison said:
Dear Pommy:

A UNION query is constructed by writing a set of SELECT queries, each
of which has columns in the same order and with identical or
compatible datatypes. Each SELECT must be proper and functional.
Next they must all return the same number of columns.

The words UNION or UNION ALL are then placed between the SELECT
queries, and the result of all of them are placed into the same result
set. When you do not use ALL, it tries to match up and eliminate
duplicates of all columns returned. When you do not expect there to
be duplicates, or when you want to preserve duplications, use ALL.
For your purposes, I used ALL because it can be much faster, not
spending the time trying to find duplications.

This does not mean there cannot be duplicates given your data.
Assuming for the moment that Range was unique in the original table,
you could still have a Color entered twice in the existing data. In
the revised data, you now have the ability to define a unique index or
the primary key on Range / Color (ignoring the ColorNumber I added,
which would make it unique in any case) thereby preventing a Color
being added twice for any Range.

The ability to easily constrain the data to prevent such duplication
is a big advantage of the structure for the data that results from
this UNION query. In fact, such queries are called "Normalizing Union
Queries" because the greatly improve the structure of the data. To
constrain the data to prevent duplication of colors in 20 sets of
columns within each row would take (19) * (20) / 2 = 190 different
constraints. In addition, if you ever want to add a 21st color to one
of the Ranges, you'd have to add 4 new columns (and 20 more
constraints). With the normalized data, you don't have to change the
table structure or its constraints! That's the difference between
good design and bad - good design is extensible.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


briliant! worked a treat - thanks a lot. I forgot to put the [] round
the field names (they had spaces in them - bad practice i know). Could
u please confirm where i need to put the UNION ALL text, i got a bit
confused as when i put it on the very last line - it wouldn't work,
but when i took it off it did.

thanks v.much!!!

Tom Ellison said:
Dear Pommy:

As the code I gave is SQL, not VBA, it is not intended to work in a
module, but it is the code for a query.

Open a new query (Create Query in Design View) and close the Show
Table window without selecting any table or query on which to base the
new query. Click SQL in the upper left corner, and paste in the SQL
code I wrote. Change "YourTable" to the actual table name. Correct
any column name spellings as well, then run it. You can add the other
18 colors later, just get this much working for now.

Please let me know how you get along with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 9 Mar 2004 05:01:47 -0800, (e-mail address removed) (Pommy_g) wrote:

thanks for the reply, the only problem is that i am not very familiar
with vba and all that, where am i supposed to put the code? i tried
putting it in as a module but it doesn;t seem to recognise the values
(field names) that i change, it comes up with "compile error" however
it does recognise the table.

thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top