Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    6

    Unanswered: Please help with Access query with groupby on Union All query

    Hello,
    I created an access query with union. Everything is working fine now. But i would like to eliminate the duplicate records if more than one have the same Full street address.
    Here is my query:

    SELECT tableA.LOW_Right as From, tableA.UP_Right AS To, 1 AS Status, tableA.STREET AS FullStreetAddress
    FROM tableA
    WHERE (((tableA.[LOW_Right]) Is Not Null And (tableA.[LOW_Right])>0)) OR (((tableA.[UP_Right]) Is Not Null And (tableA.[UP_Right])>0));
    UNION ALL
    SELECT tableA.LOW_Left AS From, tableA.UP_Left AS To, 0 AS Status, tableA.STREET AS FullStreetAddress
    FROM tableA
    WHERE (((tableA.LOW_Left) Is Not Null And (tableA.LOW_Left)>0)) Or (((tableA.UP_Left) Is Not Null And (tableA.UP_Left)>0));


    This is the result from above query:
    From To Status FullStreetAddress
    1000 5000 1 1251 Smith Rd
    1000 5998 1 1251 Smith Rd
    3001 6701 0 521 St. Albert St
    1581 6701 0 521 St. Albert St
    125 4523 1 1258 Bellpart

    Now i need help to show only those records with no duplicate on FullStreetAddress.

    For each tableA.STREET, If LOW_Right > 0 and there are multiple rows of tableA.STREET and LOW_Right that have the same value, only the row that has the highest UP_Right value should be shown

    For each tableA.STREET, If LOW_Left > 0 and there are multiple rows of tableA.STREET and LOW_Left that have the same value, only the row that has the highest UP_Left value should be shown

    For each tableA.STREET, If UP_Right > 0 and there are multiple rows of tableA.STREET and UP_Right that have the same value, only the row that has the lowest LOW_Right value should be shown

    For each tableA.STREET, If UP_Left > 0 and there are multiple rows of tableA.STREET and UP_Left that have the same value, only the row that has the lowest LOW_Left value should be shown


    Result should be shown like this:

    From To Status FullStreetAddress

    1000 5998 1 1251 Smith Rd (show highest UP_RIGHT)
    1581 6701 0 521 St. Albert St (show lowest LOW_left)
    125 4523 1 1258 Bellpart

    Thanks in advance.
    I'm very appreciated for your help.

    TN
    Last edited by tweetyng; 06-05-15 at 15:58.

  2. #2
    Join Date
    Apr 2007
    Posts
    6

    Please help with Access query with groupby on Union All query

    If i try to group by, it's only worked on the first 2 parts
    For each tableA.STREET, If LOW_Right > 0 and there are multiple rows of tableA.STREET and LOW_Right that have the same value, only the row that has the highest UP_Right value should be shown
    For each tableA.STREET, If LOW_Left > 0 and there are multiple rows of tableA.STREET and LOW_Left that have the same value, only the row that has the highest UP_Left value should be shown

    These, I don't know how to do on these parts
    For each tableA.STREET, If UP_Right > 0 and there are multiple rows of tableA.STREET and UP_Right that have the same value, only the row that has the lowest LOW_Right value should be shown
    For each tableA.STREET, If UP_Left > 0 and there are multiple rows of tableA.STREET and UP_Left that have the same value, only the row that has the lowest LOW_Left value should be shown


    SELECT tableA.LOW_Right as From, Max(tableA.UP_Right) AS To, 1 AS Status, tableA.STREET AS FullStreetAddress
    FROM tableA
    GROUP BY tableA.LOW_Right, tableA.STREET
    HAVING (((tableA.LOW_Right) Is Not Null And (tableA.LOW_Right)>0)) OR (((Max(tableA.UP_Right)) Is Not Null And (Max(tableA.UP_Right))>0));
    UNION ALL
    SELECT tableA.LOW_Left AS From, Max(tableA.UP_Left) AS To, 0 AS Status, tableA.STREET AS FullStreetAddress
    FROM tableA
    GROUP BY tableA.LOW_Left, tableA.STREET
    HAVING (((tableA.LOW_Left) Is Not Null And (tableA.LOW_Left)>0)) OR (((Max(tableA.UP_Left)) Is Not Null And (Max(tableA.UP_Left))>0));

    Results show below:

    1000 5998 1 1251 Smith Rd (worked, after group by)
    3001 6701 0 521 St. Albert St (but this record is still showing, need to eliminate this)
    1581 6701 0 521 St. Albert St (need to show only this record)
    125 4523 1 1258 Bellpart

    TN
    Last edited by tweetyng; 06-05-15 at 15:59.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi
    Try This
    Code:
    SELECT tableA.LOW_Right as From, Max(tableA.UP_Right) AS To, 1 AS Status, tableA.STREET AS FullStreetAddress 
    FROM tableA
    GROUP BY tableA.LOW_Right, tableA.STREET
    HAVING (((tableA.LOW_Right) Is Not Null And (tableA.LOW_Right)>0)) OR (((Max(tableA.UP_Right)) Is Not Null And (Max(tableA.UP_Right))>0));
    UNION ALL
    SELECT MAX(tableA.LOW_Left) AS From, tableA.UP_Left AS To, 0 AS Status, tableA.STREET AS FullStreetAddress 
    FROM tableA
    GROUP BY tableA.UP_Left, tableA.STREET
    HAVING (((tableA.LOW_Left) Is Not Null And (tableA.LOW_Left)>0)) OR (((Max(tableA.UP_Left)) Is Not Null And (Max(tableA.UP_Left))>0));
    I think The MAX() aggregate function in the second query should be on the first field (with appropriate group clause).

    ???


    MTB

  4. #4
    Join Date
    Apr 2007
    Posts
    6
    Thanks MikeTheBike for your time.
    I tried as you suggested but i didn't get quite right on results. I even tried Min()aggregate function on the 1st field in the second query.
    I was thinking about trying to create subquery but couldn't figure it out yet.
    Thanks again.Twee

  5. #5
    Join Date
    Apr 2007
    Posts
    6
    I tried to create a query below. It's weird that when i copied the table with left few records in there, it's worked and showed corrected results. However, when i switched back to the original table with thousands, thousands records in there, and it's not working. It's showing single record in there.
    What did i do wrong?


    SELECT tableA.LOW_Right AS sFrom, tableA.UP_Right AS sTo, 1 AS Status, tableA.STREET AS FullStreetAddress FROM tableA
    WHERE (((tableA.LOW_Right) Is Not Null And (tableA.LOW_Right>0 And (tableA.LOW_Right) In (SELECT MIN(tableA.[LOW_Right]) FROM tableA GROUP BY tableA.STREET,tableA.UP_Right)) AND ((tableA.UP_Right) Is Not Null And (tableA.UP_Right)>0 And (tableA.UP_Right) In (SELECT MAX(tableA.[UP_Right]) FROM tableA GROUP BY tableA.[STREET],tableA.[LOW_Right])));
    UNION ALL SELECT tableA.LOW_Left AS sFrom, tableA.UP_Right AS sTo, 0 AS Status, tableA.STREET AS FullStreet,Address
    FROM tableA
    WHERE (((tableA.LOW_Left) Is Not Null And (tableA.LOW_ Left)>0 And (tableA.LOW_ Left) In (SELECT MIN(tableA.[LOW_ Left]) FROM tableA GROUP BY tableA.STREET,tableA.[UP_Left])) AND ((tableA.UP_ Left) Is Not Null And (tableA.UP_Left)>0 And (tableA.UP_Left) In (SELECT MAX(tableA.[UP_Left]) FROM tableA GROUP BY tableA.[STREET],tableA.[LOW_Left])));


    TN

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    my guess woudkl be the joins are not correctly formed and /or you are excluding rows due to the combination of where statements
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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