Results 1 to 3 of 3
  1. #1
    wulfhund is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    2

    Have 3 tables - problem with pulling data for query

    I am trying to do a database for a rescue groups adoptions. I know I'm doing something wrong:

    We have 3 tables: Adoptions, Animals, and People.

    The people just has names with contact information.
    The animals has the animals info: name, age, color, breed, all that.
    The adoptions has info on who adopted and surrendered the animal.

    What I wanted to do was create a query for finished adoptions and unfinished. Since some people adopt more than one animal, I have in the adoptions animal1, animal2, animal3, and animal4.

    I wanted to be able to do reports or queries with who adopted or surrendered along with names of animals and ages, sex, color. But I can't add the sex or color - the query just doesn't work then.

    I don't know how to get it to work. I basically want it to reference the animals' record and pull the age/color/sex for the right animal....but I'm completely lost as to how to do this if I have it set up the way it is.

    First table fields:

    AdoptionID (primary key)
    Animal1
    Animal2
    Animal3
    Animal4 (these all refer to the animal table)
    Adopter
    Source (these both refer to the people table)
    DateOfAdoption
    FinishedAdoption (checkmark y/n)

    2nd Table fields:

    Person (primary key)
    CellPhone
    HomePhone
    WorkPhone
    Address
    City
    State
    Zipcode
    Email

    3rd Table fields:

    Animal


    Age
    Sex
    Color
    LikesOthers
    LikesDogs
    LikesCats
    Food
    ComesWithCage
    LitterTrained
    MedicalCondition
    Vaccinated
    VaxDate

    If anyone can help me I'd be very grateful!

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    well this is a fairly classical database requirement. You have an Animal table - this must have a primary key autonumber. You have a People table - this must have a primary key autonumber. Your Adoption table is in fact a transaction table that cross references the 2, along with transaction specific information. This table also needs a primary key autonumber field.

    Although you did not ask, I would strongly recommend you consider each adoption a unique event. Yes, people will adopt more than 1 animal at once - but it would be best if each is processed as a separate adoption with its own unique ID.

    I think when your tables, keys & relationships are in place your queries will work.

  3. #3
    wulfhund is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    2
    Thank you, I have it this way now and I have a different question - not sure if I should ask here, or create another thread.

    Basically I have a adoption info table that has who is adopting, so it has:

    new owner
    old owner

    Both of these have a dropdown to the person field in the contact list.

    Now - what I want to do on one of my input forms is have a location box, where it shows the city and state of the animal using the old owner as the source. However, I don't know how to do this, or if it is even possible, to tell it to use the old owner name, lookup the city/state using the name or the ID number, and display it, no matter how much I dig around.

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

Similar Threads

  1. Pulling only certain data from tables.
    By stevman22889 in forum Access
    Replies: 2
    Last Post: 07-15-2010, 06:23 PM
  2. Query problem with multiple tables
    By DanW in forum Queries
    Replies: 1
    Last Post: 11-20-2009, 06:23 PM
  3. Tables and “all data query” ID problem
    By mashe in forum Queries
    Replies: 5
    Last Post: 08-22-2009, 06:05 AM
  4. Replies: 0
    Last Post: 07-27-2009, 07:51 AM
  5. Pulling most recent data from child table
    By davidv43 in forum Access
    Replies: 0
    Last Post: 02-07-2009, 11:36 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