Results 1 to 3 of 3
  1. #1
    YDav is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    1

    Question Help with a database

    Hello. I am looking for help with some seemingly simple database that gives me troubles.

    Scenario: there are apples and bananas.
    Apples could be delivered from Farm A or Farm B.
    Bananas could be delivered from Farm A or Farm C.
    My contacts at Farm A regarding Apples: Mike and John
    My contacts at Farm A regarding bananas: Mike, John and Peter
    My contacts at Farm B regarding Apples: Nick and Sean


    My contacts at Farm C regarding bananas: George

    end result should be in a form: I need to order Apples (drop down menu) - it should show me that I have two options: Farm A or B, chose Farm A ( drop down menu), this should generate an email distribution list, click on a button and generate a generic prepopulated email to Farm A distribution list (TO,CC, subject, body of the email)

    My thought that I would need three tables:
    1. fruitID, FruitName
    2. farmID, FarmName
    3. contactID, Contact name, phone number, email, FarmName

    would you please give me your thoughts...

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    thoughts are:

    farmname in table 3 should be farmid

    you need a junction table to join fruit to contacts

    JunctionID, FruitID, ContactID

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'd add FarmID to that table, or else you'll never be able to query which farms have the apples. FruitID should be indexed, allowing duplicates, since OP says a fruit can have more than one contact person. A less normalized approach would be to forego the junction table and have the FruitID in tblFarms, allowing duplicate farm names, one for each fruit ID, which might make the FarmID field kind of pointless. But something tells me the db in question is not as simple as it's made out to be, so I'm not saying that's how I'd do it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  2. Replies: 1
    Last Post: 03-26-2016, 06:42 AM
  3. Replies: 1
    Last Post: 07-01-2015, 04:18 AM
  4. Replies: 1
    Last Post: 03-21-2015, 11:55 AM
  5. Replies: 2
    Last Post: 01-28-2013, 04:42 PM

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