Using a Multi-Select List box for AND Criteria (1 Viewer)

shadow9449

Registered User.
Local time
Today, 02:40
Joined
Mar 5, 2004
Messages
1,037
It's fairly common to run a report using a combination of criteria from a list box. The way this is commonly done is by having a hidden text box create a list of items selected and then the query for the report uses an IN statement for the criteria to effectively OR the different criteria.

What I want to do is run a report where selecting multiple selections of the list box ANDs the criteria.

To be clear:

If the user selects "Apples", "Oranges", "Apricots", instead of running a report showing (for example, all clients who purchased) apples, oranges OR apricots (using the IN ('14', '12', '16') where the numbers represent product codes).

What I want different from the previous case is that the query written only shows clients who purchased ALL items selected.

Any suggestions?

Thank you

SHADOW
 

Guus2005

AWF VIP
Local time
Today, 07:40
Joined
Jun 26, 2007
Messages
2,645
You already must have something like
Code:
    For Each varItem In Me.lst.ItemsSelected
        retval = retval & ", '" & varItem & "'"
    Next varItem
to create the IN clause.

You need to change the WHERE clause from

Code:
where X in ('1','2')

into

Code:
where X = '1' and X = '2'

I need to see your code if you can't figure this out.

HTH:D
 

shadow9449

Registered User.
Local time
Today, 02:40
Joined
Mar 5, 2004
Messages
1,037
You need to change the WHERE clause from

Code:
where X in ('1','2')

into

Code:
where X = '1' and X = '2'


Ahh...that's as far as I got. The problem here is that the data is normalized ;)

Look at the example I uploaded. The only client I want listed on my report is John Smith because he bought both an apple AND an orange. If I put a where condition that says "where x = 'apple' and x = 'orange'" then NO ONE will ever get returned because Mr. Smith's apple and orange exist in separate records.

See the dilemma?

SHADOW
 

Attachments

  • Data.PNG
    Data.PNG
    3 KB · Views: 90

Guus2005

AWF VIP
Local time
Today, 07:40
Joined
Jun 26, 2007
Messages
2,645
Normalisation is never a problem. It *should* be normalized. I made a mistake. A value can't be both '1' AND '2'. At least not in this universe.

Your initial query was correct however not complete.

When you want to know who got an apple and an orange you have to execute the following query.
Code:
SELECT Table1.Name, Count(Table1.Name) AS Num
FROM Table1
WHERE (((Table1.[Fruit]) In ('Apple','Orange')))
GROUP BY Table1.Name
HAVING (((Count(Table1.Name))=2));
Enjoy!
 

shadow9449

Registered User.
Local time
Today, 02:40
Joined
Mar 5, 2004
Messages
1,037
Normalisation is never a problem. It *should* be normalized.

I know...I was joking...that's why the ;)

I made a mistake. A value can't be both '1' AND '2'. At least not in this universe.

Yes, that's why I had my problem :)

Your initial query was correct however not complete.

When you want to know who got an apple and an orange you have to execute the following query.
Code:
SELECT Table1.Name, Count(Table1.Name) AS Num
FROM Table1
WHERE (((Table1.[Fruit]) In ('Apple','Orange')))
GROUP BY Table1.Name
HAVING (((Count(Table1.Name))=2));
Enjoy!

Hmm...ok, that does get a bit more complicated. I'll have to have it dynamically write the code (remember, this is coming from a listbox) use the Count as the number of selected items but that DOES make sense!

Bedankt voor de hulp

SHADOW
 

shadow9449

Registered User.
Local time
Today, 02:40
Joined
Mar 5, 2004
Messages
1,037
Code:
SELECT Table1.Name, Count(Table1.Name) AS Num
FROM Table1
WHERE (((Table1.[Fruit]) In ('Apple','Orange')))
GROUP BY Table1.Name
HAVING (((Count(Table1.Name))=2));
Enjoy!

Works perfectly!

Bedankt!

SHADOW
 

Guus2005

AWF VIP
Local time
Today, 07:40
Joined
Jun 26, 2007
Messages
2,645
Glad you got that sorted.

You can use this query as a base to filter out everyone who has selected an Apple and an Orange.
If you store the resulting query in qrySelectedPeople.
Running the following query will get you the resulting information on each person individually:
Code:
select * from BigTable inner join qrySelectedPeople on BigTable.Name = qrySelectedPeople.Name

Graag gedaan, your welcome.

Share & Enjoy!
 

shadow9449

Registered User.
Local time
Today, 02:40
Joined
Mar 5, 2004
Messages
1,037
Your way is probably a bit simpler but I included the client names in the join of the first query, so I only have one query.

Groetjes

SHADOW
 

Users who are viewing this thread

Top Bottom