You can use a subquery which counts the order lines where the item is in the
value list. If the count equals the number of items then the order contains
all three items, e.g. using the Northwind Order Details table as an example:
SELECT *
FROM [Order Details] AS OD1
WHERE
(SELECT COUNT(*)
FROM [Order Details] AS OD2
WHERE OD2.OrderID = OD1.OrderID
AND ProductID IN(1,2))=2;
which returns all orders containing products 1 and 2 (Chai and Chang),
whereas:
SELECT *
FROM [Order Details] AS OD1
WHERE
(SELECT COUNT(*)
FROM [Order Details] AS OD2
WHERE OD2.OrderID = OD1.OrderID
AND ProductID IN(1,2,3))=3;
returns no orders as none contain product 3 (Aniseed Syrup) in addition to 1
and 2.
By adding the following module, from the link I gave in my earlier reply, to
the database:
'''''''''Module Begins'''''''''''
Option Compare Database
Option Explicit
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function InParam(fld, param) As Boolean
Dim stToken As String
If IsNull(fld) Then fld = ""
Do While (Len(param) > 0)
stToken = GetToken(param, ",")
If stToken = LTrim$(RTrim$(fld)) Then
InParam = True
Exit Function
Else
InParam = False
End If
Loop
End Function
'''''''''Module Ends'''''''''''
You can include parameters in the query:
SELECT *
FROM [Order Details] AS OD1
WHERE
(SELECT COUNT(*)
FROM [Order Details] AS OD2
WHERE OD2.OrderID = OD1.OrderID
AND InParam(ProductID,[Enter product ID list:]))
=[Enter number of products:];
By entering 1,2 at the first prompt and 2 at the second the result would be
the same as the first query above. The InParam function is not the fastest
thing on two wheels but with the 2000+ rows in the Northwind table
performance is acceptable.
In a developed application you'd not use simple parameter prompts like this
of course, but probably a form with a multi-select list box in which the
products would be selected by name and whose AfterUpdate event procedure
would be used to fill (a) a hidden text box on the form with a
comma-separated value list of the corresponding OrderID values., and (b)
another hidden text box with the list box's ItemsSelected.Count value. The
two hidden text boxes would then be referenced as the parameters by the
query.
Ken Sheridan
Stafford, England
JJP said:
Karl, maybe you can help a little further. Your formula did work but I
probably stated my case poorly.
Considering the following database structure:
OrderNumber
Item
QTY
Price
There can e a variety of Items in the Item Field such as:
(WFM7120, WFM7120EYE,WFM7120AD, WFM7120DDE,WFM7120HD,WFM7120PHY, etc.)
Ideally I want to find all the orders that contain for Example WFM7120HD,
WFM7120AD and WFM7120EYE) The order should contain all three. Using the
formula (Like "*" & [qryall].[Item] & "*") along the lines you suggest I see
orders that have on either WFM7120HD or WFM7120AD or WFM7120EYE).
Once I filter on orders that have all three, I would like to see the ENTIRE
ORDER and quanties and prices. I can do that by creatring a second query
that relates the order numbers from the fist filtering query to the
underlying database.
Any suggestions would be appreciated.
Thanks,
KARL DEWEY said:
In design view add a calculated field --
Input_List: [Enter items separated by space]
In criteria row put --
Like "*" & [YourTable].[Product] & "*"
--
KARL DEWEY
Build a little - Test a little
JJP said:
I use the IN FUNCTION in my query to filter specific product configurations.
For example,
In(WFM7120,WFM7120HD,WFM7120PHY)
I would like to be able to change the products on the fly using something
like the [ENTER] function, but I cannot figure out how to do it. Can anyone
suggest a method for doing this?
Thanks,
