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

    Unanswered: field in query with data from one of to sources depending on the date.

    Hi,

    I've asked this or something similar a few times but I haven't got an answer that I've found useful.

    Basically we have a database that has been in place for some time, we're a large company and have branches throughout the country. Recently there has been a change in our branch structure, some have moved into different areas or regions or even merged with other branches.

    What I need to be able to do is have a query that looks at a field in table 1 (order) the has the date the order was made and if the date is before 01/01/2004 then it'll take information from table 2 (old branch) if it's after 01/01/2004 then it'll take the data from table 3 (new branch) each of these will have a field called BranchID.

    I know there is a big question on the normalisation of this database however we have to be able to report correct information as it was then and as it is now. So changing the branch table is not an option (I've done this for the moment but have to come up with another solution).

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What's the problem? Do it in code ... How is this "app" written? Is it queries and macros or is it VBA code? Your options differ with how it's written/developed.

  3. #3
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    Originally I don't know, all I have is the original MDE file and I'm building a new one from that I've got the tables form queries & reports from the mde. now I'm trying to build everything else around that foundation.

    What would you suggest? I've tried a union query but it just merged everything.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do it in code? oh ye of little faith!
    Code:
    select O.stuff , OB.stuff
      from orders O
    inner
      join oldbranch OB
        on O.BranchId - OB.BranchID
     where O.orderdate < '2004-01-01'
    union all     
    select O.stuff , NB.stuff
      from orders O
    inner
      join newbranch NB
        on O.BranchId - NB.BranchID
     where O.orderdate >= '2004-01-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by r937
    do it in code? oh ye of little faith!
    Code:
    select O.stuff , OB.stuff
      from orders O
    inner
      join oldbranch OB
        on O.BranchId - OB.BranchID
     where O.orderdate < '2004-01-01'
    union all     
    select O.stuff , NB.stuff
      from orders O
    inner
      join newbranch NB
        on O.BranchId - NB.BranchID
     where O.orderdate >= '2004-01-01'
    Nice query ... Still has nothing to do with putting it into code ... So I guess I was not particularly helpful ...

  6. #6
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    I've tried this but it keeps coming up with an error, any ideas?

    SELECT tblcase.IntCase, tblcase.DateAdded, [Tblsortcode_branch until dec 03].Branch, [tblArea until dec 03].StrArea, [tblRegion until dec 03].StrRegion
    FROM ([tblRegion until dec 03] INNER JOIN ([tblArea until dec 03] INNER JOIN [Tblsortcode_branch until dec 03] ON [tblArea until dec 03].IntArea = [Tblsortcode_branch until dec 03].IntArea) ON [tblRegion until dec 03].IntRegion = [Tblsortcode_branch until dec 03].IntRegion) INNER JOIN tblcase ON [Tblsortcode_branch until dec 03].BranchID = tblcase.BranchID
    WHERE tblcase.DateAdded <#01/01/2004#
    UNION ALL SELECT tblcase.IntCase, tblcase.DateAdded, [Tblsortcode_branch].Branch, [tblArea].StrArea, [tblRegion].StrRegion
    FROM ([tblRegion] INNER JOIN ([tblArea] INNER JOIN [Tblsortcode_branch] ON [tblArea until dec 03].IntArea = [Tblsortcode_branch].IntArea) ON [tblRegion].IntRegion = [Tblsortcode_branch].IntRegion) INNER JOIN tblcase ON [Tblsortcode_branch].BranchID = tblcase.BranchID
    WHERE tblcase.DateAdded >=#01/01/2004#;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gee, i wonder what the error could be

    any chance you could post that as well?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    just a syntax in join opertion

  9. #9
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    it's ok I found it,

    it was the

    [tblArea until dec 03].IntArea = [Tblsortcode_branch].IntArea) in the second half of the query.

    d'oh

    thanks.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in the 2nd query in the union, you refer to [tblArea until dec 03] in one of the ON conditions, but it isn't one of the tables in the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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