Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Muse View Post
    Does anyone have suggestions? Because I'm stuck. Thanks!
    If I review, I see where you had at least 2 suggestions (posts 4 and 8) and didn't bother to comment on either of them. I can't speak for Dave14867 but that sort of (dis)engagement doesn't do much to keep my interest in a problem.

    You probably need a combination of a Bill of Materials, Manufacturing and Inventory schemas. You could look here under Manufacturing for some hints.


    FWIW, I disagree with your comment that a table of stocked parts doesn't need to be in a relationship unless you don't care what's in stock and just want to associate a part with a job. By that I don't mean an actual defined relationship in Access (they can be helpful but are not absolutely necessary) but I think you'd want that relationship in at least a virtual sense.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    Join Date
    Apr 2017
    Posts
    1,673
    A typical schema how such databases are set up:

    You have a table where all parts of various types are defined - purchased, produced, fictitious, etc. tblParts: PartID, PartName, PartType, ....;
    You have a table where workplaces/workstations are registered, etc. tblWorkstations: WSID, Workstation, ...;
    You have a table where a production orders for certain part of produced type at certain date in certain workstation are registered, etc. tblProdOrders: ProdOrder, PODate, WSID, ProdID, Qty, ... (ProdID is PartID of produced part);
    You have a table where for every part of produced type (products) a list of all component parts in assembly order are listed, with quantities needed at every step of producung a single unit of product, like tblBOM: BomID, ProdID, ProdBomRow, CompID, CompQty, ... (CompID is PartID of component assembled into product). This table allows you to calculate, which parts and how much of them are needed to fulfill the production order;
    Probably you also need a table, where for every BOM list row, all operations needed to assemble the component into product are listed, like tblBOMOp, BomID, OpID, OpTime, ...;

    Now we have prerequisites defined, and can proceed with actual workflow.

    Starting with storage. Storage (a real, or imaginary) is a place, where all starts, and where all ends. Here you register all purchased, incoming from production, or otherwise gotten parts when they are coming in, and all sold, or scrapped, or bundling for production, or whatever parts when they are going out. Like tblStorageMoves: MoveID, [StorageID], PartID, MoveType, MoveDatetime, MoveQty, ...; This is the table, where you can always find out, how much you bought or sold certain part in certain period, or how much of certain parts are available currently, etc. You can have several storages, or a single one (in which case StorageID is optional field).

    Another main table to make all this to work, is a table where finished products are registered. Like tblReports: ReportID, ProdOrder, RepDatetime, [ProdID], [QtyPlanned], QtyProduced, [QtyRemains], ... (in [] are optional fields - you can get according values from other tables, but sometimes it is more effective to calculate them and save).

    Probably you'll need some additional tables e.g. to move info between production and storage, but essentially it is the gist of it.

  3. #18
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Sorry, I did not mean to not reply, I tried to do a blanket reply to hopefully answer everyone. Sorry very sorry. I'm not trying to dismiss anyone.
    Dave14867 PM'd me so I was replying to him there.

    l will have a look at the link you suggested. I guess, I'm not very good at database structure yet so I'm usually winging it whenever I make one. I'm trying to teach myself so I still have problems with how certain things should work. That's probably why I tried to use access jargon instead of describing what I wanted to do in the first place.

    I really appreciate your reply.


    Quote Originally Posted by Micron View Post
    If I review, I see where you had at least 2 suggestions (posts 4 and 8) and didn't bother to comment on either of them. I can't speak for Dave14867 but that sort of (dis)engagement doesn't do much to keep my interest in a problem.

    You probably need a combination of a Bill of Materials, Manufacturing and Inventory schemas. You could look here under Manufacturing for some hints.
    FWIW, I disagree with your comment that a table of stocked parts doesn't need to be in a relationship unless you don't care what's in stock and just want to associate a part with a job. By that I don't mean an actual defined relationship in Access (they can be helpful but are not absolutely necessary) but I think you'd want that relationship in at least a virtual sense.

  4. #19
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Where's the mind-blown emoji?

    I had written down what I wanted my database to do and I got most of it working for the most part, but I think I may have to read your reply about 10 times so I can soak it in. I really want to figure this thing out, but I think I'm just getting more and more confused the further I go.

    Thanks!

    Quote Originally Posted by ArviLaanemets View Post
    A typical schema how such databases are set up:

    You have a table where all parts of various types are defined - purchased, produced, fictitious, etc. tblParts: PartID, PartName, PartType, ....;
    You have a table where workplaces/workstations are registered, etc. tblWorkstations: WSID, Workstation, ...;
    You have a table where a production orders for certain part of produced type at certain date in certain workstation are registered, etc. tblProdOrders: ProdOrder, PODate, WSID, ProdID, Qty, ... (ProdID is PartID of produced part);
    You have a table where for every part of produced type (products) a list of all component parts in assembly order are listed, with quantities needed at every step of producung a single unit of product, like tblBOM: BomID, ProdID, ProdBomRow, CompID, CompQty, ... (CompID is PartID of component assembled into product). This table allows you to calculate, which parts and how much of them are needed to fulfill the production order;
    Probably you also need a table, where for every BOM list row, all operations needed to assemble the component into product are listed, like tblBOMOp, BomID, OpID, OpTime, ...;

    Now we have prerequisites defined, and can proceed with actual workflow.

    Starting with storage. Storage (a real, or imaginary) is a place, where all starts, and where all ends. Here you register all purchased, incoming from production, or otherwise gotten parts when they are coming in, and all sold, or scrapped, or bundling for production, or whatever parts when they are going out. Like tblStorageMoves: MoveID, [StorageID], PartID, MoveType, MoveDatetime, MoveQty, ...; This is the table, where you can always find out, how much you bought or sold certain part in certain period, or how much of certain parts are available currently, etc. You can have several storages, or a single one (in which case StorageID is optional field).

    Another main table to make all this to work, is a table where finished products are registered. Like tblReports: ReportID, ProdOrder, RepDatetime, [ProdID], [QtyPlanned], QtyProduced, [QtyRemains], ... (in [] are optional fields - you can get according values from other tables, but sometimes it is more effective to calculate them and save).

    Probably you'll need some additional tables e.g. to move info between production and storage, but essentially it is the gist of it.

  5. #20
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Hi, Ajax,

    I know that the tblAssy_Inv is in a one to many relationship with the Build Order table (tblBTO), because there will be many builds of each assembly. The parts consumed table is what was pulled from the Main Inventory list (I'll attach another photo. I didn't realize I had cut it out). There are no records in the Parts Consumed table yet because I don't know how to link that data back to the BTO.

    PartNumber is not the same as Part_Number. Part_Number is the part number of the assembly from tblAssy_Inv table. I know it's redundant since it has a primary key, but I need the information to display on the form. I'm sure there's an easier way to do this, but I'm trying to learn.

    Thanks!Click image for larger version. 

Name:	DBase_relation2.PNG 
Views:	16 
Size:	27.0 KB 
ID:	43256

    Quote Originally Posted by Ajax View Post
    I would, but too confused with your description. I'm unable to tie it back to your relationships and don't understand your relationships. Also you said you had another way of going about it.

    I don't see an inventory table, or a parts table for that matter

    which is what table?

    from your relationships you appear to already have a link (BTO_ID)

    and is partnumber the same as part_number?

  6. #21
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Quote Originally Posted by Micron View Post
    If I review, I see where you had at least 2 suggestions (posts 4 and 8) and didn't bother to comment on either of them. I can't speak for Dave14867 but that sort of (dis)engagement doesn't do much to keep my interest in a problem.
    I don't know if you missed this or not, but post #7 was in reply to you and post #6. That's where I was trying to describe what I was trying to do instead of using database terms. I guess I should quote posts so others will know who I'm trying to answer.

  7. #22
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I guess I should quote posts so others will know who I'm trying to answer.
    that would be very helpful

    Also help if you read the responses and answered the questions asked. We are up to post 21 and still no-one seems to know what it is you are trying to do based on the tables you have shown us.

    You know your tables we don't. Suggest take us through the tables to show how the process works because for me it still does not make sense. It feels like there are a bunch of tables missing.

  8. #23
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Quote Originally Posted by Ajax View Post
    that would be very helpful

    Also help if you read the responses and answered the questions asked. We are up to post 21 and still no-one seems to know what it is you are trying to do based on the tables you have shown us.

    You know your tables we don't. Suggest take us through the tables to show how the process works because for me it still does not make sense. It feels like there are a bunch of tables missing.
    Okay
    Let me see if I can explain it better...

    The tblAssy_Inv are assemblies that we manufacture. The tblBTO (Build to Order) are the build orders that are opened once an assembly is picked from the tblAssy_Inv table. I have tblMain_Inv for items we keep in stock that are used to put these assemblies together. The tblParts_Consumed table is reserved for parts used from tblMain_Inv for the Build to Order.

    The first pic is the Build to Order form and there's a consume button that will take you to the next form (2nd pic) that has a list of stocked parts (tblMain_Inv). Once you choose a part from there, it opens the next form (3rd pic). This is where I'm trying to consume the quantity. The box left of the consume button is where I insert the quantity I want to use. This is where I'm stuck. When I "consume" the part, it doesn't associate it to the Build to Order I'm working with and I'm not sure how to associate it or link it?

    Click image for larger version. 

Name:	frmBTO_Update.PNG 
Views:	16 
Size:	49.9 KB 
ID:	43257
    Click image for larger version. 

Name:	frmMain_Inv.PNG 
Views:	16 
Size:	21.2 KB 
ID:	43258
    Click image for larger version. 

Name:	frmPartConsume.PNG 
Views:	16 
Size:	24.7 KB 
ID:	43259

  9. #24
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This is my understanding of your environment. Readers need to understand your set up clearly in order to respond with focused advice.

    You build Assemblies. Are these Assemblies (of predefined parts)or can they be Custom(unique part/parts for a given Order)?
    Your Company manufactures 1 or many Assemblies


    An Assembly consists of Many Parts.
    An Assembly may contain 0,1 or many instances of a given Part
    Each Part has a unique id and a name
    Parts are Categorized by NSN(National/NATO Stock Number)
    Parts are Stocked at your facility
    The BuildToOrder sounds like a Picklist (these parts in these quantities are required to manufacture the Assembly) (my best guess..)
    Further, it seems as the Picklist items are "used"(partial manufacture), they are removed from the Picklist (at least conceptually)

    It also seems you collect info about Assemblies and participate in Warranty and Repair activities (no details available).

  10. #25
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Hi, Orange,
    I don't know how else to explain it. I definitely feel like I'm out of my element now.

    Thanks for your response though. Let me see if I can give an example. If I can't explain it this way or I'm explaining it wrong, then I give up >_<

    Let's say, we manufacture Playstation, Playstation 2, Playstation 3 and Playstation 4's which are listed on the tblAssy_Inv. The tblAssy_Inv isn't very big. It just has a primary key (autonumber), Playstation 3 description, part number, model number and cost.

    I want to make a Playstation 3. This is where I open a Build to order, select Playstation 3 from a drop down menu, it auto fills the appropriate fields taken from the tblAssy_Inv. Once I do that, I can add additional information like serial number, date Build to Order was started and Customer which is recorded to the tblBTO table.

    I want to use or consume parts from the tblMain_Inv (which contains all the parts needed to manufacture this PS3). It contains fields for Part ID (autonumber), part number, description, serial number (if it has one), quantity in stock, cost of the part and minimum stock required to keep on hand. I'm trying to find out how I link the consumption to the Build to Order (tblBTO).

    I hope this makes any sense. I'm sorry, if I'm not understanding it right.

    Thanks,
    Muse

    Quote Originally Posted by orange View Post
    This is my understanding of your environment. Readers need to understand your set up clearly in order to respond with focused advice.

    You build Assemblies. Are these Assemblies (of predefined parts)or can they be Custom(unique part/parts for a given Order)?
    Your Company manufactures 1 or many Assemblies


    An Assembly consists of Many Parts.
    An Assembly may contain 0,1 or many instances of a given Part
    Each Part has a unique id and a name
    Parts are Categorized by NSN(National/NATO Stock Number)
    Parts are Stocked at your facility
    The BuildToOrder sounds like a Picklist (these parts in these quantities are required to manufacture the Assembly) (my best guess..)
    Further, it seems as the Picklist items are "used"(partial manufacture), they are removed from the Picklist (at least conceptually)

    It also seems you collect info about Assemblies and participate in Warranty and Repair activities (no details available).

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think your tables are complete or set up correctly. While I wade through your process related info, why don't you take a look at normalization here and/or here and see if you agree? If you have already researched the subject, I'm trying to tread lightly here but have to say I don't think you fully understand it. I have to always temper such assessments with the view that the complete process is seldom covered by anyone, but we can try making a start. If you (re)read up on the subject and then compare your understanding to what I (or I think we) will post on that I think you'll get somewhere.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you keep missing out steps
    Let's say, we manufacture Playstation, Playstation 2, Playstation 3 and Playstation 4's which are listed on the tblAssy_Inv. The tblAssy_Inv isn't very big. It just has a primary key (autonumber), Playstation 3 description, part number, model number and cost.
    this I understand

    This is where I open a Build to order, select Playstation 3 from a drop down menu, it auto fills the appropriate fields taken from the tblAssy_Inv. Once I do that, I can add additional information like serial number, date Build to Order was started and Customer which is recorded to the tblBTO table.
    also understood - the drop down uses tblAssy_Inv as a rowsource.

    I want to use or consume parts from the tblMain_Inv (which contains all the parts needed to manufacture this PS3). It contains fields for Part ID (autonumber), part number, description, serial number (if it has one), quantity in stock, cost of the part and minimum stock required to keep on hand.
    this makes no sense to me. Where is the table that says to make a PS3 you require parts 1, 2 and 3. To make a PS4 you require parts 1,5 and 6 etc? Or are you simply relying on the user knowing which parts and what quantity is required? Any why would the quantity change? For this PS3 I'm going to use 2 of part 1, but for the same product on a different assembly, I'm going to use 3 part 1's?

    quantity in stock and cost of the part should not be in tblMain_Inv. quantity should be calculated as and when from a transactions table which I don't see unless it is the consumed table, and costs change over time so should also be in the same table, or perhaps somewhere else

    And still you haven't answered is partnumber and part_number the same thing? If so why is it in tblBTO?
    I'm trying to find out how I link the consumption to the Build to Order (tblBTO)
    As already stated, you have a link - why is this not acceptable?

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Had this unposted while I played with a detailed response on Notepad, so sorry if it seems to duplicate or contradict anything that was just posted.

    I don't think your tables are complete or set up correctly. While I wade through your process related info, why don't you take a look at normalization here and/or here and see if you agree? If you have already researched the subject, I'm trying to tread lightly here but have to say I don't think you fully understand it. I have to always temper such assessments with the view that the complete process is seldom covered by anyone, but we can try making a start. If you (re)read up on the subject and then compare your understanding to what I (or I think we) will post on that I think you'll get somewhere.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #29
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, this is not how I like to make in depth suggestions - I put the concepts on paper as do many seasoned developers but this is from my head, and is not based on a crystal clear understanding of the process. It's only meant to support the notion that there is a bit of a lack of understanding db normalization. It is by no means complete (or necessarily accurate). It seems to support what Ajax just wrote about not mixing transactions with your parts table.

    These are what I think are the bare minumum of tables and respective fields that you should have for the issue you presented. However, it does not cover the idea of having a BOM which may be what you really need, but one has to know where to stop with the suggestions. A BOM would allow you to get a complete list of parts, sub assemblies and quantities for any build. You could choose a build item and pre-populate a parts list and report on stock availability in one go.

    (Desc is short for Description)

    The units you manufacture:
    tblAssy
    AssyId autonumber; primary key (some add PK to the end of the field name)
    AssyName
    AssyDesc
    etc.

    ALL of the parts you "consume" on any build. Perhaps even parts that you use as sundry items as well - depends on what the db is to support.
    tblParts
    PartID autonumber; PK
    PartNumber
    PartDesc
    etc. (supplierID as foreign key from tblSupplier perhaps?)
    Parts transactions do not go into parts table

    tblInventory
    InventoryID autonumber; PK
    PartIDfk - PK from tblParts
    TransType (transaction type) PK from tblTransactionType?
    QTY single field for + or - qty. The form sets -2 or 2 based on transType. Stock count is therefore the sum of all +'s and -'s for a PN
    etc.

    tblBTO
    btoID autonumber PK
    If a build has data like start/end/promise dates, customer, etc. then no parts data goes into these records because the BUILD IS ITS OWN ENTITY thus see next table. Probably best to have bto and parts used on it separated regardless. So the bto is an entity and so is/are the part(s). Usually when 2 or more entities are involved, there is a junction table to link their records because in reality you have a many to many relationship (many bto that use many parts).

    tblBTOparts - junction table for a build and its parts
    btoPartID autonumber PK
    btoID - FK from tblBTO (same ID value for every part used on any ONE build). These 1st 2 might need to be a composite key so that the same part cannot be added again.
    InvIDfk - part PK from tblInventory NOTE: linking to inventory would permit a direct link between stock count and a bto. If you link to tblParts, you need a work around to know if there is sufficient stock for a build.
    btoPartQty - how many of a part? I think to have a BOM table provide a part qty could only work if a bto is for ONE assembly and no more.

    A junction table for PartsSuppliers may be required if several vendors supply the same part and it is the same PN in your system.
    Add standard stuff not related to this, such as tblCustomer, tblUser and so on.

    HTH
    Last edited by Micron; 10-19-2020 at 05:15 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #30
    Muse's Avatar
    Muse is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2020
    Location
    TX
    Posts
    15
    Quote Originally Posted by Micron View Post
    Had this unposted while I played with a detailed response on Notepad, so sorry if it seems to duplicate or contradict anything that was just posted.

    I don't think your tables are complete or set up correctly. While I wade through your process related info, why don't you take a look at normalization here and/or here and see if you agree? If you have already researched the subject, I'm trying to tread lightly here but have to say I don't think you fully understand it. I have to always temper such assessments with the view that the complete process is seldom covered by anyone, but we can try making a start. If you (re)read up on the subject and then compare your understanding to what I (or I think we) will post on that I think you'll get somewhere.

    Ok, thank you.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking Subform to Main Form
    By Alex Motilal in forum Forms
    Replies: 1
    Last Post: 03-17-2019, 10:27 PM
  2. Linking to an Oracle Back End Part 2
    By Paul H in forum Programming
    Replies: 3
    Last Post: 01-23-2019, 09:45 AM
  3. Replies: 16
    Last Post: 03-22-2018, 09:27 AM
  4. Replies: 1
    Last Post: 02-23-2015, 07:06 AM
  5. Linking Subform Control to Main form
    By KWarzala in forum Forms
    Replies: 1
    Last Post: 03-13-2010, 08:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums