Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2017
    Posts
    2

    Unanswered: Database for Child Advocacy Center

    I am trying to build a database for a child advocacy center where I work to keep track of cases. I have a main table containing an auto number as the primary key to identify each case. I also have fields in this table for the name record for our client (primary key in the names table), and fields for the suspect's name record id. I currently have them joined using a junction table for a many to many relationship. I need to be able to build a query that will bring all the information from the main table, and the fields from the names table where the client id and name id are equal, as well as where the suspect id and name id are equal.

    I am new to access and am stumped.

    In case it helps the name table id is the primary key in the name table and in the junction table. the mdtnumber is the primary key in the mdt table and in the junction table.

    Thanks in advance for your help.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    614
    Provided Answers: 32
    the autonums are mostly for Access references, you propably have a CaseID in all the tables.

    the tCases table would join to the tSuspects table on CASEID
    the tCases table would join to the tClient table on CASEID
    etc
    join all satellite table to the tCase.

  3. #3
    Join Date
    Sep 2017
    Posts
    2

    Thanks

    Quote Originally Posted by ranman256 View Post
    the autonums are mostly for Access references, you propably have a CaseID in all the tables.

    the tCases table would join to the tSuspects table on CASEID
    the tCases table would join to the tClient table on CASEID
    etc
    join all satellite table to the tCase.
    Ranman,

    Thanks for the suggestion. I don't have a common field between the main names table and the main mdt table. I have tried adding the client and suspects nameid fields to the junction table but can't seem to make it work. Would you be willing to take a look at the db if I compress and email to you?

    Thanks in advance.

    Dave

Posting Permissions

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