Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2014
    Posts
    7

    Unanswered: Eliminated duplicated records in access query

    Hello team,
    What is the best way to remove these duplicated records in my query?
    I have a Query named: My_Track with 3 column: SO_NUMBER, TRACKING_NUMBER and VOID
    I'm trying to eliminated all duplicated records from my result, please see attached image showing duplicated records in the last two rows
    SO_NUMBER: 0469547 have the same TRACKING_NUMBER and a flag VOID "Y" and "N"
    Thanks,
    Nick
    Attached Thumbnails Attached Thumbnails duplicate.jpg  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are no duplicated rows in the sample data set you provided. Three rows have the same value in the SO_NUMBER column but different values in TRACKING_NUMBER and VOID. Which row should be kept?

    Please post the SQL of the query. and a sample of data. It's impossible to give a sensible answer without knowing what we're dealing with.
    Have a nice day!

  3. #3
    Join Date
    Feb 2014
    Posts
    7
    This is my current SQL for the query
    Code:
    SELECT BlOrderStatus.SO_NUMBER, BlOrderStatus.TRACKING_NUMBER, BlOrderStatus.VOID
    FROM BlOrderStatus
    WHERE (((BlOrderStatus.SO_NUMBER) Between [Enter Start SO] And [Enter End SO]));
    basically it'll pull a range of SO_NUMBER from BlOrderStatus table.

    BlOrderStatus having below data structure as showing below

    SO_NUMBER | TRACKING_NUMBER | VOID
    0469545 1111111111111 N
    0469538 2222222222222 N
    0469540 3333333333333 N
    0469547 4444444444444 N
    0469549 5555555555555 N
    0469547 AAAAAAAAAA N
    0469547 AAAAAAAAAA Y

    Looking at SO_NUMBER: 0469547
    I would like to keep the record with one unique Tracking_NUMBER, example the 4444444444444
    and not showing the last two records for the same SO_NUMBER but tracking identical to each other and has been void ( VOID value with a "Y")

    Basically every time I have a Void tracking, the SO_NUMBER will have 3 records, one good one I would like to keep, the other two have identical Tracking_Number and one of them have a VOID with value "Y" that should not show up on my report since it's been void.

    Thanks,

  4. #4
    Join Date
    Feb 2014
    Posts
    7
    This is my current SQL for the query
    Code:
    SELECT BlOrderStatus.SO_NUMBER, BlOrderStatus.TRACKING_NUMBER, BlOrderStatus.VOID
    FROM BlOrderStatus
    WHERE (((BlOrderStatus.SO_NUMBER) Between [Enter Start SO] And [Enter End SO]));
    basically it'll pull a range of SO_NUMBER from BlOrderStatus table.

    BlOrderStatus having below data structure as showing below

    SO_NUMBER | TRACKING_NUMBER | VOID
    0469545 1111111111111 N
    0469538 2222222222222 N
    0469540 3333333333333 N
    0469547 4444444444444 N
    0469549 5555555555555 N
    0469547 AAAAAAAAAA N
    0469547 AAAAAAAAAA Y

    Looking at SO_NUMBER: 0469547
    I would like to keep the record with one unique Tracking_NUMBER, example the 4444444444444
    and not showing the last two records for the same SO_NUMBER but tracking identical to each other and has been void ( VOID value with a "Y")

    Basically every time I have a Void tracking, the SO_NUMBER will have 3 records, one good one I would like to keep, the other two have identical Tracking_Number and one of them have a VOID with value "Y" that should not show up on my report since it's been void.

    Thanks,

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Eliminating the rows where Void = 'Y' is easy:
    Code:
    SELECT TRACKING_NUMBER.SO_NUMBER, TRACKING_NUMBER.TRACKING_NUMBER, TRACKING_NUMBER.VOID
    FROM TRACKING_NUMBER 
    WHERE TRACKING_NUMBER.VOID = 'N'
    But in your data sample this left us with 2 rows with the same SO_NUMBER:
    Code:
    SO_NUMBER TRACKING_NUMBER	VOID
    469547	  4444444444444  	N
    469547	  AAAAAAAAAA    	N
    How do you decide which one must be kept and which one must be rejected?
    Have a nice day!

  6. #6
    Join Date
    Feb 2014
    Posts
    7
    I would like to remove the one have the tracking matched up with VOID have the value "Y"
    Code:
    469547	  AAAAAAAAAA    	N
    and keep one unique and have no matching tracking.
    Code:
    469547	  4444444444444  	N
    Thanks,

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    See my former post: there are two (2) rows having the value 'Y' in the [VOID] column for a [SO_NUMBER] of 469547.
    Have a nice day!

  8. #8
    Join Date
    Feb 2014
    Posts
    7
    I'm not sure I understand what need to be done here!

    We can disregard the void column to simplify the issue,

    How can we have a query that will not show the duplicated?

    SO_NUMBER | TRACKING_NUMBER | VOID
    0469545 1111111111111 N
    0469538 2222222222222 N
    0469540 3333333333333 N
    0469547 4444444444444 N
    0469549 5555555555555 N
    0469547 AAAAAAAAAA N << these two are duplicated when no VOID column
    0469547 AAAAAAAAAA Y << these two are duplicated when no VOID column

    Thanks,

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select distinct?
    or select distinctrow on say a query which just has the SO_Number and tracking number

    ALL, DISTINCT, DISTINCTROW, TOP Predicates - Access - Office.com
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bluestorm909 View Post
    I'm not sure I understand what need to be done here!

    SO_NUMBER | TRACKING_NUMBER | VOID
    0469545 1111111111111 N
    0469538 2222222222222 N
    0469540 3333333333333 N
    0469547 4444444444444 N
    0469549 5555555555555 N
    0469547 AAAAAAAAAA N << these two are duplicated when no VOID column
    0469547 AAAAAAAAAA Y << these two are duplicated when no VOID column
    1. You cannot say that some rows have no VOID columns. In a data set all rows have the same number of columns, although some of them may be Null (if Nulls are allowed).

    2. In your post of yesterday (two before your last one, which begins with "This is my current SQL for the query"), you sent a data sample where all columns have non-null values. Here's that sample properly formatted and with a row number added (Tab being used as separator in your original message):
    Code:
    # |	SO_NUMBER | TRACKING_NUMBER | VOID
    1	0469545	    1111111111111     N
    2	0469538	    2222222222222     N
    3	0469540	    3333333333333     N
    4	0469547	    4444444444444     N
    5	0469549	    5555555555555     N
    6	0469547	    AAAAAAAAAA	      N
    7	0469547	    AAAAAAAAAA	      Y
    If we eliminate row #7, which was handled previously using a WHERE clause, you can see that we are left with 2 rows (#4 and #6) that both have the same SO_NUMBER (0469547) but different TRACKING_NUMBER values (#4 = 4444444444444 and #6 = AAAAAAAAAA).

    What rule must be used to determine which of the 2 rows must be kept in the final data set and which must be discarded? That was the meaning of my last post.
    Have a nice day!

  11. #11
    Join Date
    Feb 2014
    Posts
    2

    Smile Hey bluestorm try this query buddy.

    Hi bluestorm,
    If you want to remove duplicate of so_number and tracking number please try my below query.
    Query:
    select * from table1 a where a.Sonumber||a.tracking_number in
    (select b.Sonumber||b.tracking_number from table1 b group by b.Sonumber||b.tracking_number
    having count(1)=1)

    After using this query it return
    Result:

    0469545 1111111111111 N
    0469538 2222222222222 N
    0469540 3333333333333 N
    0469547 4444444444444 N
    0469549 5555555555555 N

    I hope this is the result you were expecting.
    Let me know if you want something different.

  12. #12
    Join Date
    Feb 2014
    Posts
    7
    Code:
    # |	SO_NUMBER | TRACKING_NUMBER | VOID
    1	0469545	    1111111111111     N
    2	0469538	    2222222222222     N
    3	0469540	    3333333333333     N
    4	0469547	    4444444444444     N
    5	0469549	    5555555555555     N
    6	0469547	    AAAAAAAAAA	      N
    7	0469547	    AAAAAAAAAA	      Y
    Let me give you a brief background of how the data constructed:

    Basically we start out with a SO NUMBER , with a Unique Tracking Number, and a default value "N" for VOID

    Until we got to SO_NUMBER: 0469547 with value: "AAAAAAAAAA" , and default value for VOID is "N"

    Next we found something wrong with this Tracking and we would like to void this TRACKING NUMBER for this SO_NUMBER. so we VOID this
    ->the column 7 got added to the database with same SO_NUMBER and the TRACKING we want to void "AAAAAAAAAA" and a value "Y" in the VOID column.

    Let imagine we end up with a lot of data. and every time we void a Tracking of an order, the SO_NUMBER will have 3 records (similar to SO_NUMBER: 0469547 above):
    1 unique Tracking that we would like to keep (row #4)
    2 records we need to hide from query (row #6 and #7)

    So I'm not sure if this do able or not?
    Thanks,

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I don't understand why row #6 does not end up with the value 'Y' in the column [Void], it does not seem to be logical but whatever, it's your application.

    2. Can we assert that the rows to be eliminated will all end up with a value of 'AAAAAAAAAA' (or at least a non numeric value) in the [TRACKING_NUMBER] column? In this case, what's the use for the column [VOID]? If not the question remains open: In case of duplicates in the column [SO_NUMBER], what determines the row to be kept and the row(s) to be discarded?

    You won't be able to solve the problem until you can provide an aswer to that question. It's not a question of how to write the query, it's a question of logic.
    Have a nice day!

  14. #14
    Join Date
    Feb 2014
    Posts
    7
    Quote Originally Posted by Sinndho View Post
    1. I don't understand why row #6 does not end up with the value 'Y' in the column [Void], it does not seem to be logical but whatever, it's your application.

    2. Can we assert that the rows to be eliminated will all end up with a value of 'AAAAAAAAAA' (or at least a non numeric value) in the [TRACKING_NUMBER] column? In this case, what's the use for the column [VOID]? If not the question remains open: In case of duplicates in the column [SO_NUMBER], what determines the row to be kept and the row(s) to be discarded?

    You won't be able to solve the problem until you can provide an aswer to that question. It's not a question of how to write the query, it's a question of logic.
    1. Column #6 don't have "Y" because it's always start with "N" as default on all SO NUMBER and Tracking, until we VOID the tracking and then we have #7, with duplicated of #6 but with VOID = "Y"

    2. We can eliminated the column VOID if it'll solve the problem. original it's use for flagging purposes, to know which Tracking has been voided.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Up to now, I asked at least three times the same question, which is fundamental, without receiving any usable answer. I'll repeat that question a fourth (and last) time here:

    Knowing that, after having eliminated the row(s) with a 'Y' value in the [VOID] column (that was handled quite a long time ago), we are left with more than one row having the same value in the [SO_NUMBER] column,what determines the row to be kept and the row(s) to be discarded?
    Have a nice day!

Posting Permissions

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