Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships (2000)

    Hi..... I feel a bit stupid but about 8 years ago I created a Vendor Tracking database and I am trying to recreate it and can't remember what I am doing. So here goes...... Any help would be greatly appreciated.

    Purpose of the Database: To be able to view Sales Reps form and pick a list of vendors from that form and have it show the vendor information including Name, Address, ect... Products carried either warehouse or stocked and a comments fields show last contact and date of contact sorted by most recent contact date.

    What I have setup so far is pityfull...... I have 3 tables..... Sales Rep with fields named ID(Primary Key), Sales_Rep and Vendor_ID.

    Table 2 is Vendor with fields named Vendor_ID(Primary Key), Name, Phone, Ext, Fax, Alt_Contact, Sales_Rep, Products_SKU, Warehouse, Sold and Comments both Warehouse and Sold are Yes/No fields

    Table 3 is Products with fields named ID(Primary Key), Products_SKU, Warehouse, Sold and Vendor_ID

    Relationships setup: Sales Rep Table to Vendor Table: ID Field to Sales_Rep Field (One to Many)
    Vendor Table to Products Table: Vendor_ID to Vendor_ID (One to Many)

    Now I can enter multiple products for each vendor but can't have multiple vendors for each sales rep. And then of course I cant remember how to setup the form structure I want either. Brain very rusty since I haven't done any database work in about 4 years.

    Well that is the scenario if anyone can point me in the right direction it would be great.

    Thanks in advance for any help.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    "but can't have multiple vendors for each sales rep. "

    Do you also want multiple sales reps for each vendor?
    If so you have a many-to-many relationship. Take the VendorID our of the Sales rep table, and create a linking table that contains both a VendorID and SalesRepID. Either make them joint keys, or create a separate autonumber Key. This table associates Sales Reps with Vendors. Otherf ields would be anything else that specifiically relates to the connection between a vendor and a sales rep. May not be any other fields.
    Regards
    John



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

    Re: Relationships (2000)

    John's advice is sound, he has pointed you in the right direction. You will find a simple example of forms and subforms used to implement a many-to-many relationship attached to <post#=364203>post 364203</post#>.

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    So I would create a relationship from the Sales_RepID field in the Sales Rep table to the linking table which is a table with 2 fields Sales_RepID(Number) and Vendor_ID(Number) both defined as primary keys. One to many from Sales_RepID to Sales_RepID and then One to Many from Vendor_ID to Vendor_ID in the Vendors table? That didn't seem to work.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    What you say sounds OK. What do you mean by "That didn't seem to work." ? what actually happened.

    I notice in your original post you also had a Sales_Rep field in the vendors table. You don't need that either.
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Relationships (2000)

    You also don't need Products_SKU in the Vendor table.

    Post your database and lets see what you have done so far.

  7. #7
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    OK I have the tables setup exactly as you laid them out I have 1 to many relationships and I checked all 3 boxes in the edit relationships dialog.

    Now I need to create a form that I can pull up from a drop down on a Main form that would open a form showing the sales rep and all the vendors for that rep. Then I want to pick a vendor on the form and have it open another form to show the vendor information Name, Phone, Ext, Fax, Alt Contact and the Products they carry and see if they warehouse or store checkbox. Then on that same form below the products I want to see the comments for that vendor sorted by date of last contact.

    I think I need one more table a Vendor Comments table so I can have multiple comments appearing with date of contact and comments for that date. I created a VendorComments Table with the fields Vendor_ID(Number and Primary Key) Comments (Memo) and Contact Date(Date) and I created a relationship from the Vendors Table to the VendorsComments table from Vendor_ID to Vendor_ID but it only creates a 1 to 1 relationship....... Is that correct?

    How do I get the file small enough to upload? I have nothing really setup except the tables right now but the file size is ove 100k

    Thanks everyone for all the help....... I have attached an image.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    Because you want to have multiple comments about the one vendor, don't use the VendorID as the key of the comments table. Create a new autonumber field, VendorCommentID and make that the key. Keep the vendorID, as a relationship with the Vendor table, but once it is not the key, you can have multiple comments.

    To upload it, first Zip it. That should make it small enough.
    Regards
    John



  9. #9
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    Got it........ I am attaching the database....... Here is the table structure now and relationships. Oh yeah when veiwing the Sales Rep form not created yet I would like to be able to add vendors on the fly including their information.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    I have made a little bit of a start on this. Have a look at this, then take it one step at a time from here.

    There is a menu with a list of reps. Select one and you can see the vendors associated. You can also Add Vendors here.

    I suggest you create a form that shows a vendors details: a form with two subforms: vendors details in main form, and subforms for products and comments.
    You could put a command button in the footer of the list of vendors associated with a rep, that would show you the details of that vendor. The command button wzard will do the work.

    You can also add new reps from the menu.
    Regards
    John



  11. #11
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    Ok I have created forms like I want them but not funtional completely. I guess what I am looking to do is from the main page pick a Rep and it will open a sales rep detail page Showing the rep and the vendors associated with that rep. Also in the same form I would like to be able to add a new rep and the Vendors that are associated with that rep. Also to be able to pick from a dropdown a vendor and then the vendor details form would open up. Then from the main form I would like to be able to pick a vendor from the drop down and the vendor form open up and would like to be able to add a vendor and associated sales rep from there. On Main form I would like each button to open to a blank record to add the rep or vendor depending on which button is pressed.

    Hopefully I explained it enough.

    I am attaching the file.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    I have not looked at your attachment yet, but I am confused by your post.

    "I guess what I am looking to do is from the main page pick a Rep and it will open a sales rep detail page Showing the rep and the vendors associated with that rep. "
    What I posted already did that, I thought.

    You also want to add new reps. You could do that from the Rep details screen, but you have to choose a Rep to get there, so that did not make a lot of sense to me. So I put it on the menu. Given that there is only one field for a rep : the name of the rep. There was not need for a separate form for this.

    Next you want to :" Also to be able to pick from a dropdown a vendor and then the vendor details form would open up. " You tell me what went wrong with your attempt at this, or where you got stuck, and I will try to help.
    Regards
    John



  13. #13
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relationships (2000)

    Either my relationships are wrong or I created the forms wrong because I can't seem to get reps associated with vendors correctly

    Main Page...... There is a dropdown list with Rep Name and a button to add rep. If I click the button I would like to go to a blank record to add the rep name and the vendors they are associated with. If I pick a rep name from the dropdown I want the rep details to open up showing the Rep name and all associated vendors in a list. If I click a vendor on the list in the rep details then the Vendors form Details would open up.

    Main Page...... There is a dropdown with Vendor Name and a button to add a vendor. If I click the add a vendor button I would like to open up to a blank record to add the vendor, associated rep or reps and products carried and comments if nessecary so the vendor detail form I think will work fine for that just need to add the associated rep so that we don't have to return to the rep screen to do it. If I pick a vendor from the dropdown list I would like the vendor detail form to open up to that record.

    I think I have explained it right. I have created the forms in the uploaded file but not correctly.

    Thanks for all the help.

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    I now have looked at your attachment, and see that you have not used the db I posted, but started again.

    Did you look at the db I posted?

    Each of your command buttons just has code : DoCmd.GoToRecord , , acNewRec.
    This will try to go to a new record in the current form, which does not have records. Instead you need to choose to open the relevant form at a new record.

    You also need a command button next to each list that you open the relevant form at the person selected. The command button wizard will do that.

    Your vendors detail form correctly shows all the products for that vendor, and the commendts for that vendor. To view assocaited sales reps, you need a third subform, not just a text box. this subform will as its source a query using both sales rep and the linking table. With three subforms, it might be better to use a tab control to say hide the comments until you want to see them.
    Regards
    John



  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Relationships (2000)

    You can have the other form open after you pick a name from the list, although I would not because you would end up opening the other from when you did not want to. If you look in the list to see who is there, you will open the other from whether you like it or not.

    To do what you want, first create a commad button to do the job (using the wizard) and select the option of finding a specific record. When it works, take the code behind the button, and copy it into the after update event of the combo box.

    Try doing that with the one I posted and you will see that it works OK. If you used a listbox (instead of a combo box) you could also use the double click event for the same job. That requires the user to consciously choose to open the form.

    You can change between listboxes and combo boxes by right clicking on either and choose change to from the popup menu.


    This code will open the form "Sales rep" at a new record.

    Dim stDocName As String
    stDocName = "SalesRep"
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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