DB Design & Table Setup Help

[jzwp11]

As far as I know, there is no tool, so it is up to you. As we have laid out the tables earlier in this thread, you should be OK. Are there any other tables that you did not mention?

[Zanzibarjones]

Oh gosh yes, 1 more.
A table for tracking my own inventory. The table would not need to add any fields that don't exist in other tables already. It would just be for storing my counts of parts (PartsColorsID) that I have.

[jzwp11]

You would need at least 1 other table to track the movements in and out of inventory assuming that the amount you have is somewhat independent from what was supposed to be in each set.

tblPartTransactions
-pkPartTransID primary key, autonumber
-fkPartColorID foreign key to tblPartColor
-dteTrans (transaction date)
-Qty
-fieldtosaywhetherinventoryiscominginorgoingout (or you can just use positive quantities or negative quantities without this field).

I'm not sure how you go about adding/subtracting parts from inventory. Do people by them from your or do you buy them from others? You may want to look at Allen Browne's site for more on inventory control.

[Zanzibarjones]

Well the Inventory thing would be more for tracking what sets I could build with my current inventory. And yes I imagine at some point and time I will be adding and subtracting parts from my own inventory. So I will check that link out too.

[jzwp11]

If you have an inventory & since you will already have the parts applicable to each set, you can compare the two to see if you have the necessary parts in inventory to construct the set.

[Zanzibarjones]

That is awesome. Ok, I am going to work on his for a bit. If I get stuck, mind if I come back with some more questions? I don't want you to do it for me, just kind of kick me in the right direction

[jzwp11]

If I get stuck, mind if I come back with some more questions?

I do not mind at all!

[Zanzibarjones]

Ok, so after looking at my tables a while, and figuring out how the data will go into those tables and be related, I came to a question.

We were talking about adding data through a Form. But if I had the data already in an excel spreadsheet, I could import it into a separate table and use and append query to update or import it into the right table.

I will start with the Parts Table. I have all the parts already in a sheet separated into PartNum & PartName (Description). Importing into it's own table is fairly easy.

Question: I don't need to run the append query from a form do I? I can write the query and just run it, to append the data into the right table, correct?

[jzwp11]

We were talking about adding data through a Form. But if I had the data already in an excel spreadsheet, I could import it into a separate table and use and append query to update or import it into the right table.

An update query is a different animal. You want the append query. You can start with a normal SELECT query (to query the data in the imported table). Once you have the data you want selected, change the query type to append, choose the destination table and correlate the fields of the imported table to those of the destination table. Then run the append query. More than likely, you will not need to save the append query once you have run it. I would check at each stage to make sure records are being appended correctly. You may also want to make backup copies at various stages so that if an error occurs you do not have to go through the entire process again.

Question: I don't need to run the append query from a form do I? I can write the query and just run it, to append the data into the right table, correct?

Correct, you do not need any forms when just migrating the data.

[Zanzibarjones]

Ok, SO I did my first append query, started with Categories, and it worked fine, no issues.

Now I am going to append the tblParts. My Sheet I am importing has the parts listed, and has 3 columns of data for each part. The Part Number, which will append to tblParts.PartNum, the Description, which will append to tblParts.Description, and I have Category, which will append tblParts.CategoryID.

But I have the Categories in the sheet set up as their CategoryName, not CategoryID. So how will that append?

Or is it a 2 step process. First append the tblParts with just the PartNum and PartName
Then
Append the CategoryID with the CategoryName by matching it to the tblCategories.CategoryName?

[jzwp11]

Create a Select query that includes the imported parts table and your category table (by adding both to the upper pane of the design grid view). Join the tables via the category name fields (the names have to match exactly). In the grid, select the part number field of the imported table, the part description field of the imported table and the primary key field of the category table (pkCategoryID?). Change the query type to an append query, select your parts table as the destination table and correlate the 3 fields just selected to the corresponding fields in the parts table.

Have you made sure you don't have any duplicates in your import table (use the find duplicates query wizard to check)

[Zanzibarjones]

Create a Select query that includes the imported parts table and your category table (by adding both to the upper pane of the design grid view). Join the tables via the category name fields (the names have to match exactly). In the grid, select the part number field of the imported table, the part description field of the imported table and the primary key field of the category table (pkCategoryID?). Change the query type to an append query, select your parts table as the destination table and correlate the 3 fields just selected to the corresponding fields in the parts table.

Have you made sure you don't have any duplicates in your import table (use the find duplicates query wizard to check)

1) Yes I have checked for dupes already, and we are good to go.
2) I don't understand the part about using the CategoryID to correlate. PartNum and PartName, ok I got that. But the CategoryID is messing with me a bit.

My Excel Spreadsheet has these exact 3 columns:
PartNum Description Category
10012 1 x 1 x 1 Brick
10013 1 x 2 x 1 Brick
10013 4 x 12 x 1 Plate

So I do not see how the Words like 'Brick' & 'Plate' will correlate using the CategoryID instead of the CategoryName.


*** Edit: OOoooooo I see what you mean..... Nevermind

[jzwp11]

From our earlier discussion

tblParts
-PartID pk
-PartNum
-PartName
-fkCategoryID foreign key to tblCategory

tblCategory
-CategoryID pk
-CategoryName

spreadsheettable
PartNum
Description
Category


You would make the join between the categoryname field of tblCategory and the category field of the spreadsheet table. The SQL view of the select query will look like something like this:

SELECT partnum, description, CategoryID
FROM spreadsheettable INNER JOIN tblCategory ON spreadsheettable.category=tblCategory.categoryName

After you get the above, change the type to an append

[Zanzibarjones]

Yes, Ok I see now. The join is what associates the 2 tables. I got it now. I missed that one little thing and it screwed up my whole way of thinking. Amazing how easy that was. Boy that is a heck of a lot easier than going through every record and selecting the proper categoryID.

[jzwp11]

Glad you got it; much easier!