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

[tnguyen315]

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

 

[LaurieHamlin]

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.

 

[tnguyen315]

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

 

[LaurieHamlin]

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.

 

[tnguyen315]

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

 

[tnguyen315]

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

 

[Triacona]

Select Distinct?

Thank you,

Kind regards

Triacona