If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
turn a table over
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?) |
#2
|
|||
|
|||
turn a table over
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 " wrote: 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?) |
#3
|
|||
|
|||
turn a table over
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). -- _________ Sean Bailey " wrote: 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?) |
#4
|
|||
|
|||
turn a table over
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. :-) |
#5
|
|||
|
|||
turn a table over
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 :-) |
#6
|
|||
|
|||
turn a table over
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. -- _________ Sean Bailey " wrote: 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 :-) |
#7
|
|||
|
|||
turn a table over
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. |
#8
|
|||
|
|||
turn a table over
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 " wrote: thanks Clifford! I'll try the union query. :-) |
Thread Tools | |
Display Modes | |
|
|