INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Please help with Access query with groupby on Union All query.

Please help with Access query with groupby on Union All query.

(OP)
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

RE: Please help with Access query with groupby on Union All query.

T,
If you can't group your union query, create a final query that brings in the data from the union query and group in the final query.
I hope that helps.

RE: Please help with Access query with groupby on Union All query.

(OP)

Thanks LaurieHamlin for your time.

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

RE: Please help with Access query with groupby on Union All query.

You have to group on all the fields, not just GROUP BY tableA.LOW_Left, tableA.STREET . You may need to create more queries to do it.

RE: Please help with Access query with groupby on Union All query.

(OP)
Thanks again LaurieHamlin.
I tried to grouped by all fields but didn't work.
I'm trying to see if i can use UNION query as a sub query but couldn't find a solution yet.
TN

RE: Please help with Access query with groupby on Union All query.

(OP)
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

RE: Please help with Access query with groupby on Union All query.

Select Distinct?

Thank you,

Kind regards

Triacona

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close