Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    Unanswered: Using information from different tables in single field a query

    Hi

    Can anyone tell me how to do this.

    Ihave a database with multiple table but for what I need help with I only need three.

    table 1 - field = branch
    table 2 - field = branch
    table 3 - field = dateadded

    what I need to do is run a query where if the dateadded is before 01/01/04 then it will take branch data from table 1, if it's after 01/01/04 then it will take data from table 2.

    I've tried a few methods but none have worked. I think it might be a union query or something like that but I'm not sure.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Based on the assumption that you have a way to tie table three to one and two The following query will get you going in the right direction


    SELECT TableThree.DateAdded, IIf([TableOne.Branch] Is Null,[TableTwo.Branch],[TableOne.Branch]) AS BranchData
    FROM TableTwo RIGHT JOIN (TableOne RIGHT JOIN TableThree ON TableOne.Branch = TableThree.Branch) ON TableTwo.Branch = TableThree.Branch
    ORDER BY TableThree.DateAdded;


    TableOne
    Field -> Branch
    South
    West


    TableTwo
    Field -> Branch
    East
    North


    TableTHree
    Fields -> Branch | DateAdded
    East | 1/5/2004
    North | 1/1/2004
    South | 12/31/2003
    West | 2/2/1989


    Results
    Fields -> DateAdded | BranchData
    2/2/1989 | West
    12/31/2003 | South
    1/1/2004 | North
    1/5/2004 | East

    S-

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This sounds like you don't have normalized data. If you have two identicle tables which you are pulling information from based on a date field in yet another table, is there a reason you wouldn't combine all the information into one table and include a dateadded field?

  4. #4
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    Yeah, unfortunately, we need to maintain the previous branch structure in all the reports we run. Which is why I thought a 2nd table would be the best way to maintain the old data but incorprate the new data as well.

Posting Permissions

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