Join Query Help

[newbieaccess203]

i have small simple table where some rows of column STR and NBR are exactly the same but have different MDR number.
As to make my point clear I have divided that in different color group. For e.g. If you notice blue color has STR and NBR duplicated in both row but MDR is different. Same with yellow and green group.

I am looking to create a command button(name it NEXT) in access forum/dashboard, which will be show me the result like blue color and than when I click that button(NEXT BUTTON), it should show me yellow records, when i click next it should show me green button.

mainly how should i write query to display that result ?



I tried this query:
SELECT *
FROM Table1 AS s1
INNER JOIN Table1 AS s2
ON
(s2.STR = s1.STR OR
s2.NBR = s1.NBR
)
WHERE
s1.[MDR] <> s2.[MDR];

But it does not give me the desired result I am looking for.

[JoeM]

I cannot download your image from my current location, and I cannot say that I totally understand your question, but I think I see one potential issue:

INNER JOIN Table1 AS s2 
ON 
(s2.STR = s1.STR OR
s2.NBR = s1.NBR
)

I have never seen OR used in a JOIN clause. I don't think that is is possible. You may need to move that down to your WHERE clause.

Note that while it is rare, it is valid to have a query between two tables with no JOIN statement. That would create a Cartesian Product between your two tables, but your WHERE clause should limit it to just the records that you want to return.

[newbieaccess203]

so again added tables in both pdf and zip format and under zip it is xls file so hopefully you should be able to open the table and see what I am talking about.

I want to build the query where it will display first display records in purple color and clicking next button on form should display yellow color records and than green color.

Please understand i colored the records to explain what type of output i am looking for the query.
The reason i color first two records purple because it has STR and NBR are same in both the rows and MDR number is different.

Same with other colored records.

HOPEFULLY I AM ABLE TO EXPLAIN CLEARLY WHAT TYPE OF OUTPUT I AM EXPECTING FROM QUERY.

I TRIED TO CREATE QUERY BY MYSELF:
SELECT T1.Region, T1.STR, T1.NBR, T1.DESCR, T1.MDR
FROM Table1 AS T1
INNER JOIN Table1 AS T2
ON T1.STR = T2.STR AND T1.NBR = T2.NBR
WHERE T1.MDR<>T2.MDR
ORDER BY T1.STR, T1.NBR;

BUT IT DISPLAY THE POP TEXT BOX FOR ASKING ME TO ENTER THE VALUE

I JUST WANT TO DISPLAY THE RECORDS

[JoeM]

I think it would be much more beneficial if you could post a sample database (not just screen prints of your desired output), so we can see what your data tables actually look like, why your query is behaving as it does.

[newbieaccess203]

accidentally i posted that with new thread "
need to display combo box selection match on the form instead separate query/datasheet view