Dear all,
I'm trying to compose a query, but I don't know how to express the statement.
I have 4 tables: Customers, Orders, Order Details and Products.
Each customer has many orders and each order may include many products.
Let's say that querying Orders and Order Details, we have the following data:
CustomerID ProductID
1 1
1 2
1 3
2 1
2 5
3 1
3 2
4 2
I'd like to display ONLY the customers that ordered BOTH Product 1 AND Product 2!
I don't like to include customers that ordered only one of the 2 products!
The result I'd like to get is:
CustomerID
1
3
Please help to express this sql query...
Thanks in advance,
John 5 2386
John,
When you create a query, each column is evaluated as 'And' so with this in mind,
put CustomerID in the first column, and ProductID in column 2 AND column 3. Set
the criteria for column 2 as 1 and the criteria for column 3 as 2. Your query
now reads -- 'Return all customers who ordered ProductID 1 AND who ordered
ProductID 2.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com
"JohnA" <ja**********@yahoo.com> wrote in message
news:b4**************************@posting.google.c om... Dear all,
I'm trying to compose a query, but I don't know how to express the statement. I have 4 tables: Customers, Orders, Order Details and Products. Each customer has many orders and each order may include many products. Let's say that querying Orders and Order Details, we have the following data: CustomerID ProductID 1 1 1 2 1 3 2 1 2 5 3 1 3 2 4 2 I'd like to display ONLY the customers that ordered BOTH Product 1 AND Product
2! I don't like to include customers that ordered only one of the 2 products! The result I'd like to get is: CustomerID 1 3
Please help to express this sql query...
Thanks in advance, John
Thanks for your reply!
I've already tried your suggestion, but it doesn't work and I think
that's right.
As you sad, if I set 2 columns of a query as the same field and then set
criteria for both columns, that's evaluated as AND! So how is it
possible a product to be equal to 1 AND 2 simultaneously? It doesn't
work this way.
And I think there is no way to do this with a single SQL statement. I
maybe need to use something like INTERSECTION, but for the moment I'm
seeking information about it, because MS-Access doesn't support this
statement.
If you have a better suggestion, please please reply me!
Thanks for your time!
From: PC Datasheet
Date Posted: 4/19/2004 3:13:00 AM
John,
When you create a query, each column is evaluated as 'And' so with this
in mind,
put CustomerID in the first column, and ProductID in column 2 AND column
3. Set
the criteria for column 2 as 1 and the criteria for column 3 as 2. Your
query
now reads -- 'Return all customers who ordered ProductID 1 AND who
ordered
ProductID 2.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
JohnA wrote: Dear all,
I'm trying to compose a query, but I don't know how to express the statement. I have 4 tables: Customers, Orders, Order Details and Products. Each customer has many orders and each order may include many products. Let's say that querying Orders and Order Details, we have the following data: CustomerID ProductID 1 1 1 2 1 3 2 1 2 5 3 1 3 2 4 2 I'd like to display ONLY the customers that ordered BOTH Product 1 AND Product 2! I don't like to include customers that ordered only one of the 2 products! The result I'd like to get is: CustomerID 1 3
Please help to express this sql query...
Thanks in advance, John
Maybe a double pass query will work.
I'll assume orderdetails table has the order id. Create a Totals query
that groups on orderid. First colum orderid (groupBy). Second column
orderID (Count). Third column is ProductID. In criteria select only
those that match product1 or product2.
Now create a query that you can link the orders table to the customers
table to this query. Drag the count field from the query and enter in
the criteria row the number 2.
John Asproloupos <ja**********@yahoo.com> wrote in
news:40*********************@news.frii.net: Thanks for your reply!
I've already tried your suggestion, but it doesn't work and I think that's right.
As you sad, if I set 2 columns of a query as the same field and then set criteria for both columns, that's evaluated as AND! So how is it possible a product to be equal to 1 AND 2 simultaneously? It doesn't work this way. And I think there is no way to do this with a single SQL statement. I maybe need to use something like INTERSECTION, but for the moment I'm seeking information about it, because MS-Access doesn't support this statement. If you have a better suggestion, please please reply me!
Thanks for your time!
You and Datasheet are on the right track, but have a ways to go.
Open the query builder. Add the orders table TWICE. Yes, two copies
of the table. Now join the tables on customerid.Now put your
customerID and two copies of ProductID in the query grid, making
sure that ONE comes from EACH copy of the table. Add your criteria
and view the results..
Bob Quintal
From: PC Datasheet Date Posted: 4/19/2004 3:13:00 AM
John,
When you create a query, each column is evaluated as 'And' so with this in mind, put CustomerID in the first column, and ProductID in column 2 AND column 3. Set the criteria for column 2 as 1 and the criteria for column 3 as 2. Your query now reads -- 'Return all customers who ordered ProductID 1 AND who ordered ProductID 2.
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!
You can use the IN Key word with a subquery to do the job of
INTERSECT.
The way it would work would be like as follows:
SELECT CustID FROM tblOrderDetail
WHERE ProdID = 1 AND CustID IN
(SELECT CustID FROM tblOrderDetail WHERE ProdID = 2)
The subquery produces a list of customers who have ordered product 2
(1, 3, 4). The main query then matches items from that list to the
CustIDs who have ordered product 1 (1, 2, 3), to produce the result
(1, 3).
Hope that helps,
-Carlos
John Asproloupos <ja**********@yahoo.com> wrote in message news:<40*********************@news.frii.net>... Thanks for your reply!
I've already tried your suggestion, but it doesn't work and I think that's right.
As you sad, if I set 2 columns of a query as the same field and then set criteria for both columns, that's evaluated as AND! So how is it possible a product to be equal to 1 AND 2 simultaneously? It doesn't work this way. And I think there is no way to do this with a single SQL statement. I maybe need to use something like INTERSECTION, but for the moment I'm seeking information about it, because MS-Access doesn't support this statement. If you have a better suggestion, please please reply me!
Thanks for your time!
From: PC Datasheet Date Posted: 4/19/2004 3:13:00 AM
John,
When you create a query, each column is evaluated as 'And' so with this in mind, put CustomerID in the first column, and ProductID in column 2 AND column 3. Set the criteria for column 2 as 1 and the criteria for column 3 as 2. Your query now reads -- 'Return all customers who ordered ProductID 1 AND who ordered ProductID 2.
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andrea |
last post by:
Hi,
I'm trying to emulate part of our client-server application as a web
site so customers can use it, and I'm stuck when it comes to
re-ordering items in a list.
Basically we have a list of...
|
by: Mike Morton |
last post by:
Runnint Mysql 3.23.47
I have the following two tables:
backorder_notification
->product_code varchar
->email varchar
->date_added date
Products
->code varchar
->name varchar
|
by: Paulo Andre Ortega Ribeiro |
last post by:
I have a Microsoft SQL Server 7.0.
I wrote a sql command that creates a temporary table with a ORDER BY
clause.
When a execute a SELECT on this temporary table sometimes the result is
ok, but...
|
by: David |
last post by:
Hi,
I have part of my SQL statement in my asp page as follows:
WHERE ((pcbforecast.ShipETA < '31/12/2005') and
(products.BBProductName = .......
The problem I am having is that I want the...
|
by: Elroyskimms |
last post by:
Using SQL 2000...
tblCustomer:
CustomerID int
CompanyName varchar(20)
HasRetailStores bit
HasWholesaleStores bit
HasOtherStores bit
tblInvoiceMessages:
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: JohnA |
last post by:
Dear all,
I'm trying to compose a query, but I don't know how to express the statement.
I have 4 tables: Customers, Orders, Order Details and Products.
Each customer has many orders and each...
|
by: kansaskannan |
last post by:
I have an Access 2000 database which lists customers by name, and how
much (in dollars) they have purchased of various products. How do I
write a SQL statement to select customers who make up the...
|
by: tjc0ol |
last post by:
Hi guys,
I'm a newbie in php and I got error in my index.php which is:
1054 - Unknown column 'p.products_id' in 'on clause'
select p.products_image, pd.products_name, p.products_id,...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |