Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Assistance setting table relationships.

    I've managed to back myself into a corner with my table structure. Initially I really didn't need relationships but I find myself wishing I had set them earlier. (Probably an amateur mistake, but good thing I'm an amateur!)

    I have several tables that log specific information about different tasks during a flight. Each record for each table is uniquely identified by the flight number, however I just set the primary key as an autonumber.

    So in reality, there can be only one record, for each table, with a unique flight number.

    I want to set my relationships so that I can see which tables have a record with the same flight number.


    A visual example:

    Tables: Takeoffstuff LandingStuff Inflightstuff

    Records: 19923 19923 19923
    19924 19924 19924
    19925 19925 19925


    The Query: Which tables have records for flight # 11924

    The Result: Takeoffstuff, landingstuff, inflightstuff

    Thanks for any assistance as usual!
    Version: Access 2010

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you don't need relationships then its doubtful you need to use a relational db in the first place

    relational integrity is more about enforcing rules on data to make certain its more coherent, more accurate, more reliable. a relationship enforced in a db doesn't by it self do anything magical (you could enforce the same rules in software), you have to have more trust in your users, you have moire code to testr and verify).

    if you haven't alrady enforced relationships then now is a pretty good timne to do so, but you may well get data aerrors stopping you imposing relationships.

    you can define what effectively is a temporary relatiosnhip in the query designer (it will work only when ever that query is executed) by clicking and dragging accross columns/tables to define such a relationship. but such relationships are not as powerfull or flexible as a true table level relationship definition. they wont do anythign to stop you stuffing in bad data

    effectively what you want is a join
    select my, column, list from TakeOffStuff as TO
    join LandingStuff as LS on LS.FlightID = TO.FlightID
    join InflightStuff as IS on IS.FlightID = TO.FlightID

    access join syntax is a little different to standard SQL so youmay well need to teweak the above. if you can create your links int he query dewsigner do so, it will make your life easier


    in true relational terms you probably ought to have another table Flights, whcih stores everything that is pertinent to the flight including the flight number, which should be the primary key. from that you have your 3 current tables all using the flight number as a foreign key to flights
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    Ahha! Thank you sir!
    Version: Access 2010

Posting Permissions

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