Table structure messed up?

[plog]

You've got to stop thinking about everything at the order level. Your database is a heirarchy, similar in structure to a family tree. Foam, Bags, Plugs and Paint are at the bottom; the next level up is Parts and off to the side are Customers, on top of that are Orders:

                               Orders
                   Customers            Parts
                                  Foam, Bags, Plugs, Paint

When I ask if a Part can have multiple foam, or bags or plugs; I mean is there at least one Part (I don't care which one) that can have multple of those, do not talk about orders. Bags, Foam and plugs are indirectly related to Orders (via Parts). They are directly related to Parts so that's where we need to establish exactly what type of relationship those entities have to Parts.

Can you put some data in Foams, Plugs Bags and Paint and upload a new database?

 

[jesswoo]

You've got to stop thinking about everything at the order level. Your database is a heirarchy, similar in structure to a family tree. Foam, Bags, Plugs and Paint are at the bottom; the next level up is Parts and off to the side are Customers, on top of that are Orders:

                               Orders
                   Customers            Parts
                                  Foam, Bags, Plugs, Paint

When I ask if a Part can have multiple foam, or bags or plugs; I mean is there at least one Part (I don't care which one) that can have multple of those, do not talk about orders. Bags, Foam and plugs are indirectly related to Orders (via Parts). They are directly related to Parts so that's where we need to establish exactly what type of relationship those entities have to Parts.

Can you put some data in Foams, Plugs Bags and Paint and upload a new database?

Ok I took out table plugs. After talking to my boss he doesn't think it is necessary to keep an inventory of them. I put in some info for foam, bags, and paint.

Thank you so much for all of this help I am seeing now that i was looking at this the wrong way!

 

[plog]

2 questions:

A. In the foam table that designates a huge roll, correct? And when you use it for a part you tear off part of that roll, the size of which depends on the part it is used for, correct?

B. When one part is packaged can it use more than one bag or more than one type of foam? Are there any parts that use no foam and/or bags?

 

[jesswoo]

2 questions:

A. In the foam table that designates a huge roll, correct? And when you use it for a part you tear off part of that roll, the size of which depends on the part it is used for, correct?

B. When one part is packaged can it use more than one bag or more than one type of foam? Are there any parts that use no foam and/or bags?

A. In the foam table that designates a huge roll, correct? And when you use it for a part you tear off part of that roll, the size of which depends on the part it is used for, correct?
Yes, each part has a specific length for foam strips


B. When one part is packaged can it use more than one bag or more than one type of foam? Are there any parts that use no foam and/or bags
Each part will only get 1 sheet of foam or 1 bag. Yes, there are some parts that do not get either.

 

[plog]

Attached is what I believe is the correct structure for your database. I consolidated tblBags and tblFoam into tblPackingMaterials; I created tblPartPacking to establish the relationship between tblParts and tblPackingMaterials; I created tblOrders; I created tblOrderParts to establish the relationship between tblOrders and tblParts; I created the relationships among all the tables and I created a master qeury to bring in all the data.

You most likely will have to add a few fields to tables, the key is knowing which table to add that field to. Look it over and let me know if you have any questions.

 

[jesswoo]

Attached is what I believe is the correct structure for your database. I consolidated tblBags and tblFoam into tblPackingMaterials; I created tblPartPacking to establish the relationship between tblParts and tblPackingMaterials; I created tblOrders; I created tblOrderParts to establish the relationship between tblOrders and tblParts; I created the relationships among all the tables and I created a master qeury to bring in all the data.

You most likely will have to add a few fields to tables, the key is knowing which table to add that field to. Look it over and let me know if you have any questions.

Thank you so much! this is awesome and exactly what I needed. Thanks for taking the time to help me with this!

I understand where I was going wrong with what I had and it was so frustrating, I have been working on this a month...

 

[jesswoo]

Attached is what I believe is the correct structure for your database. I consolidated tblBags and tblFoam into tblPackingMaterials; I created tblPartPacking to establish the relationship between tblParts and tblPackingMaterials; I created tblOrders; I created tblOrderParts to establish the relationship between tblOrders and tblParts; I created the relationships among all the tables and I created a master qeury to bring in all the data.

You most likely will have to add a few fields to tables, the key is knowing which table to add that field to. Look it over and let me know if you have any questions.

I do have 2 questions, How do I create a new order? I know i will create a form but from which table?

Also, I wanted to be able to filter the orders by status and department. Before I just created lookup fields in the orders table and filtered the orders with a query that specified which orders to show. Can I add a status field with a lookup field limited to Active, On Hold, Complete and a department field limited to Line, Batch on this orders table and be able to make a query? Its just a visual thing.

 

[plog]

Ultimately you will have a form for every table, you shouldn't start with an orders form, but build forms from the bottom of the heirarchy up (the one I posted a few posts back). This is the order I would do it in:

1. tblPackingMaterial
2. tblPaint
3. tblPaint
4. tblPartPacking*
5. tblCustomers
6. tblOrders
7. tblOrderParts*

* tblPartPacking and tblOrderParts are junction tables (http://en.wikipedia.org/wiki/Junction_table) which means they will most likely be sub forms on one of the 2 forms whose tables they join.

How will status be determined--can you do it by looking at the data itself? Do you want to track when an order was moved from status to status? Where does department fit in all of this?

 

[jesswoo]

Ultimately you will have a form for every table, you shouldn't start with an orders form, but build forms from the bottom of the heirarchy up (the one I posted a few posts back). This is the order I would do it in:

1. tblPackingMaterial
2. tblPaint
3. tblPaint
4. tblPartPacking*
5. tblCustomers
6. tblOrders
7. tblOrderParts*

* tblPartPacking and tblOrderParts are junction tables (http://en.wikipedia.org/wiki/Junction_table) which means they will most likely be sub forms on one of the 2 forms whose tables they join.

How will status be determined--can you do it by looking at the data itself? Do you want to track when an order was moved from status to status? Where does department fit in all of this?

Ok so I created a Part entry form based off of the parts table and put tblpartpacking as a subform on that so when I put a new part in I can just put the packing materials too. Does this sound right? I also created a form for each table.

I am thinking, for order creations Can I make tblorderparts a subform to a form created from tblorders?

How I am planning on doing this is making sure that when I add an order the part and its information were put in first. Like enter a new part before I create a new order with it. If I do this and use tblOrders with tblOrderParts as a subform will all of the rest of the information about that part be added to the master query? I ask this because tblOrders and tblOrderParts do not have all the fields that the master query has.

How will status be determined--can you do it by looking at the data itself? Do you want to track when an order was moved from status to status?
The status would go to active when I create an order, I would change the status to on hold if we were waiting on paint or something for the job and complete it when the job is finished. I had a few queries that matched the order form and set the criteria to each status for each query and was able to get the visual I wanted.

Where does department fit in all of this?
We have 2 sides that we run parts on, line and batch. I just want to be able to separate the line from the batch. Parts are not specific to each side, depending on who is busier a part might be run on batch or line. I did the same query method I mentioned above for this as well.

 

[plog]

I am thinking, for order creations Can I make tblorderparts a subform to a form created from tblorders?

Yes, that's how you would do it.

That master query was just a query to bring all your data together, it probably shouldn't be used. The problem with it is that is shows down to the packing material level, which means if a part has both foam and a bag it will appear twice in that query. That's not a bad thing if you are looking at just packing material, but not when you are looking at orders because it will make it appear more parts are there than should be.

Since status is its own data and not really based on anything, I would create a field for it in tblOrders. Still not clear on where department should go, where do you think it would go?

 

[jesswoo]

I think the department should go into the Orders table because a part can be run on either the line or batch depending on how busy we are and the only thing it would be specific to is the order. If an order has multiple parts all of those parts are run in the same department.

 

[plog]

Sounds good, I think you are getting the hang of it. Always try to put data at the lowest level of the heirarchy it will be needed.