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.
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:
SELECT DISTINCT main.[Order Number]
Now, the problem is, because of the DISTINCT keyword in the statement, I can't add:
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.
WHERE main.[Deleted] = FALSE
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...