9 Replies - 909 Views - Last Post: 11 January 2016 - 03:06 PM

#1 dbacode  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-January 16

simple Query to build help

Posted 07 January 2016 - 01:33 PM

see the attachment, there is table

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];
:code:

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

This post has been edited by modi123_1: 07 January 2016 - 01:47 PM


Is This A Good Question/Topic? 0
  • +

Replies To: simple Query to build help

#2 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: simple Query to build help

Posted 07 January 2016 - 01:47 PM

Your image is not attached.
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13553
  • View blog
  • Posts: 54,090
  • Joined: 12-June 08

Re: simple Query to build help

Posted 07 January 2016 - 01:49 PM

Aaah.. Ahh.. Ahh-say.. Like a second vacuum cleaner you don't don't have attachments!

Posted Image
Was This Post Helpful? 0
  • +
  • -

#4 dbacode  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-January 16

Re: simple Query to build help

Posted 08 January 2016 - 10:45 AM

here is the attachment

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#5 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2342
  • View blog
  • Posts: 9,393
  • Joined: 03-December 12

Re: simple Query to build help

Posted 08 January 2016 - 02:48 PM

I see no unique identifier first of all. How do you know which is the proper MDR value?

So much of that info is duplicated, I have no idea what data is should be holding.
SELECT
  region,
  str,
  nbr,
  desc,
  mdr
FROM
  table1 t1
UNION
  SELECT
  region,
  str,
  nbr,
  desc,
  mdr
FROM
  table1 t2
WHERE t1.mdr <> t2.mdr

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: simple Query to build help

Posted 08 January 2016 - 02:56 PM

Note that DESC should not be used as a field-name as it is a SQL keyword. (It is possible to use it if you always delimit it with square brackets, but it is preferable to change the name.)
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: simple Query to build help

Posted 08 January 2016 - 04:06 PM

Description is also a reserved word in Access (I'm surprised DESCENDING isn't, even though it isn't an Access keyword).
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: simple Query to build help

Posted 08 January 2016 - 04:30 PM

The correction to the original code is to change OR to AND, it requires matching both fields, STR and NBR:

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;

Was This Post Helpful? 0
  • +
  • -

#9 dbacode  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 07-January 16

Re: simple Query to build help

Posted 11 January 2016 - 12:20 PM

Thanks for the query and I design almost same query. My problem is when I run the query it pop up text box and asking me to enter value.

I do not want popup box. I just want to display results
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: simple Query to build help

Posted 11 January 2016 - 03:06 PM

If a parameter box pops up then you have misspelt the name of a field or table.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1