Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Problems getting numbers to match up in query

    Hello,

    I am hacking up a database that someone else made. This is for inventory counting dollar value.

    What I'm trying to do is have it return to me if something was adjusted out in 2009 then adjusted back in in 2010. This would show up as a negative dollar value in 09 and a positive of the same amount in 10.

    I thought I had it but here is what it would do:

    From table 2009
    Material Variance
    1234567 -123
    1234567 15

    From Table 2010
    Material Variance
    1234567 123
    1234567 0

    Whenever I try to make a query it gives me something like this:
    Material Variance09 Variance10
    1234567 -123 123
    1234567 -123 0
    1234567 15 123
    1234567 15 0

    How can I set this up so it actually gives me what I want? Here are pics of the queries I'm basing this off of:


    http://i779.photobucket.com/albums/y...joy/query1.jpg

    http://i779.photobucket.com/albums/y...joy/query2.jpg

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by chris07tibgs View Post
    How can I set this up so it actually gives me what I want?
    In an otherwise well constructed question you've shown us everything apart from what you actually want!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pootle flump View Post
    In an otherwise well constructed question you've shown us everything apart from what you actually want!
    oh yeah lol

    I want it to only show if something was adjusted out, and the same dollar value was adjusted back in. Where I get stuck is when a Material number is multiplied out because there are different values in each table.

    Example of what I would want it to look like:

    Material Variance 2009 Variance 2010
    1234567 123 15
    1234567 0 20

    Result: Nothing

    Or:

    Material Variance 2009 Variance 2010
    1234567 -123 15
    1234567 0 123

    Result:
    Material Variance 2009 Variance 2010
    1234567 -123 123

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So something like this in your where clause?
    Code:
    WHERE [Variance 2009] * -1 = [Variance 2010]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pootle flump View Post
    So something like this in your where clause?
    Code:
    WHERE [Variance 2009] * -1 = [Variance 2010]
    I'm going through the list looking for errors, but so far I can't find any! Looks perfect! Thanks!

Posting Permissions

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