DB Design & Table Setup Help

[Zanzibarjones]

Can you use that inner join for more than two tables when building a query?

[Zanzibarjones]

Now, in the case of appending the tblSets table, I have the sets in chunks, several excel spreadsheets. I was thinking I was going to be building tables for each theme, but I am not doing that now.

So, I can run several appends to get the data into the tblSets, correct?
Just like later down the road, if I want to add 20 new sets, I can just use an append query, and it will add the data to the table. Is that correct?


***Edit- Ok it worked. I figured it out. As long as I did not append the ID field (the primary key from the import to the sets table) it worked

This is making me a very happy camper

[jzwp11]

You can use multiple tables or queries as part of a query. You just have to make sure there are logical joins between them. Under some circumstances you can take 2 unrelated tables/queries to get a Cartesian Product of the data in both tables. That comes in handy once in a while.

It sounds like you have the migration under control!

[Zanzibarjones]

Yes the imports have gone according to plan.

But now I have these parts lists for each set saved in excel using coloumn names PartNum, ColorName, Qty, and SetNum.

Will I have to write 2 separate queries in order to append the tblPartColorSets?
One query using the tblPartsImport and inner join PartNum to tblParts and ColorName to tblColors. And then a second query to inner join tblPartsImport to tblSets using SetNum?

Then append those 2 queries to tblPartColorSets?

*** Edit: Ok I just figured out, the above question is moot. There will be a lot of unnecessary redundancies doing that way. tblPartColors (the combination of tblPart & tblColors) will need to have entries just creating unique ID for every part color combination, that Lego currently makes.

That way, when I enter the parts list for each set, I can grab the unique PartColorID from tblPartColor and just set the qty for that piece int hat set, because some sets will use the same part in the same color, just a different qty.

So I am not sure if I can do that through excel sheets listed above and imprt it properly into the right tables. It may be easier to just enter the data via a form. Any suggestions?

[jzwp11]

You will need to get the themes into their table. Have you already?

Does your input table that has the setnum also have the theme? Do you have a spreadsheet that correlates setnum and theme? If not, then we can leave blank for now; you will have to enter it later either manually or with an update query (edits existing records)

tblSets
-SetID pk
-SetNum
-SetName
-YearReleased
-ThemeID


Does your import table include the setname, yearrelease in addition to the setnum?

You can select just unique setnum and setname &yearreleased in available by using the DISTINCT key word.

SELECT DISTINCT setnum, setname, yearrelaesed FROM importtablename

Change to an append query and append to tblSets

You can use the same approach to populate the color table. Then you can join the import table to the part number table and the color table to populate tblPartColors


Then you can join the import table to tblPartColors via both the part number & color tables and also join the import table to the set table to get ready to append tblSetPartColor. You would bring in the quantity field as well.

[Zanzibarjones]

Yes, all the base data is in the right tables.

Man I wish I could show you my layout for these append queries. I think they are right, it seems to be working. I could be wrong, and I will have to double check, but I think they are right.

[jzwp11]

Man I wish I could show you my layout for these append queries.

You can post the SQL text of the queries. Switch to SQL view, copy and paste to your post.

[Zanzibarjones]

Ok cool, here it is...

Query 1:
INSERT INTO tbl_PartsList_Import2 ( SetNum, PartID, ColorID )
SELECT tbl_PartsList_Import.SetNum, tblParts.PartID, tblColors.ColorID
FROM (tbl_PartsList_Import INNER JOIN tblParts ON tbl_PartsList_Import.PartNum = tblParts.PartNum) INNER JOIN tblColors ON tbl_PartsList_Import.ColorName = tblColors.ColorName;

Query 2:
INSERT INTO tblPartColors ( PartID, ColorID )
SELECT tbl_PartsList_Import2.PartID, tbl_PartsList_Import2.ColorID
FROM tbl_PartsList_Import2;

Query 3:
INSERT INTO tblPartColorSet ( PartColorID, SetID, Qty )
SELECT tblPartColors.PartColorID, tblSets.SetID, tbl_PartsList_Import.Qty
FROM (((tbl_PartsList_Import INNER JOIN tblSets ON tbl_PartsList_Import.SetNum = tblSets.SetNum) INNER JOIN tblParts ON tbl_PartsList_Import.PartNum = tblParts.PartNum) INNER JOIN tblColors ON tbl_PartsList_Import.ColorName = tblColors.ColorName) INNER JOIN tblPartColors ON (tblParts.PartID = tblPartColors.PartID) AND (tblColors.ColorID = tblPartColors.ColorID);

Those 3 seem to get my parts lists from Excel into tblPartColorSet. I just need to verify the data somehow. I think I will be able to do that through another query, but I will need to display the Name fields, not the ID fields.

[jzwp11]

I had some concerns, Query 1 and Query 2 are appending to tbl_PartsList_Import2 but Query 3 is pulling data from tbl_PartsList_Import.

Additionally, Query 1 is using SetNum whereas Query 3 is using SetID was this intentional?


Regarding Query 2, if multiple sets have the same part/color you will have duplicates in tblPartColor. This is where the DISTINCT key word is necessar

INSERT INTO tblPartColors ( PartID, ColorID )
SELECT DISTINCT tbl_PartsList_Import2.PartID, tbl_PartsList_Import2.ColorID
FROM tbl_PartsList_Import2;

[Zanzibarjones]

I had some concerns, Query 1 and Query 2 are appending to tbl_PartsList_Import2 but Query 3 is pulling data from tbl_PartsList_Import.

Additionally, Query 1 is using SetNum whereas Query 3 is using SetID was this intentional?


Regarding Query 2, if multiple sets have the same part/color you will have duplicates in tblPartColor. This is where the DISTINCT key word is necessar

INSERT INTO tblPartColors ( PartID, ColorID )
SELECT DISTINCT tbl_PartsList_Import2.PartID, tbl_PartsList_Import2.ColorID
FROM tbl_PartsList_Import2;

I know that I really need to learn the query syntax. Can you suggest some good learning material (video course, trial and error, closing my eyes and praying...)

[jzwp11]

I didn't know of any good SQL sources right off hand but I did some internet searching and found this site that might be helpful.

You will have to run query 2 first in order to get the parts & their colors into tblPartColors because you need pkPartColorID to be created before you can append to tblSetPartColors. I'm just not sure whether tbl_PartsList_Import2 or tbl_PartsList_Import contains the correct data. Assuming that tbl_PartsList_Import is the correct table, then the query would look like this (not tested)

INSERT INTO tblPartColors (PartID, ColorID)
SELECT DISTINCT tblParts.PartID, tblColors.ColorID
FROM (tbl_PartsList_Import INNER JOIN tblParts ON tbl_PartsList_Import.PartNum = tblParts.PartNum) INNER JOIN tblColors ON tbl_PartsList_Import.ColorName = tblColors.ColorName

Then you can create another query that uses tblParts, tblColors, and tblPartColors (make the joins between pkPartID and fkPartID and pkColorID and fkColorID) and your tblSets. Now add the import table and join partnum, colorname and setnum of the import table to their respective corresponding fields in their primary tables (tblParts, tblColors, tblSets). Your SELECT clause should again include the DISTINCT key word to prevent any duplicates.

[Zanzibarjones]

Ok great. Going to test that in a little while. Thanks for the link, but honestly, I have learned more from you in the past week then I have from anywhere else. Maybe you should open your own teaching site

SELECT DISTINCT.... got it.

[jzwp11]

Unfortunately, I don't have the credentials to be able to teach, so I just help out here.

[Zanzibarjones]

I didn't know of any good SQL sources right off hand but I did some internet searching and found this site that might be helpful.

You will have to run query 2 first in order to get the parts & their colors into tblPartColors because you need pkPartColorID to be created before you can append to tblSetPartColors. I'm just not sure whether tbl_PartsList_Import2 or tbl_PartsList_Import contains the correct data. Assuming that tbl_PartsList_Import is the correct table, then the query would look like this (not tested)

INSERT INTO tblPartColors (PartID, ColorID)
SELECT DISTINCT tblParts.PartID, tblColors.ColorID
FROM (tbl_PartsList_Import INNER JOIN tblParts ON tbl_PartsList_Import.PartNum = tblParts.PartNum) INNER JOIN tblColors ON tbl_PartsList_Import.ColorName = tblColors.ColorName

Then you can create another query that uses tblParts, tblColors, and tblPartColors (make the joins between pkPartID and fkPartID and pkColorID and fkColorID) and your tblSets. Now add the import table and join partnum, colorname and setnum of the import table to their respective corresponding fields in their primary tables (tblParts, tblColors, tblSets). Your SELECT clause should again include the DISTINCT key word to prevent any duplicates.

Ok I have tested the frist part of this query, and it is allowing dupes for some reason. Could it be because the tblPartColors is making a Unique ID for every entry into the table?

[jzwp11]

tblPartColors is only the destination table, so you want a unique ID to be created for the primary key field. But in using the DISTINCT key word, you should not have more than one incoming record with the same combination of partID and colorID.