Results 1 to 3 of 3

Thread: Query - Filter out multiple bar codes to show only 1 bar code per product.

  1. #1
    Board Regular
    Join Date
    Nov 2007
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Query - Filter out multiple bar codes to show only 1 bar code per product.

    Hi!

    My query is this -
    I have 3 columns.

    Barcode | Product Type | Product Name | Colour


    1234567890123 | AAA | Example1 | White
    1234567890124 | AAA | Example1 | Black
    1234567890125 | AAA | Example1 | Red
    1234567890126 | AAA | Example2 | Purple
    1234567890127 | AAA | Example2 | Orange
    1234567890128 | AAA | Example2 | Blue
    1234567890129 | BBB | Example3 | Green
    1234567890130 | BBB | Example3 | Yellow
    1234567890131 | BBB | Example3 | Pink
    1234567890132 | BBB | Example4 | Grey
    1234567890133 | BBB | Example4 | Bright Green
    1234567890134 | BBB | Example4 | Navy Blue

    I wish to group each product by Product Type and Product Name but I only want 1 bar code showing, doesn't matter which one, and I don't care about the colour.

    How can I have a query just select 1 barcode so the data would look like the following?

    Barcode | Product Type | Product Name

    1234567890123 | AAA | Example1
    1234567890126 | AAA | Example2
    1234567890129 | BBB | Example3
    1234567890132 | BBB | Example4

  2. #2
    Board Regular
    Join Date
    Nov 2007
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query - Filter out multiple bar codes to show only 1 bar code per product.

    Nvm i figured it out - put 'First' or 'Last' into the grouping field (Total) and it brings only 1 record.

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,653
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query - Filter out multiple bar codes to show only 1 bar code per product.

    Be very careful with Last or First - it seldom works the way many would expect. Do not use it on an un-ordered set of records or you run the risk of getting what you think is Last sometimes, and sometimes not. Oft said that table records are like a bucket of marbles in that they have no guarantee of an intrinsic order. Sticking with the marbles, think of it this way:
    I dumped the marbles in a chute where they could all line up in single file in a trough and the green cat's eye marble was first in line. What guarantee do I have that the same marble will be first next time I do this? Obviously none. This is how Last (or First) can function unless you impose an order on the recordset. I see no definitive evidence of an order in your data example, hence the warning. If you're thinking bar code, and that is a text field, then no.

    The sad thing about all this is that it can creep in unnoticed for a long time, thus corrupt data. I have a job to do for someone else to fix this very problem.
    Last edited by Micron; Jun 21st, 2019 at 02:24 PM. Reason: added comment
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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