On 8 Mar 2004 08:04:49 -0800, (e-mail address removed) (Pommy_g) wrote:
Pommy, one request: if you want to post a question to multiple
newsgroups, crosspost rather than multiposting. Many of us volunteers
look at several newsgroups and it's much simpler to know if a message
has been answered already if you crosspost. Put all (*NO MORE THAN
THREE*) the newsgroup names in the Newsgroups line, and post them
together.
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
Then you have a BADLY DESIGNED TABLE. You should not store data in
fieldnames at all, and should not have repeating fields. It's good
that you're changing this!
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
I'd go further: Range/Colour/Size/Quantity.
In order to migrate the data you can use a "Normalizing Union Query".
This is a bit of work, since you must go to the SQL window, but it's
not difficult. Set up your new table (I'll assume my suggested
structure but the same principle would apply if you use yours); then
create a Query by typing
SELECT Range, Color1 AS Color, (50) AS Size, [qnt of Size50 color1] AS
Quantity
WHERE [qnt of Size50 color1] IS NOT NULL
UNION ALL
SELECT Range, Color1 AS Color, (75) AS Size, [qnt of Size75 color1] AS
Quantity
WHERE [qnt of Size75 color1] IS NOT NULL
UNION ALL
SELECT Range, Color1 AS Color, (100) AS Size, [qnt of Size100 color1]
AS Quantity
WHERE [qnt of Size100 color1] IS NOT NULL
UNION ALL
SELECT Range, Color2 AS Color, (50) AS Size, [qnt of Size50 color2] AS
Quantity
WHERE [qnt of Size50 color2] IS NOT NULL
UNION ALL
SELECT Range, Color2 AS Color, (75) AS Size, [qnt of Size75 color2] AS
Quantity
WHERE [qnt of Size75 color2] IS NOT NULL
UNION ALL
SELECT Range, Color2 AS Color, (100) AS Size, [qnt of Size100 color2]
AS Quantity
WHERE [qnt of Size100 color2] IS NOT NULL
UNION ALL
...
You may need to do this in two or three passes if you get the QUERY
TOO COMPLEX error - in fact I'd try it on color1 through color10
first; then maybe color11 through color30 and so on.
Base an Append query on this UNION query and run it (maybe go get a
cup of tea while it's running, it might take a while!)
