Re: Query condition

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Allen,
I've been using the sub query you suggested and it works well. However,
i've realised that it would be beneficial to show all of the order lines that
belong to the order. You mentioned in your last post that i should use a
grouping on the orderID? Can you explain this in a bit more detail?
Thanks
Robin

"Allen Browne" wrote:

The way the query is formatted, it will show only the 57 product unless you
adjust it further (e.g. grouping on the OrderID field, and using Where in
the Total row under the ProductID.)

John Spencer also posted a good reply. It's worth experimenting with his
suggestion too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robin" <Robin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4821454E-A79C-4C00-BD93-6D631127E50E@xxxxxxxxxxxxxxxx
Hi Allen,
I tried your suggestion and i've got a result but the query is only
showing
productID 57. Is this correct or should both productID's be listed?
Robin

"Allen Browne" wrote:

Because you need to query 2 different *rows* of data, you could use a
subquery to choose the other one.

This kind of thing:

SELECT OrderDetail.*
FROM OrderDetail
WHERE (OrderDetail.ProductID = 57)
AND EXISTS (SELECT OrderID
FROM OrderDetail AS Dupe
WHERE Dupe.OrderID = OrderDetail.OrderID
AND Dupe.ProductID = 58);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

"Robin" <Robin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50605C87-896C-45B3-977B-51E8EB340EF8@xxxxxxxxxxxxxxxx
Hi All,
I have two tables for managing our sales orders. One has the order
header
info such as customer name, account number, etc and the other stores
the
order line info such as productID, unit price, qnty, etc. The tables
have
a
one to many relationship and are joined using the OrderID.

I need to create a query to list all the sales orders that include both
productID 57 and 58 but i can't quite get my head round it! If i put
"57
or
58" in the criteria box it gives me all of the orders that include
either
57
or 58 whereas i need to know the orders that include both!

Thx in advance.
Rob


.

Relevant Pages

  • Re: No Cursor Solutions Please
    ... sorry about the typo, both of the order by clauses should read, ... order by orderid, productid ...
    (microsoft.public.sqlserver.programming)
  • Re: Query condition
    ... The way the query is formatted, it will show only the 57 product unless you adjust it further (e.g. grouping on the OrderID field, and using Where in the Total row under the ProductID.) ... AND EXISTS (SELECT OrderID ... > I have two tables for managing our sales orders. ...
    (microsoft.public.access.queries)
  • Re: Multiple Field Search Query
    ... Consider the Northwind with OrderID and ProductID. ... This query returns the one orderID 11077 which contained the two products. ... I have 15 fields where I enter drug results. ...
    (microsoft.public.access.queries)
  • Re: Changes to "get the latest data point" for SQL Server 2008?
    ... The standard trick to get around this restriction is to use a common table expression to get the values from the windowing function and then to use that cte. ... rankOVER(PARTITION BY productId ORDER BY pricedate Desc) As rankvalue ... Then you can query the view, for example, ... approach is that that inner query can't be improved by WHERE's applied ...
    (microsoft.public.sqlserver.server)
  • Re: Records will not append to another table due to Key violations
    ... Perhaps you have tried every suggestion, but there is no way for anybody ... Can you create a query using the ... can say that if you append one table to another you will be adding new ... the records to the other table due to validation rule violations or key ...
    (microsoft.public.access.tablesdbdesign)