Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: qry 2 tbl on 1 column change w/ 1 result?

    I'm new at this...

    I have a table reflecting now, and the same table from x days ago.

    I query both but only want the differences in a particular column D to display, not both tables. Just the parts of the tables based on the change in column D. I have the queries linked together and the design view is something like
    bla bla bla fields etc. w/
    Field: ColumnD
    Table: qryCurrentTbl
    ...
    Or:<>[qryPastTbl].[ColumnD]

    And it does display the changed value in column D plus all other associated data. However, it also feeds in the past data on the row below it.

    Like, customer X has date change in column D and in the row below it the same customer X shows previous date in column D. Nice for comparison but...

    How can I exclude the previous data and only display the current change?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So what are you going for exactly here? You want a recordset of the most recent table, but only records that have changed since the previous iteration?

    try something to this effect:

    SELECT t2.*
    FROM t2 LEFT JOIN t1 ON t1.someID = t2.someID
    WHERE t1.someID IS NULL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2005
    Posts
    3

    Thanx!

    I am trying joining two tables using a common ID and only returning data from a difference in a common column.
    I tried

    SELECT t2.*
    FROM t2 LEFT JOIN t1 ON t1.someID = t2.someID
    WHERE t1.someID IS NULL

    but I got too many results.

    my original, which I had help from our resident expert, looks more like

    SELECT t2.*
    FROM t2 Inner JOIN t1 ON t1.someID = t2.someID
    WHERE t1.someID <> t2.someID

    but it results in something like

    Customer last_order_date
    Frank Today
    Frank Last week

    whereas I don't need to know frank ordered last week, just today. um I guess what I'm trying to say is, the queries do join but they return *both* of the differences that I am extracting from them. And I'd rather have just the recent difference returned only.

    Is it the Join part that I'm not structuring properly?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're resident has a logic error

    FROM t2 Inner JOIN t1 ON t1.someID = t2.someID
    WHERE t1.someID <> t2.someID

    How can t1.someID = t2.someID AND t1.someID <> t2.someID at the same time?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2005
    Posts
    3

    that's pretty funny!

    my bad. From my sql view it's more like this

    SELECT [qry1].[A], [qry1].[B], [qry1].[C], [qry1].[D], [qry2].[D]

    FROM qry1 INNER JOIN qry2 ON [qry1].someID = [qry2.].someID WHERE ((([qry1].[D])<>[qry2].[D]));

    and I was able to cut the results in half by exchanging <> for >

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So don't include qry1.d?

    do you understand how that query works?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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