Results 1 to 8 of 8
  1. #1
    jimmy2002 is offline Novice
    Windows XP Access 2013
    Join Date
    Nov 2015
    Posts
    1

    Access Relationship

    So I have 6 tables: Doctors, Drugs, Health Plans, Patients, Refills, and Rx.

    Doctors have fields of ID (primary key), Doctor ID, First Name, Last Name, and Phone
    Drugs have fields of UPN (primary key), Name, Generic, Description, Unit, Dosage, Dosage Form, Cost, and Price/Dosage
    Health Plans have fields of ID (primary key), Plan ID#, Plan Name, Address, City, State, Zip, and Phone
    Patients have fields of Patient ID (primary key), first name, last name, phone, date of birth, gender, health plan, and allergies
    Refills have fields of ID (primary key), prescription id, and refill date
    Rx have fields of prescription id (primary key), UPN, quantity, unit, date, expire date, field7, auto refill, refills used, instructions, id, and doctor



    I just need help since I'm new to access and the professor hardly even taught any of this. I'm struggling to find relationships for tables of patients, drugs, and health plans. Thank you!

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    to get proper help you need to explain what you want to do with this data. For example drugs are prescribed by doctors so you would need a linking table to contain the primarykey of the doctors, drugs and patients plus perhaps a quantity and date - so the table data is effectively saying 'this doctor prescribed this quantity of this drug to this patient on this day'

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Some hints:

    "Name" is a reserved word in Access and shouldn't be used for object names.

    Object names should be letters and/or numbers (underscore is the exception). NO spaces, punctuation or special characters.
    For example, "Price/Dosage" could be "PriceDosage" or "Price_Dosage".

    "ID" is a poor name because it is not descriptive. You have 3 tables with "ID" as the PK. In table "RX", there is a field "ID" - which table does "ID" refer to?



    You should use pencil and paper or white board or cardboard and crayon or ??? to design your table structure. (I use a window and a dry marker)

    Work through the tutorials at Rogers's Access Library: http://www.rogersaccesslibrary.com/forum/forum46.html

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Work through this tutorial to learn and experience the basics, then apply it to your situation.
    Work through the tutorial in 30-45 minutes.
    Good luck.

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Quote Originally Posted by orange View Post
    Work through this tutorial to learn and experience the basics, then apply it to your situation.
    Work through the tutorial in 30-45 minutes.
    Good luck.
    you should make a macro for this post mate .

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Andy,

    I don't think a macro would be anymore successful.

    I see posters like Jimmy every day. His issue
    I just need help since I'm new to access and the professor hardly even taught any of this. I'm struggling to find relationships for tables of patients, drugs, and health plans. Thank you!
    is fairly common. Poster attended classes, but the teacher just raced over the Access stuff while attempting to teach underlying database concepts.

    I think a major part of this problem is the marketing job done by M$oft (and others). Excel was, and is still successful. All the Office products are similar.....so get Access and you'll be able to build databases.

    The best analogy I have is driving and flying -- Flying an airplane is very similar to driving a car ---steering wheel, brakes, instruments, lots of automation/gadgets..... it's just 1 more dimension (what can be so difficult). Oh yes, we just skipped over the concepts of flight, drag, wind direction and speed, stall conditions, and regulations/policies and licenses and certification......

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    No I understand, I was just joking because a lot of the time you give the same advice. Rightly so

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Andy,

    But your question is a good one. How can people spend months dabbling, or at best - trial and error. Workarounds, frustrations...? But can't spend an hour or 2 learning the basics/working through a couple of tutorials.
    Say 1 on Normalization and one on relationships/entities/data model.

    Mind you, I have seen people painting floors, or attempting to solder copper tubing, or assemble a DIY toolshed,...
    ithout ever watching a video, or reading instructions...

    I guess that's the evolutionary process.........

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

Similar Threads

  1. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  2. Replies: 4
    Last Post: 04-17-2014, 09:39 AM
  3. Replies: 4
    Last Post: 09-05-2012, 07:26 AM
  4. Access Relationship Question
    By glassjames in forum Access
    Replies: 2
    Last Post: 05-22-2012, 07:01 PM
  5. Relationship problems in access
    By danish raza in forum Database Design
    Replies: 1
    Last Post: 07-10-2011, 05:50 AM

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