Split Table into different Tables

[silentwolf]

Hi guys,

A long time ago I found an article about splitting a table into several tables for nomalization but I can not find it anymore.
But it would be very handy now as I need to split a table into differnt tables.

I got one Product Table and it is not really normalized as I got that data from Excel.

In that table there are ProdCat, ProdCat1, ProdCat2, ProdCat3, ProdCat4

In that article it showed how to programmatically split those fields into new tables.

Does someone has a code snippes for that or can give me a tip on how to go about it rather then do it all by hand?

Hope I explained it ok for you to understand what I am after.


Many Thanks for Help.

 

[pbaldy]

A UNION query is a way to "normalize" data like that. At it's simplest:

SELECT ProdCat
FROM TableName
UNION ALL
SELECT ProdCat1
FROM TableName
UNION ALL
...

 

[silentwolf]

Hi guys,

thanks for your replies!
I will try that out and let you know how I am going with it!

Cheers

 

[silentwolf]

Hi again,

I did look into it and it gave me a good understanding on what needs to be done but it is not quite what I am after.
Those queries helped to check and find those in each"Category" shown in the Attachment.

Meaning that in that case there is Prod1, Prod2 Prod3, Prod4, a type of ProductCategories cascading down..

I guess I did not explain it correctly it was late already and I do appologies if that was the case!

It is a kind of cascading typ I try to explain below in the table

Prod1	Prod2	Prod3	Prod4
In this field there about two different "Prod1"	Cascading to Prod1 with about 10 different "Prod2"	Cascading to Prod2 with about 10 different "Prod3"	Cascading to Prod3 with about 10 different "Prod4"

So each field is a cascadint to the field before...

I got about 8000 Records from at the moment three different Suppliers.
As the suppliers have different "Set Up" so to say how they give us the Data I extracted from one Supplier all Products in put it into a new Table

Just so I limit Records or Group Records with the same Structure..this I just us as a temp Table.

There are about 1100 Records to this table .

============================

Some other Suppliers have just a Descriptions and no "Prod1 , Prod2, Prod3, Prod4" which is like Categories..

They or some again have like Desc1, Desc2, Desc3, Desc4 wich is just more information regarding the "Product"

Some other Suppliers just have One Description.

===========================

So what I did is try to set up a Table Structure like

RootCategory.................Suppliers, with a Junction Table SupplierRootCategory.........
Something like
SupplierA............Humidifiers
SupplierB.............Maschinery
SupplierB.............Equipment
SupplierC.............Chemicals

Attached a Screenshot to ProdCategories...

But it does get quite messy or alot of work to maintain I guess to set it up like that and when other Suppliers don't have all of those Categories then those would hav NA or something like that for a "Type Of ProductCategorie"

I am just a little lost on how I can put it all together without haveing to make a ton of new forms or to filter and get Data from the Products.

Hopefully it is understandable what the issue is or how to work with it the best?

Cheers

 

[silentwolf]

Hi,

Just an update to my structure,
I know you guys don't like my field names but I hope it is still ok to see what the structure does.

With that structure the first 1111 Records are working fine. So first step done I guess.

When I like to update the ProductModels Table I am not sure how to go about it?

with this SQL I filter some records just standart...

SELECT Products.ProdID, Products.ProdDescription
FROM Products LEFT JOIN (Models RIGHT JOIN ProductModels ON Models.ModID = ProductModels.ProdModModIDRef) ON Products.ProdID = ProductModels.ProdModProdIDRef
WHERE (((Products.ProdID)>1111) AND ((Products.ProdDescription) Like "*schraube*"))
ORDER BY Products.ProdID;

I know that the ModID Should be in this case "18"

So how can I update ProductModels Table with all the records found in the above query and add the ModID to it?

Is there a easy way of finding the wanted ModelID and then update it?


Can someone help please?

 

[silentwolf]

You're right, we don't like your naming schema.

well I tried with other schemas and then you have same names on different tables and it makes it hard to know where the fields belong to.

I got this shema not from myself a "instructor" on a learning platform uses it and well it does make sence in a way and I just used it too.

Each level of breakdown has a meaning. What is it?

I am not to sure what you mean by that Pat sorry?

I will send you an example once I have sorted a little better myself..

P.S. Also try to get into naming it like you guys

Thanks

 

[silentwolf]

Hi again,

an updated version is attached and a bit more userfriendly in nameing the fields so I hope for some comments.

I did include also a Table called PartPriceList which is not linked and it shows a table how I receive from the supplier for Parts.

Importend is that we are able to update new Prices for Parts and for Models in the Database.

The "Modelpricelists" are simmilar to the "PartPriceList" but have some extra Descriptions in some Suppliers lists we receive and not included in that database.

In the database you can see that ModDescriptions or PartDescriptions are included.

With those tables I should be able to update the received Pricelists easily enough to have
Desc1, Dec2, Desc3 into one field and then feed the Part or ModDescription tables with this extra Descriptions into one single field.

Hope this makes more sense now and as I said would love to hear some comments on the structure.


Cheers

 

[gemma-the-husky]

I don't know if Pat touched on this, but there's a difference if your prodcat1, prodcat2 etc mean different things.

If a product is just included in multiple categories, all of which are just general categories, than you need to list all the categories in a "categories" table, and then you need a junction table to manage the links.

So you get
Products(productID, productName)
categories(categoryID, categoryName)
and ProductCategories(productID, categoryID)

and a product can be in any number of categories. 0,1 or more than 5.

However, if your Product Categories ProdCat, ProdCat1, ProdCat2 etc are different attributes of a product that need to be kept separate, then you need a different structure.

And also different: if membership of ProdCat2 relates to membership of prodcat1, and so on, then it's nothing to do with the product itself, and you need a different structure again.

So can you give us an example of the categories you are managing for a couple of products?
Also, are all categories completed for all products, or not necessarily.

 

[silentwolf]

Hi thanks for your reply gemma-the husky !

If a product is just included in multiple categories, all of which are just general categories, than you need to list all the categories in a "categories" table, and then you need a junction table to manage the links.

Unfortunatelly not.
The "PartPriceList" Table is a modified Excel Sheet we receive from the Supplier.
I updated this Excel File to "Prod1, Prod2, and so on in the table as I created the structure I named those in RootCategories, Categories, SubCategories, and Model and have those cascading.

A Screenshot attached shows how it would relate in the Database.

This PriceList which is just one of few different PriceLists we receive from different Suppliers all again in Excel Worksheets.

In Attachment Data_02 shows a different Suppliers PartList the only thing missing are the prices in that Screenshot.

As there are thousens of those Articles I was hoping to set up a why to be able to find those Articles easily.
In this screenshot there is all information in one Column again that is how we get those Prices and Lists.

In this Column there is information on what Model(s) this Part does belong to and a description on what this Part is,

For the first line

13241203

*P* Beleuchtung Adria/SenseEase/Reviva II/Laola II/Ergo+ / Licht in farbig / Bei Veredelung muss Gehäuse 10700401 zu R&R

"*P* = not sure lol
Beleuchtung = Lighting
Adria = "Lighting Model"
SenseEase; Reviva 2, Laola 2, Ergo + are "Bathtub Models"
Licht in Farbe = Description Light is in Color
Bei Veredelung muss Gehäuse xxx = Info about useage

Data_03
Another Price List with Beschreibung, Beschreibung2, Beschreibung3, Beschreibung4..

Honestly I am not sure what that all means but I belive my friend should know lol..

Beschreigung = ORing or Silikon Typ of Seal and so on..

So how can you find those Products or Articles easily?

There must be a way of differentiate or group them so you dont need to look for hundreds or even thousends of Records.

Because I am sure he does not know how the Supplier called the Product or Article he needs to find.

Or he does not know how the Supplier Called the Article in the first place.

I mean of course he can filter I am aware of that but just wondering how to be able to have a set up so you can
1. Import and Update New Prices
2. Find "Models" like Bathtub "SenseEase"
3. Find "Parts" related to SenseEase and of course those prices.

The following should be managed by the Database.

Customer X has Bathtub " SenseEase" and needs to get it fixed "meaning this bathtubs have some functions like whirljets or lights or sound system...
So his SenseEase has a issue with the SoundSystem " no Sound" is comming out of the little box.
And also the Lighting is not the right color or whatever..

So my friend needs to fix the issue. Therefor he needs Parts.
Perhabs new Cables, New Lighting

In the same time he does maintainance work for this bathtub.. so maybe he needs furhter parts related to this particular Model "SenseEase"
Or even a new pump just incase there is an issue there. Or is an old model...

So he needs to write an "Quote" for "Customer X" where he can list all the parts what may be necessary to fix those issues.
If he gets the "Contract" or Order.. then he might need to order parts so he can complete this tasks.

Either a Quote or a Order/Invoice needs to find Parts in any case. And most likely he needs to find it easily.
So listing just the "Products" or "Articles" Same but differnt naming from different Suppliers will not do the trick.
Meaning if I put all the different "PriceLists" into one table there will be many blanks for different eiter descriptions or "Categories"
and it is nearly inpossible to find articles what he needs to complete the job.

So he will not use it and my work is for the pin which is not really what I would like.

Well therefor he should be able to look into "Model" SenseEase" find perhaps erlier "maintainence work" where he can find all parts needed or create from scratch a "Quote" or Invoice if confirmed as a "Contract"

This got quite long now but I hope this makes bit more sense now and again I would appreciate some thoughts on ths topic.

Cheers

 

[gemma-the-husky]

Well the important thing is that you understand exactly what your data means, and how to split it.
are all of the analysis columns related to the products or is the right most column related to the preceding column? and so on.

 

[silentwolf]

Hi again,

you both for taking the time to explaining and helping me with my issue.
Unfortunatelly I am out and about again with work and will be back later today.

So will need to work through your suggestions and questions a little later.

Thanks for your input and help it is much appreciated!




Cheers