Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Design (2000)

    Hi,
    I seem to have a mental block on how to set this database up. It's either in my table designs or relationships or both or it can't be done. I suspect the former. [img]/forums/images/smilies/smile.gif[/img]
    I have attached a sample database. Let me give a brief history. We rebuild machines, lathes, drill, grinders, mills. So we have to order parts to complete the repairs. I am trying to create a parts database that will track the parts ordered. This db has 2 parts. Our mechanics have to fill out a DPL (defective parts list) that identifies all the parts on the machine that need replacing. That DPL goes out to Vendors for quoting (this part is not in the db at this time). Once quotes come back we have to take the parts from the DPL and place them on order. That's the basics of the database. My problem arises with putting the parts on order. I have the DPL portion working. The DPL and Order Forms work as follows. DPL: DPL per machine. One machine can have many components, one component can have many parts. Order Form (4172A): One Machine per OF (order form), One Vendor per OF, could have possible multiple componenets and multiple parts per OF. One machine could have many OF to get all the parts ordered. One Vendor per OF is the main concern.

    The only thing that is different on the sample database I've attached is the machine, employee, car and controller tables are linked to this database. It is not 100% necessary for this to happen, it would just be easier so I don't have to add/delete employees or add/delete machines from 2 databases.

    My main problem is figuring out how to get the parts on an order form. I would like it to happen like this. Choose a machine from a list, then the availble vendors that have parts for that machine will be available, choose a vendor from that list, which will show or have the parts that are linked to that machine and vendor, then the order form can be generated, filled in with the necessary data and printed.

    Thanks,
    Deb
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design (2000)

    What strikes me first is that you have a one-to-many relationship between tblVendor and tblParts. From your description, I would guess that it should be a many-to-many relationship (one vendor offers many parts for sale, one part can be sold by many vendors.) If that is correct, you need an intermediary table tblVendorParts in which each record represents a particular part sold by a particular vendor.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design (2000)

    Hans,
    That's somewhat true and I toyed with that option but couldn't get anything to work. What I mean by somewhat is, a Vendor could sell many parts and a part could be sold by many vendors. I didn't think that was an issue because once they send these parts out for quote, they come back with one vendor to order the part from and that's what I was tracking. Does that make a difference?
    Thanks
    Deb
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design (2000)

    In the database as posted, a part is linked to one vendor. You stated that you want to be able to see a list of vendors that have parts available for the DPL, and choose a vendor from that list; if there is only one vendor per part, you will have little choice... Or am I completely off?

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design (2000)

    Well, I don't think you're off, maybe I've explained it wrong or I'm off. Let me try and expalin again. Once the DPL is typed in, that portion is done. A Quote is sent out to find the vendor with the lowest price and avail. Once that happens, someone will go back and add a vendor to that particular part. On a particular DPL a vendor could be listed once or many times. To order those parts we have to use this 4172 form and one form goes to one vendor and only includes one machine and the parts associated with that machine and vendor. That's what my end result needs to be. I'm trying automate it somewhat because the people using the database aren't very computer savvy. I thought it would be easier for them to choose a machine. It would then tell them all the vendors that have parts for that machine (the list could be short, could have 1 vendor or 20), they can then select the vendor which would generate a parts list to be ordered for that vendor for that machine.

    Now if you can think of an easier way to generate this, please suggeest. None of this is in stone on how it has to be generated. While typing this I just had the idea of maybe having them put in all vendor info with pricing and just have them click a button that would generate 4172s for all vendors for a particular machine, they don't get to select.
    Thanks,
    Deb

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design (2000)

    I assume that this "4172 form" is something to be printed, so in Access terms it is actually a report. If you create a query that returns all parts (in no particular order), you can group them on the report by vendor, starting each group on a new page. The result would be a series of "forms" (report pages), one for each vendor.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design (2000)

    The 4172 report does need to be printed, but it has to have some data entered first that relates to that particular form such as a po#, quality and control need to initial and date it, they need to put in cost codes and such that pertain to the order which will pertain to the machine and vendor the order is going to. my problem is figuring out how to enter this data for the machine and vendor and corresponding parts, then print it.

    Thanks,
    Deb

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design (2000)

    I would not try to print the form (in Access terms). Create a form for data entry and a separate report for printing. Reports have specialized facilities for grouping and sorting data.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design (2000)

    I understand that and I misused my terminology. It was my plan to use the grouping feature for the report, but I'm having trouble creating the form for the entry. My relationships are not correct or I'm doing something else wrong to allow me to select a machine and then a vendor and then have the parts available for order so I can then fill in the fields necessary and then generate the report.
    Thanks,
    Deb

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design (2000)

    Any other Lounger with ideas? I don't think I can help here. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Design (2000)

    Is my explanation goofy or not understandable?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database Design (2000)

    I don't have much affinity with this type of database, so it would cost me too much time to get into it. I know that other Loungers who are active in this forum have more specific experience, so I hope they will be able to come up with something.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •