turn a table over

[maurizio]

i have a badly designed table that has some fields with values that
would better fit into a separate table
e.g. i have something like this:

let's say that every record is a fruitseller, and that the table holds
info about him and about his stock of fruits.
I have fields:
-name
-address
-no. of apples
-no. of oranges
-no. of pineapples
-no. of coconuts
....
etc

I want to fix it, building two tables 1-n, with:
TABLE 1 (fruitsellers)
-fruitseller-id
-name
-address

TABLE 2 (stock)
-fruitseller-id
-type of fruit (e.g oranges, or pineapples, or...)
-number of fruits of that type

How can I pass data from the present table to the "turned over" double
table? it's easy to get the fruitseller table, but what about the other
one? I need to add a record in it with the appropriate fruitseller id
for every fruit field that has value >0 for that fruitseller in the
present table, but how do i do it?

(I can't write VB or AccessBasic...; I have Access 2000 or 2003. Can it
be done with a query?)

 

[Clifford Bass]

Hi Maurizio,

Sure can, assuming that your current table has the fruit seller IDs in
it. It can be done with either a bunch of queries or with one long insert /
union query. Here is how to do it with a union query.

insert into stock ([fruitseller-id], [type of fruit], [quantity])
select [fruitseller-id], "Apples", [no. of apples]
from [oldtable]
where [no. of apples] > 0
union all
select [fruitseller-id], "Oranges", [no. of oranges]
from [oldtable]
where [no. of oranges] > 0
union all
select [fruitseller-id], "Pineapples", [no. of pineapples]
from [oldtable]
where [no. of pineapples] > 0
.......

Just keep adding union all select .... statements to the end until you
have statements for all of your current fruit fields.

Clifford Bass

 

[Beetle]

Actually, you need at least two more tables (not one). A fruit seller can
sell many types of fruit, and any given type of fruit can be sold by
many fruit sellers. To model this many-to-many relationship you need
a third (junction) table to define it. For example;

tblFruitSellers
**********
SellerID (Primary Key)
CompanyName
other attributes specific to each seller

tblFruitTypes
*********
TypeID (PK)
FruitName
other attributes specific to each type of fruit

tblFruitSales
*********
SellerID (Foreign Key to tblFruitSellers)
TypeID (FK to tblFruitTypes)
SaleQty
other attributes that apply to fruit sales (like SaleDate or whatever)

The junction table woul typically use a composite PK.

As far as rolling your existing data, you may need to create some temporary
tables or fields and use update queries to get the proper fruit TypeID
generated for each fruit name, etc. (once you have the ID values established
in the fruit type table).

 

[maurizio]

Clifford Bass ha scritto:

Hi Maurizio,

Sure can, assuming that your current table has the fruit seller IDs in
it. It can be done with either a bunch of queries or with one long insert /
union query. Here is how to do it with a union query.

insert into stock ([fruitseller-id], [type of fruit], [quantity])
select [fruitseller-id], "Apples", [no. of apples]
from [oldtable]
where [no. of apples] > 0
union all
select [fruitseller-id], "Oranges", [no. of oranges]
from [oldtable]
where [no. of oranges] > 0
union all
select [fruitseller-id], "Pineapples", [no. of pineapples]
from [oldtable]
where [no. of pineapples] > 0
......

Just keep adding union all select .... statements to the end until you
have statements for all of your current fruit fields.

Clifford Bass

thanks Clifford!
I'll try the union query.

 

[maurizio]

Beetle ha scritto:

Actually, you need at least two more tables (not one). A fruit seller can
sell many types of fruit, and any given type of fruit can be sold by
many fruit sellers. To model this many-to-many relationship you need
a third (junction) table to define it. For example;

tblFruitSellers
**********
SellerID (Primary Key)
CompanyName
other attributes specific to each seller

tblFruitTypes
*********
TypeID (PK)
FruitName
other attributes specific to each type of fruit

tblFruitSales
*********
SellerID (Foreign Key to tblFruitSellers)
TypeID (FK to tblFruitTypes)
SaleQty
other attributes that apply to fruit sales (like SaleDate or whatever)

The junction table woul typically use a composite PK.

As far as rolling your existing data, you may need to create some temporary
tables or fields and use update queries to get the proper fruit TypeID
generated for each fruit name, etc. (once you have the ID values established
in the fruit type table).

Thanks!
I don't need any additional info on the fruittypes (no other
attributes), so I'll try the union query suggested by Clifford: but i'll
keep in mind the possibility of a many-to-many design

 

[Beetle]

Even if you don't have any other attributes of the fruit types that you
need to keep track of, it would still be easier (and better design) to have
them in their own table. That way you can use a combo box to select
the fruit type for each record rather than having to repeatedly type the
actual names over and over again. It would also make for easier
queries and reporting when you want to summarize data based on the
different fruit types.

 

[Piet Linden]

Sorry about beating a dead horse, but I worked on non-normalized
designs like the one you describe for six months, and trying to
summarize something like that is a complete nightmare. Performance
was horrendous, as was validation. You just don't want to go there.
It gets down to things like "What does a blank field in a record
really mean?" Does it mean (a) there IS a value, but we don't know it?
(Missing data) or does it mean that there is no value? These are
fundamentally different and need to be treated differently, and if you
don't normalize, you can't do it.

 

[Clifford Bass]

Hi Maurizio,

You are welcome. Beetle's thoughts are good to keep in mind for future
if needed. My query was written with the assumption that you do have a fruit
type table and would adjust the query appropriately. However, just in case
you do not, I do recommend as Beetle did that you do incorporate a fruit type
table so as, at a minimum, to be able to enforce specific fruit names. Even
if it only contains the fruit name, you can use that to populate combo boxes
and also to prevent people from entering fruit names in all sorts of
different manners. For example: Someone might enter Apples also as Appls or
Blue Berries also as Blueberries.

Clifford Bass