Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: Help with relationships!!!

    Hi guys,
    Here is my problem. As part of my internship at an investment firm I'm creating a database that contains all the major timberland owners in the US. My main table has about 300 firms/organizations that own timberland. My primary key in that table is simply an auto number that gives each entry a unique "Owner ID". One of the tables I'm trying to link in has all recent timberland transactions. I painstakingly went through the entire transactions table and looked for buyers and sellers that I already had in my main timberland owners database. If I found one, I entered the unique owner ID next to either the buyer or the seller. Therefore, I have two fields, buyer and seller ID that match up to the numbers in the primary key of my main table. What I want to do is form relationships between the owner ID column in my main table and both the seller and the buyer ID columns in my second table. That way, I can preform queries such as "Give me all recent transactions that Plum Creek was involved in". I would enter in plum creek under criteria for firm/organization in the field from my main table, and I would want the search to pull any line from my second table where plum creek was listed as the buyer or seller. This works if I only have one relationship say between the "owner ID" and "Seller ID" fields, but then I can only bring up recent transactions where plum creek is the seller. What I want is it to pull from both seller ID and buyer ID columns when there is a match with plum creek. Any time I try this double relationship, I either get no results since it wants both the seller ID and the buyer ID to match (which will never happen) or I get an error about an ambiguous outer join. Any idea how to do this?? Do i need two different recent transaction tables? Any help would be MUCH appreciated, and my boss would love me
    Attached Thumbnails Attached Thumbnails Query.png   Relationships.png   Transactions.png  

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Wow, you have this all over the place. As answered elsewhere:

    You need the same relationship in the query that you have in relationships: an aliased copy of the Firm/Organization table.
    Paul

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Access is not good at linking one column of one table to two columns in another. I think that you need to rethink your model for recording transactions. Try splitting transactions into headers and details. Your header table will hold the transaction ID, date and any other fields that apply to it. Your transaction details table will pick up the transaction ID, participant ID (owner ID from your table of organisations), participant role (buyer/seller/agent/guarantor/whatever) and any other fields. This table is keyed on transaction ID and participant ID. If you foresee the need to have one organisation carrying out more than one role within a single transaction, include the participant role in the key as well.

    BTW, you might want to consider renaming your tables, queries and columns to avoid characters that aren't letters, numbers and underscores. This will make it a lot easier to write and maintain queries and code in the future.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jun 2013
    Posts
    3

    New Tables

    Quote Originally Posted by weejas View Post
    Access is not good at linking one column of one table to two columns in another. I think that you need to rethink your model for recording transactions. Try splitting transactions into headers and details. Your header table will hold the transaction ID, date and any other fields that apply to it. Your transaction details table will pick up the transaction ID, participant ID (owner ID from your table of organisations), participant role (buyer/seller/agent/guarantor/whatever) and any other fields. This table is keyed on transaction ID and participant ID. If you foresee the need to have one organisation carrying out more than one role within a single transaction, include the participant role in the key as well.

    BTW, you might want to consider renaming your tables, queries and columns to avoid characters that aren't letters, numbers and underscores. This will make it a lot easier to write and maintain queries and code in the future.
    Ok, i think I see what you are saying. So would my header table have everything that's in my transactions table right now? and how would the entries in the details table work? would I have two entries for each transaction, one with the participant ID of the seller and one with the participant ID of the buyer?

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The header table will not have everything that's currently in the transaction table - that would defeat the point! It will have the fields that contain information that is unique to the transaction - ID, date, possibly location*. The other fields will be moved to the details table.
    You will have at least two detail records per header record - one for the seller and one for the buyer. If the land being sold is going to more than one buyer, you will have one detail record for each of them, in addition to the seller. If you wish to record that a sale went through an agent or broker, you can record that as well. Similarly if someone is acting as a guarantor to either party, you could record that, too.

    *Taking another look at your thumbnails, I would advise you to split the locations for each transaction into another table as well. You have multiple locations in some records - this is a terrible violation of database normalisation, and will make queries and changes far more difficult than they need to be. Instead have a table for transaction locations, which will hold one record per transaction ID/location ID combination. It's a little more work to set up, but it will make future maintenance much easier.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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