Parts and Models List

[mreinsmith]

I've been reading about how to use a junction table to do this but I just can't seem to get it straight

I have a list of parts and a list of models

Can someone walk me through the steps of creating the relationships and creating the form for data entry

but most importantly I need to be able to import a csv with that data

If there is a good post with a walk through, maybe point me to that?

Thank You

Matt

 

[MadPiet]

How are Parts and Models related in the real world? A Model consists of one or more Parts?

Model--(1,M)--ModelParts--(M,1)--Part
Say the primary key of Model is ModelKey and it's a unique integer. And Part's Primary Key is PartKey. So, by definition, PartKey and ModelKey are both unique. Then the primary key of ModelParts is the combination of those two keys (ModelKey, PartKey)

Creating the relationships? go into relationship view and drag & drop the ModelKey from Model to ModelParts on top of ModelKey in ModelParts. same with Part-->ModelParts.

 

[MajP]

The big question is if this is simply?

Model 1
-- part 1
-- part 2
-- part 3

Model 2
-- part 1
-- part 4
--part 5


or
model 1
-- part 1
-- Part 2
-------- Sub part 1 to Part 2
-------- Sub part 2 to Part 2
--Part 3
-------- Sub Part 1 to Part 3

The 1st is simple the latter can get harder.

The first can be done with a traditional Master form with the Model and a subform to enter the parts. The latter can be done that way but gets real complicated to visualize once you have a lot of sub levels. I do this with a treeview to allow unlimited amount of Model, part, sub part, sub sub part, etc.

Solved - Products - Sub Assemblies - Assemblies

Its been a while but I wrote a system many years ago - we used the same principle as outlined by arnel

www.access-programmers.co.uk

 

[jdraw]

Matt,
I think we need more info about your environment and your proposed project/database.
Here is an older draft picture that may be relevant to your requirement. Assembly is a junction table. FinalProduct would equate to your model, if I understand your post.

MajP's example allows for many parts, sub assemblies etc.

 

[mreinsmith]

For the moment I would like to keep it a simple 1 level Part # to Model #. It's going to be in a separate database all by itself for testing

That alone would give me a huge benefit

For Example.

Part #s have a UID, Part #, Manufacturer, Description
Model #s have the same thing; UID, Part #, Manufacturer, Description

When I download the data, It's for a single part number and the models it fits.

-Part # 650298
- Model # X100
- Model # X200
- Model # X300

I usually just put this together in a spreadsheet. Columns are Part #s, rows are Models #s.

790698	84004658	691053
401577	40F777	4035A7
4035A7	40G777	404577
404577	40H777
4045A7	4045A7

​

As simple brute force way would be to just drop this into a DB table. But I'm thinking that would be a mess to deal with over the long term

What I would like to do is import the info AND/OR put the part number in a Text box and then paste all the models into a text box

Hit the button and all the models get added to that part without creating duplicates of model numbers or part numbers

That's why I keep thinking about a junction table.

Let me know what info you would like next or if this is enough

 

[MadPiet]

If you put that in Excel, you can unpivot it and you would end up with (ModelNo, PartNo), which would be useful. Then that's your junction table. Ideally, it would include a Quantity of each PartNo for each model, so it would be (ModelNo, PartNo, Quantity)

Part #s have a UID, Part #, Manufacturer, Description
Model #s have the same thing; UID, Part #, Manufacturer, Description

Wait, are these subassemblies? This sounds an awful lot like a Bill of Materials question. (where some "components" are subassemblies consisting of one or more "components").

 

[MajP]

import a csv with that data

If you have a CSV seems kind of silly to be pasting things into a textbox when you can simply import into a properly normalized table at a click of a button. Might take some code depending on what the csv looks like. Can you show an example CSV?

 

[Mark_]

For the future, will you also need to track parts by serial number or lot/batch?

 

[mreinsmith]

If you put that in Excel, you can unpivot it and you would end up with (ModelNo, PartNo), which would be useful. Then that's your junction table. Ideally, it would include a Quantity of each PartNo for each model, so it would be (ModelNo, PartNo, Quantity)

Part #s have a UID, Part #, Manufacturer, Description
Model #s have the same thing; UID, Part #, Manufacturer, Description

Wait, are these subassemblies? This sounds an awful lot like a Bill of Materials question. (where some "components" are subassemblies consisting of one or more "components").

Don't want to do the BOM yet just want to find out what part numbers go with what models and vise versa

 

[mreinsmith]

If you have a CSV seems kind of silly to be pasting things into a textbox when you can simply import into a properly normalized table at a click of a button. Might take some code depending on what the csv looks like. Can you show an example CSV?

I shouldn't have said the csv, I'm not doing that anymore, it was the first time I tried to do this and completely over complicated it and ditched it

Copying and pasting would be easier for small sets of data

 

[MadPiet]

I have a list of parts and a list of models

Can someone walk me through the steps of creating the relationships and creating the form for data entry

but most importantly I need to be able to import a csv with that data.

What do your CSV files look like (what's the structure?) How many files you do you need to import and how often?

Assuming you have a "ModelParts" table that consists of (ModelNo, PartNo) at least and maybe some descriptive attributes (like Quantity), then your tables would be

Model(ModelNo, ModelName...) --(1,M)--- ModelPart(ModelNo, PartNo, [Quantity]) ---(M,1)-- Part(PartNo, Name, description...)

bold = Primary Key (unique). As for importing, I would import into a set of intermediate tables (maybe in another database) and find all the records in ModelPart that don't have corresponding records in Model and Part. Then when I found there were none, then I'd append those to the final tables (but you have to import into the "one" side(s) first... so Model and Part first, and then you can import into ModelPart.

 

[MajP]

Copying and pasting would be easier for small sets of data

What does that thing look like that you would be copying and pasting from? Copying and pasting the models into a single text box would only make sense if they came in a long list already. If you start with individual models you would simply paste them into the correct table.
In other words it would make no sense to type a long list of model numbers and then copy those and paste into a text box and then write code to split them into individual records, when it would be way simpler to type them in directly. However if I have an import already that is a long list of models then it might make sense to have a utility to split them up. So what are you starting with? If that is a blank slate them simply select the models from a combo box into the subform of a main form.
Since this is a many to many you can have it go either way or both ways.
You can have a main form with a model, and add all included parts as child records in a subform. Or you can have a main form with a Part and add all supported models underneath in the subform.

 

[mreinsmith]

What do your CSV files look like (what's the structure?) How many files you do you need to import and how often?

Assuming you have a "ModelParts" table that consists of (ModelNo, PartNo) at least and maybe some descriptive attributes (like Quantity), then your tables would be

Model(ModelNo, ModelName...) --(1,M)--- ModelPart(ModelNo, PartNo, [Quantity]) ---(M,1)-- Part(PartNo, Name, description...)

bold = Primary Key (unique). As for importing, I would import into a set of intermediate tables (maybe in another database) and find all the records in ModelPart that don't have corresponding records in Model and Part. Then when I found there were none, then I'd append those to the final tables (but you have to import into the "one" side(s) first... so Model and Part first, and then you can import into ModelPart.

I like that, going to see how that works

 

[mreinsmith]

What does that thing look like that you would be copying and pasting from? Copying and pasting the models into a single text box would only make sense if they came in a long list already. If you start with individual models you would simply paste them into the correct table.
In other words it would make no sense to type a long list of model numbers and then copy those and paste into a text box and then write code to split them into individual records, when it would be way simpler to type them in directly. However if I have an import already that is a long list of models then it might make sense to have a utility to split them up. So what are you starting with? If that is a blank slate them simply select the models from a combo box into the subform of a main form.
Since this is a many to many you can have it go either way or both ways.
You can have a main form with a model, and add all included parts as child records in a subform. Or you can have a main form with a Part and add all supported models underneath in the subform.

I did one today from a parts site that had 1728 models for 1 part, luckily I was able Drag down and copy them

 

[mreinsmith]

Going to try to put together a few examples tonight of ones I've done manually

 

[MadPiet]

One thing you can do that's super easy is to create a linked table and connect to one of the CSV files. You'll have to define data types, but then you can just query and append etc.

Doing this manually sounds really painful. Post some sample data (doesn't have to be real... I don't want anything confidential!) But parts numbers isn't super identifiable. What does the raw data look like? if you can post a sample, I'd bet I can get it working. Might require some Excel trickery.