Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Question Unanswered: SQL DISTINCT Listbox Flagging

    Title doesn't really give much away, but wasn't sure what to write.

    I can think of ways around the problem, but was wondering if anyone knew of any super code to save me some work.

    The Setup:
    I have a table called 'main', which has various fields. Two of them being 'Order Number' and 'Deleted'. Order number being the records order number (duh), and deleted being a flag for a recycle bin style form I have.

    I also have a form which allows a user to search for records that match the requirements specified in some list boxes. For example, there is a listbox for 'Order Number', which lists all the known order numbers in the main table.

    This list is populated through a simple SQL statement:
    Code:
    SELECT DISTINCT main.[Order Number]
    FROM main
    The Issue:
    Now, the problem is, because of the DISTINCT keyword in the statement, I can't add:
    Code:
    WHERE main.[Deleted] = FALSE
    To the statement, as some records may have deleted set to true, and others may be false, but all of them have the same Order Number.

    The Question:
    Sooo... Is there a way to implement this without too much trouble? I basically need a statement that says: 'So long as at least one record, for any given Order Number, has Deleted set to FALSE, then display that Order Number in the listbox. Otherwise, do not display that Order Number'


    Any ideas? I'm fine with VBA, just not too hot on SQL.
    Looking for the perfect beer...

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Typo, bummer
    Looking for the perfect beer...

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    If order number is the primary key that would not allow duplicates, then why would you need to use Distinct?
    John M Reynolds

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Moreover, I don't see any reason why you could not add a WHERE clause because you use a DISTINCT predicate, this would be true with a GROUP BY clause but not here.
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Order number isn't the primary key. ID is. Order number has duplicates because there could be hundreds of orders going out together, with the same order number (obviously), all with different products in them.

    Got it working shortly after, was using [Deleted] instead of [Delete]... bit of a drag, but hey ho.
    Looking for the perfect beer...

Posting Permissions

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