Results 1 to 3 of 3
  1. #1
    tdelios is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    8

    Need Guidance On 2 Table Designs, Relationships & Lookups

    Hello All..



    I am new to Access 2013 and have a question regarding linking 2 tables together.


    I have a table called 'TEAMS' that stores team information from different professional sport leagues (ie. NHL, MLB, NBA etc). Therefore there will be multiple records that have the same team name (ie. TORONTO) but the Sport League column value (ie. "NHL") will identify which sport those teams will be associated to.
    More importantly, there is a 'Team Identifier' column which is considered the unique identifier as this value will never be duplicated in any row of records.


    I have a second table called "SPORT SCORES" that stores the history of each game of each of the proessional sport leagues. Within it, I want it to contain the 'Home Team' and 'Visitor Team' with their respected 'Team Identier' values. The "Home Team" and "Visitor Team' drop down list in a form should display the values that are in the TEAMS table and in addition to that should be filtered based on the Sport League column that is selected on the form.


    I need to know first how do I link (Relationships) the TEAMS and SPORT SCORES tables together as I am thinking that I need 2 connections considering that I am looking up both the Home Team and Visitor Team from the SPORT SCORES table to the TEAMS table. The second objective is to create some sort of 2 list views/data grids within the TEAMS form. One to filter records from the SPORT SCORES table as that team is considered the 'Visitor Team' and another to filter the records from the SPORT SCORES as that team is considered the 'Home Team'.


    Can someone give me some insights how to approach this including what data types I should be using when constructing my tables in order to achieve both the lookups from one table to another as well as to have the Relationships in place for both tables.

    Any help will be welcoming!

    Cheers
    Tom

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I suggest you create an ERD for your database before you begin. You can watch tutorials online to get you started. When you have some basic knowledge and after fumbling around with the design aspect of your tables, you will have new questions. The answers to those questions will bring you closer to a solution.

    https://www.youtube.com/watch?v=-fQ-bRllhXc

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I would think you are fine as it is. In sports score tbl, maybe a column that contains one of the 2 Team IDs

    SCORES
    homeID
    VisitID
    DateOfGame

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

Similar Threads

  1. Database Designs & Relationships
    By jciabattoni in forum Database Design
    Replies: 2
    Last Post: 02-24-2015, 06:36 AM
  2. Help with multi table lookups report needed
    By gjt211 in forum Reports
    Replies: 3
    Last Post: 02-03-2015, 10:07 PM
  3. Replies: 7
    Last Post: 09-06-2011, 08:00 PM
  4. Optimising lookups in a large fixed table
    By u38cg in forum Queries
    Replies: 4
    Last Post: 06-22-2011, 08:21 AM
  5. No Lookups in a Table
    By oleBucky in forum Database Design
    Replies: 9
    Last Post: 03-23-2011, 01:40 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