splitting 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
 
Why not have a table with just 3 fields:

Range Color QtySize


that way, you ONLY have 3 fields!

To make the color data entry easy, and even the range number, those fields
could be combo boxes. (and, thus you could create 3 additional tables:

tblRanges
tblColors
tblQtySizes

You then use the combo box wizard in your form to make a combo box for the
Range,Color,QtySie fields.

The real nice thing about the above is that you can add new colors, ranges
etc, and NEVER have to change your table designs. Chaining a table design is
a real hard thing, as all forms, reports etc must be modified when you add
new fields. However, nothing needs to be changed when you add new records!

The above process is the MOST IMPORTANT thing you will EVER learn about
database design. If you learn the above process (it is called normalizing),
then you can develop quality database applications!

For more info on this concept ..check out:
ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613
 
thanks for the help, but the problem is that on the form it needs to
be viewed as it is on the 1st table, if you get what i mean. This is
because there will be "ranges" which will each contain different
colours, and each colour has differnt quantities for different sizes.
So on the form i just want it to display as:

range 1
colour 1 - size 1 qnt / size 2 qnt / size 3 qnt
colour 2 - size 1 qnt / size 2 qnt / size 3 qnt
colour 3 - size 1 qnt / size 2 qnt / size 3 qnt

as opposed to running it directly from a normalised table, which would
result in the user needing to enter the range for each record, like
so:
range 1 / colour / size 1 qnt / size 2 qnt / size 3 qnt
range 2 / colour / size 1 qnt / size 2 qnt / size 3 qnt
range 3 / colour / size 1 qnt / size 2 qnt / size 3 qnt

i know it is only one other field to fill in, but it also makes it
easier for the user to view, they could just scan from one record to
the next and view/edit the data for a whole range, as opposed to
having to individually look through each record for the right one.

i was thinking of having the form appear as i wanted but still being
run from a normalised table, however i think that this would be a far
bigger mission than simply splitting the fields. As for having the
table set out as you said, it is not totally what is required, as
there are 3 different sizes for each colour within each range, and the
quantity needs to be stated for each size, so what i'm really looking
for is to split the data again, with the records that hav a particular
size goin to a particular table (3 in total) and then joining them
back up in a normalised table which would be

range / colour / size / quantity


(phew) that was a mouthful, appreciate the reply, hope i made the
problem a bit clearer

thanks
 
Back
Top