UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> New Database That Needs Help With Structure, Access 2013    
 
   
soggycashew
post Jun 17 2019, 10:58 AM
Post#1



Posts: 334
Joined: 23-April 13
From: WV, USA


Hello, Im asked to try and build a database that is used to setup machines and your setup is determined by what product your setting up. This wouldn't be an issue BUT the problem im having is if the machine is setup to run one product and you need to now setup to run a different product BUT the new product uses some of the equipment thats already installed how do you determine what is needed?

The way I wanted it set up was two combo boxes on a switchboard that you can choose what your currently running as in Product 1 then the second combo would be what your wanting to setup as in Product 2. Next, I wanted to have a report print out the steps needed in converting the machine to the new product OR I also want a button on the switchboard that you can print out a parts list of the parts needed to setup for the new product.

How would the table structure go and how can I determine what piece of equipment is needed compared to what is already installed that the new product needs?

Thanks,
Go to the top of the page
 
nuclear_nick
post Jun 17 2019, 01:50 PM
Post#2



Posts: 1,767
Joined: 5-February 06
From: Ohio, USA


Lemme see if I got this correct, re-phrasing a bit. (This will give you some insight on how I approach things, usually...)

You have several machines that make product.
You have several products.
Each product has a 'recipe'.
Each 'recipe' consists of equipment.

In order to make 'product A27' you need the equipment in 'recipe 23' set up on 'machine 56'.

If 'machine 56' needs to have the equipment set up for 'recipe 45' instead, in order to make 'product B78', we want to know what equipment needs removed and what equipment needs added to 'machine 56' before we can begin making the new product.

So... we have machines containing equipment for a recipe to make a product.

That right so far? Does it help?

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
soggycashew
post Jun 17 2019, 06:02 PM
Post#3



Posts: 334
Joined: 23-April 13
From: WV, USA


Yes that's a better explanation BUT lets skip the machine because all machines are the same but like you said if I have a product installed and want to install another that uses some of the parts then what parts are needed.

Go to the top of the page
 
orange999
post Jun 17 2019, 06:08 PM
Post#4



Posts: 1,939
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Following along with Nick's approach, don't you compare the "recipe for the new product" with the "recipe for the old/current product" to find the "difference in set up"? In effect it seems to come down to an "adjusting machine properties from A to B".
This post has been edited by orange999: Jun 17 2019, 06:10 PM

--------------------
Good luck with your project!
Go to the top of the page
 
soggycashew
post Jun 17 2019, 06:28 PM
Post#5



Posts: 334
Joined: 23-April 13
From: WV, USA


Here is a start of what I'm thinking except for the table tbl_ComponetInstallInstruction. That table is a table on how to change out the new component such as steel, paper, feed wheel and 20 other things along with an image. I'm not sure where in the structure it would go or even if this is correct?

Thought,


Attached File(s)
Attached File  Capture.PNG ( 19.85K )Number of downloads: 12
 
Go to the top of the page
 
soggycashew
post Jun 17 2019, 10:17 PM
Post#6



Posts: 334
Joined: 23-April 13
From: WV, USA


Nick using your words this is more what I'm trying to do...

I have several products.
Each product has a 'recipe'.
Each 'recipe' consists of equipment.
Each piece of equipment consists of a written install instruction.

In order to make 'product A27' I need the equipment in 'recipe 23'.

If we are currently making 'product B78' and using 'recipe 45' I want to know what equipment needs removed
and what equipment needs installed before we can begin making the new product.

I also need to know each instruction for the equipment being installed.
Go to the top of the page
 
MadPiet
post Jun 17 2019, 10:33 PM
Post#7



Posts: 3,221
Joined: 27-February 09



If we are currently making 'product B78' and using 'recipe 45' I want to know what equipment needs removed and what equipment needs installed before we can begin making

Remove are all parts that are in A and not in B.
Add are all parts that are in B but not in A.

Use an outer join between the "ingredients" of the two "recipes".
Go to the top of the page
 
nuclear_nick
post Jun 18 2019, 07:12 AM
Post#8



Posts: 1,767
Joined: 5-February 06
From: Ohio, USA


QUOTE
Yes that's a better explanation BUT lets skip the machine because all machines are the same but like you said if I have a product installed and want to install another that uses some of the parts then what parts are needed.


That's what's confusing me in the first place.

I typically think of it like this... 'Product' is 'produced' on machines. Having 'product installed' is not making sense in this way to me, and I think it may be complicating things a bit in my understanding.

I am glad, however, you provided a screenshot of what you're thinking, and I have a comment.

It appears the 'tbl_Steel', 'tbl_Paper', and 'tbl_FeedWheel' are 'ingredients' in our 'recipe'. What happens if a fourth is added? A fifth? Then we remove the 4th? What about 'tbl_Components'...
CODE
tbl_Components
--------------
(pk) ComponentID
ComponentName
CompentWidth
ComponentHeight
ComponentLength


See what I did there?

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
soggycashew
post Jun 18 2019, 10:42 AM
Post#9



Posts: 334
Joined: 23-April 13
From: WV, USA


Nick I see what you mean with the components BUT im not understanding how you could select the data for a piece of equipment if its all jumbled up in tbl_Componets. I put together some data and tables from the image and a few more "Components" can you give me an example from that data? Im also wondering how this would work in a continuous form, I vision having a main form where I would select the product then a continuous sub form that I could select the component and write about it in a text field then the next record in the sub form I could choose another component and so on until all components for setting up that product is completed.
Attached File(s)
Attached File  Database1.zip ( 21.93K )Number of downloads: 1
 
Go to the top of the page
 
nuclear_nick
post Jun 18 2019, 11:12 AM
Post#10



Posts: 1,767
Joined: 5-February 06
From: Ohio, USA


You're already familiar, it seems, with the concept of a 'junction table'... it's in your screenshot, the junction of tbl_Products to tbl_Recipes with the tbl_ProductSetUp.

It's a similar set up.

I'll take a look at your attachment later... haven't forgotten you.

Just think about the SQL a bit. State it in plain English first, if that helps, like, "Show me all of the components in a set up for product 1." Then try it in SQL, like, "SELECT ProductID, ProductName, ComponentID, ComponentName FROM tbl_Products …."

If you find issues with your tables this way... adjust the table(s) until the 'English' and the 'SQL' make sense, and you have more 'normalized' tables. (I did a 'recipe' database as a test for a business a long time ago... they wanted to be able to print ingredient lists for their 'goods', as they had to label them. Just... that was 15 years ago, and I have no idea right now where it is at the moment.)


--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
soggycashew
post Jun 18 2019, 04:59 PM
Post#11



Posts: 334
Joined: 23-April 13
From: WV, USA


Ok Nick, I been working on this and I think I'm getting somewhere... I attached 2 images. First is my new structure and second is the tbl_Components. Is this correct?
Attached File(s)
Attached File  image_2.PNG ( 50.75K )Number of downloads: 3
Attached File  Recipes.PNG ( 10.39K )Number of downloads: 7
 
Go to the top of the page
 
soggycashew
post Jun 18 2019, 10:27 PM
Post#12



Posts: 334
Joined: 23-April 13
From: WV, USA


Ok, getting burned out for today but made some progress I think... I created a from and subform but I cant enter new data into forms BUT I can open the tbl_Recipe and manually enter data like the product and then open the form and then I'm able to enter data into the record just not new ones, I cant figure out why. One that's done I need to figure out how to make selections for individual components on my subform so I can enter data that pertains to that step. I also moved 2 componets to the recipe being that one was a text field that I write whats going on and two is an image link.

Attached is the database I have so far..
This post has been edited by soggycashew: Jun 18 2019, 11:00 PM
Attached File(s)
Attached File  Database_2.zip ( 148.32K )Number of downloads: 3
 
Go to the top of the page
 
soggycashew
post Jun 19 2019, 03:09 PM
Post#13



Posts: 334
Joined: 23-April 13
From: WV, USA


Thoughts on how this could be completed, I was given until Thursday to complete. Well get most of it done and working... I keep getting the errors when trying to enter data, the changes you requested in the table were not successful because they would create duplicate values

Thanks,
This post has been edited by soggycashew: Jun 19 2019, 03:31 PM
Go to the top of the page
 
MadPiet
post Jun 19 2019, 07:07 PM
Post#14



Posts: 3,221
Joined: 27-February 09



duplicate values where? Which table? It could be that your junction table is wrong.
Go to the top of the page
 
soggycashew
post Jun 19 2019, 10:27 PM
Post#15



Posts: 334
Joined: 23-April 13
From: WV, USA


Thanks for everyone's help, I moved on to a different way of doing it so that I can have it completed by tomorrow. Ill have to look back at this in the future!

Thanks Again...
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th August 2019 - 09:37 AM