Page 3 of 3 FirstFirst 123
Results 31 to 32 of 32
  1. #31
    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
    you keep missing out steps
    this I understand

    also understood - the drop down uses tblAssy_Inv as a rowsource.

    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


    Ahhh, ok. Yes, I'm simply relying on the user knowing which parts and what quantity is required. The technician working on the assembly will write down the part and quantity they use. They use a Technical Drawing that lists the parts to use. And they in turn will write it down on a consumption report. None of the technicians will be the ones entering this information into the database. In fact, I will be the one entering the data from their Build to Order worksheet.

    I'm not exactly sure how I would incorporate a table that contains required parts for each assembly yet. I would like to, because it would make data entry much easier.

    Yes, the transaction or the quantity that is used is placed in the consumed table. And the adjustment to the Main_Inv is done once that "Consume" button is clicked on.

    And still you haven't answered is partnumber and part_number the same thing? If so why is it in tblBTO?
    I answered this in post 20. "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." It's suppose to be "PartNumber is the same as Part_Number."

    As already stated, you have a link - why is this not acceptable?
    I do have a link from the BTO table to the Parts consumed table in the relationships.


    I was going through my database tonight and trying to make a flow chart on how everything works and it looks like I may have some stuff that is no longer displaying some information. So, I'm going to work on that for now.

    Thanks for your time. I'm going to check out some of the links you and others have posted on here.

  2. #32
    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
    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
    Ok, thank you. I am writing down the processes right now to try and get a better understanding. And while doing so, I found some stuff that needs to be fixed. ^^;

Page 3 of 3 FirstFirst 123
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