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
> Table Structure, Access 2016    
 
   
thereclinerman
post Jan 6 2018, 07:05 PM
Post#1



Posts: 47
Joined: 24-November 08



fairly new user access 2016

i am building a db for work orders. i plan on having a items, components, and customer table. the item can be composed of serveral components and the components themselves can be used as items. there is no sub-component of the component. when the order comes in from the customer, it would list the items needed and entered into the db with the customers order number. when the work order is printed, i would need for the items to be broken down into componets and the work order printed.

what would be the best table structure to use.

thanks for any help
Go to the top of the page
 
MadPiet
post Jan 6 2018, 07:46 PM
Post#2



Posts: 2,420
Joined: 27-February 09



"Work Orders" is a super generic database design. Depends on what you're working on and how all the other entities in your "database world" fit together. I fixed one for someone on here ... without knowing EXACTLY how things fit together, it's impossible to say. We'd need more details.
How good are you at creating Entity-Relationship diagrams? I would start there. Not enough information to tell if you have all the tables you need from your description, I don't believe.
This post has been edited by MadPiet: Jan 6 2018, 07:48 PM
Go to the top of the page
 
orange999
post Jan 6 2018, 10:53 PM
Post#3



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


Further to MadPiet's comments, I would suggest you write a description (or two) of a specific component and what happens to it in the business. A few sample scenarios can show readers the context and help identify tables and relationships.
What exactly is an item? Component?
How does a work order in your business differ from a PurchaseOrder?
Not trying to be difficult--am just trying to get more info from you re your set up with some context.

--------------------
Good luck with your project!
Go to the top of the page
 
MadPiet
post Jan 7 2018, 06:54 AM
Post#4



Posts: 2,420
Joined: 27-February 09



Big "amen" to the scenarios. Write those down in detail. We would need to know how the "things" in your scenarios interact. Without that, we cannot help you.
Go to the top of the page
 
thereclinerman
post Jan 7 2018, 09:47 AM
Post#5



Posts: 47
Joined: 24-November 08



first of all, let me thank you guys for the interest and help. please bare with me because like i said, i am fairly new to this am retired, and doing this for a friend. my background is retired military and programming routers. all i know about this is watching a few hundred videos and helping another guy with a db. i do not know SQL, before event, after event, etc. just letting you guys know my level and what you are working with, lol.

enclosed is the db i have been working on. basicly what happens is the customer orders the item and it needs to be broken down into components for manufacture. if they order a item and a component, they need to be added together before the work order is publshed. all that i have finished so far is the tables and the means to add the customers order.

thanks,
This post has been edited by thereclinerman: Jan 7 2018, 09:48 AM
Attached File(s)
Attached File  SunFab.zip ( 43.79K )Number of downloads: 9
 
Go to the top of the page
 
orange999
post Jan 7 2018, 10:02 AM
Post#6



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


I suggest you take a couple of "Items" and describe to us in more detail (or examples) what the constituent parts(component(s)) are.
It is critical to get your "business requirement" clearly identified before getting too deeply into physical database. Much easier to adjust a picture of what you are trying to automate, than modifying tables, relationships, forms.... All the SQL and events will not correct an "iffy design".
Build a model (pencil and paper); create some test data and scenarios; and test the model to ensure you can get the outputs you need.
There is some sort of structure/hierarchy in the items and components that needs to be "fleshed out".
Good luck.
This post has been edited by orange999: Jan 7 2018, 10:03 AM

--------------------
Good luck with your project!
Go to the top of the page
 
thereclinerman
post Jan 7 2018, 10:46 AM
Post#7



Posts: 47
Joined: 24-November 08



thanks for the reply. if you are in canada, i know you are freezing, if you are in florida you might be freezing too, lol. this cold hit north carolina hard also.

ok, a 600 sofa would consist of 2 end cushions, 1 center cushion, 1 600 back, and 2 arms. the customer does not want to order individual components so they order the complete set as a item that will make 1 sofa. we have to break it down into components for manufacture as the people making the parts do not know what the complete item consist of. lets say the customer orders only two items. he needs 1 600 sofa and 1 end cushion (end cushion he had was torn, it is also a component of the 600 sofa). so we would need a work order for the following:

1 600 sofa back
3 600 end cushions
1 600 sofa center cushion
2 600 arms


the reason there is no sofa in the description of the end cushions and arms is because they can also be used in the loveseat.
this would let him manufactor 1 600 sofa and repair the other sofa he had.

she orders blocks of foam, 60" x 120" x 50" and cuts them into parts as need.
no inventory of any of these items is keep, so no inventory control is need.
invoicing is through another program she has so invoicing, costing, etc is not needed.
this db is to ouput the items need for the workers to manufacture. after production, all of this paper work is thrown away and if the customer has any questions, the actual shipping papers and the invoice answers all questions.


thanks,
Go to the top of the page
 
MadPiet
post Jan 7 2018, 11:06 AM
Post#8



Posts: 2,420
Joined: 27-February 09



Makes more sense now. Thanks for the clarification. I did something similar a long time ago... The "items are made of components, and the items have to be tracked at component level" problem.

Item (e.g. "sofa") consists of one or more Components (some number of "cushions" etc). So you have:

Item---(1,M)---ConsistsOf---(M,1)--Component (because some components can be part of different items).

Then you the

Customer--(1,M)--OrderHeader--(1,M)--OrderDetail--(M,1)--Item

part - your standard "Customer Invoice LineItem" thing.

So a person ordering a Product sees it at Product level (Product table), while others see it at Component Level (ConsistsOf table)

Does that make sense? In a word, this sounds like your standard AdventureWorks (Customer buys Product) database would be a good starting point. Then you can just customize to suit your needs by adding extra tables.
Go to the top of the page
 
thereclinerman
post Jan 7 2018, 02:39 PM
Post#9



Posts: 47
Joined: 24-November 08



thanks for the tip about adventure works. looked it up and it seems to be for SQL server. that is not the case for us. have to try this in access and split the db instead of trying to learn access and SQL server. i know i can use the access fe and link to a SQL server express, however, too complicated for my limited brain, lol. i know nothing about SQL server.

the biggest thing for me is gettng the order for a item and then breaking it down into components. will keep trying to do it with queries.

thank you
Go to the top of the page
 
MadPiet
post Jan 7 2018, 10:58 PM
Post#10



Posts: 2,420
Joined: 27-February 09



SQL Server is really irrelevant to the question. AdventureWorks is just the standard Microsoft sales database. I think the Access version was Northwind or something... (It's been a while - haven't seen it around.) But my comments on the design still stand. I did this in Access 2000 like 20 years ago... I had the same problem, but I had the luxury of asking someone who was really smart - one of my professors... and that's what he said to do.

I used the SQL Server syntax for creating tables because Access isn't self-documenting (which I think is less than helpful). While Access SQL and T-SQL are different, there is some overlap between the two - and the code I posted will work.
Go to the top of the page
 
projecttoday
post Jan 8 2018, 03:09 AM
Post#11


UtterAccess VIP
Posts: 9,546
Joined: 10-February 04
From: South Charleston, WV


Refer to qryItemComponents in the attached database.
Attached File(s)
Attached File  SunFab.zip ( 46.6K )Number of downloads: 5
 

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
mike60smart
post Jan 9 2018, 11:28 AM
Post#12


UtterAccess VIP
Posts: 12,595
Joined: 6-June 05
From: Dunbar,Scotland


Hi

In the attached on the Form that opens at Start Up select an Item using the Combobox and then only those Components associated with the Item chosen will be displayed.

Is this what you are after??


Attached File(s)
Attached File  SunFab.zip ( 67.34K )Number of downloads: 8
 

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
thereclinerman
post Jan 10 2018, 09:08 PM
Post#13



Posts: 47
Joined: 24-November 08



robert, is that table a juction table? i will have to see if i can understand the table and work it out. also changed my generic ID to read the table name plus ID. thank you for the tip.

mike, that is close to what i wanted. thank you

madpiet, i did find the db adventureworks for the pc. it had the tables and relationships but no queries and forms. i also looked at northwind db. after looking at everything and trying to strip out what i didnt need, it would not work. i am learning what i can from these, however, they are way above me at this leve, lol. plan on watching videos and looking at examples. thank you for the advice.

enclosed is a db that is close to outputing what i need. seems if i got the query to work outputing the quantity needed
i am using frmComleteItem to builld the link for customer, item name, components, and quantity
the form customer order is letting me enter the customers complete order.
the query does ouput the correct items names and quantity of components needed.

still dont know if the table structure is efficient and if all the relationships are right.

how would i choose just one order, break it down where the item name does not show up in front of every item/component like it does in the query?

again, thanks to everyone for the advice and help
This post has been edited by thereclinerman: Jan 10 2018, 09:12 PM
Attached File(s)
Attached File  SunfabWorking1.zip ( 42.55K )Number of downloads: 5
 
Go to the top of the page
 
projecttoday
post Jan 10 2018, 09:20 PM
Post#14


UtterAccess VIP
Posts: 9,546
Joined: 10-February 04
From: South Charleston, WV


Yes, it is a junction table.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
MadPiet
post Jan 10 2018, 09:27 PM
Post#15



Posts: 2,420
Joined: 27-February 09



I would probably handle this by describing the "things" you make as recipes. "Sofa" = {end cushion (2), center cushion (1), leg (6)...} Then it's really easy to sort out the parts for, say, 100 Sofas. (I think it's a "bill of Materials" thing. There's an example on Accessweb, which used to be at www.mvps.org/access ... don't remember what new site it's on, but it might be helpful to you.
Go to the top of the page
 
mike60smart
post Jan 11 2018, 05:34 AM
Post#16


UtterAccess VIP
Posts: 12,595
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Took a look at the frmCompleteItem

I am not understanding what the Qty refers to?

Is it 2 x Sofas or 2 x 600 End Cushions?

Attached File  complete.PNG ( 12.35K )Number of downloads: 1

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
thereclinerman
post Jan 11 2018, 07:11 AM
Post#17



Posts: 47
Joined: 24-November 08



thanks MadPiet, will try to track it down.

hi mike,

this is letting me build up my bill of materials. customer is who this item belongs to. the item listed is the item name while the 2 600 end cushions are components of the item. after i add the 2 arms, 1 center cushion, and 1 back i have all the components need to build the item. when the customer orders the 600 sofa, i have to break it down into all the components for manufacture.
using the item table and component table i am able to link all the components to the item name and build the completed item table.



Go to the top of the page
 
mike60smart
post Jan 11 2018, 08:13 AM
Post#18


UtterAccess VIP
Posts: 12,595
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Well on this Form the Item is the Sofa

You are only able to enter 1 Item Component for this Sofa ??

You need a Subform to add Multiple Components for the Sofa

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
thereclinerman
post Jan 11 2018, 06:37 PM
Post#19



Posts: 47
Joined: 24-November 08



mike, your are right.

i only added one component at a time. did not know i could do this through a subform and add everything at once. will get to work looking this up and implementing this. thank you for the tip.
as i said, very limited knowledge on access. am still learing and looks like this is a steep hill, lol.

am still looking at the examples in adventureworks, northwind, and other db i can find. found a receipe db that MadPiet suggested and looked through that at the receipe/ingredit tables and relationship structuure.
it very helpful. change one of the tables in my db to a real juction table that robert showed me and it worked great.

i really appreciate all the answers and advice everyone has given.


jim
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2018 - 06:51 AM