Split Table into different Tables (1 Viewer)

[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
...

 

[Pat Hartman]

I don't have code. It is usually done with queries. Define tblCategory with CatID as the autonumber and a field named CatName. CatID is the PK but CatName needs a unique index. You would start with five queries to build the Category table. The queries will produce errors regarding duplicates but ignore them. After you've run the five queries, you will have all possible Category names.
qry1
Insert into tblCategory (CatName)
Select ProdCat from YourTable Where ProdCat Is not null;
qry2
Insert into tblCategory (CatName)
Select ProdCat1 from YourTable Where ProdCat1 is not null;
qry3
Insert into tblCategory (CatName)
Select ProdCat2 from YourTable where ProdCat2 is not null;
etc.

To create the tblProdCat which is the junction table, you need five more queries to append the rows to the ProdCat table.

qry1
Insert into tblProdCat (ProdID, CatID)
Select YourTable.ProdID, tblCategory.CatID from YourTable Left Join tblCategory on YourTable.ProdCat = tblCategory.CatName;
qry2
Insert into tblProdCat (ProdID, CatID)
Select YourTable.ProdID, tblCategory.CatID from YourTable Left Join tblCategory on YourTable.ProdCat1 = tblCategory.CatName;
qry3
Insert into tblProdCat (ProdID, CatID)
Select YourTable.ProdID, tblCategory.CatID from YourTable Left Join tblCategory on YourTable.ProdCat2 = tblCategory.CatName;
etc.

Usually, there's only a few instances of the fields so I'm inclined to just use the curmudgeon technique and build queries. You can do it with ONE query that you modify with code if you prefer.

 

[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?

 

[Pat Hartman]

You're right, we don't like your naming schema. Each level of breakdown has a meaning. What is it? Can you post a db with just the five category tables in it fully populated.

 

[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.

 

[Pat Hartman]

The difference in language is hampering my ability to come up with descriptive words for the various levels. I'm guessing that the best solution will be to NOT store the breakdown on the order details but to use a popup form to find the part number to order and to only store the PartID (your autonumber) with the vendor's PartNum being the display field for the combo in the work order details.

Different products and different vendors will have a different way of describing the product. We'd probably need to see a dozen different product lists from different vendors to get a bead on what will be best. So, given my limited knowledge of YOUR problem but having experience with similar problems, I would create the part table to identify the vendor, manufacturer, part number, description and then a bunch of columns for attributes. Since the attributes would have different names for different products, I doubt you could come up with any meaningful standard so essentially, you want a flat table that mimics the spreadsheets. You can then load the spreadsheets into a single table. You just have to allow for the maximum number of attributes. This is a case where you can't actually normalize the data and I think trying to do it will cause a proliferation of tables to support different product types and you want to avoid that at all costs. It is not YOUR data. You are not going to update it. You will replace it in total when the list expires. so normalization isn't really necessary. This part of the app is more like a data warehouse with static information. Those are often flattened for ease of use.

You will need a column in the Product table that also identifies a specific price list. That means when the list needs to be replaced, you can archive the old list and import the new list. That also means that you need to exclude the archived data (you can't delete it because you will have work orders that point to it) from most queries and make it optional to view the old data on the search form. You might use an AddDT and ArchiveDT rather than a flag. That will give you more flexibility in the search. Over the years, you'll end up with a lot of old stuff but the user might want to look at the current list and the most recent list for comparison. He probably won't want to search further back but with the dates you will be able to control that. Your search form will need to allow the user to search the old stuff for reference but not allow him to select the old product for a new work order.

Then the popup form provides filtering so the user can filter the columns one at a time to reduce the list until he zeros in on what he wants. You can code your own but I wouldn't. I would create an unbound main form so you have a place for options and buttons and a subform in DS view for the products. Then the user can filter by any of the columns to reduce the list. Dbl-Click on an item could be the select trigger.

 

[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