Solved

Help with SELECT statement, need only one of each item, witch is representet in a table

Posted on 2011-03-10
6
339 Views
Last Modified: 2012-06-21
I need to list of countries, from where, a customer has active orders, but only need the value once.

The SQL select i use:
SELECT DISTINCT
SupplierCountry.cCode,
Orders.SupID,
OrdersProducts.tn
FROM (((OrdersProducts INNER JOIN Orders ON OrdersProducts.orderID = Orders.orderID)
INNER JOIN suppliers ON Orders.SupID = suppliers.SupID)
INNER JOIN  SupplierCountry  on suppliers.SupCountry =  SupplierCountry.SupCountry)  
WHERE transportNo <> '' AND Orders. Customer = ‘" & request.form("Customer ") & "' ORDER BY SupplierCountry.cCode ASC

The Tables:

Orders
Customer      orderID      SupID
Aa      1000      1
Bb      1001      1
Cc      1002      1
Dd      1003      2
Ee      1004      3

OrdersProducts
tn      ItemID      orderID
100      A      1000
100      B      1000
100      C      1000
101      D      1002
102      E      1003

Suppliers
SupID      SupCountry
1      5
2      2
3      3
4      2
5      5

SupplierCountry
cCode      SupCountry
BE      1
DE      2
FR      3
UK      4
US      5

Result:
US
US
US

I was hoping for:
US

Naan_PLS
0
Comment
Question by:NaanPLS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:NaanPLS
ID: 35094941
Ok, For some reason is the suplierID colum missing, but It could be any value.

NaanPLS.
0
 
LVL 7

Accepted Solution

by:
Ironhoofs earned 250 total points
ID: 35094945
Try to only select the country distinct:

SELECT DISTINCT
SupplierCountry.cCode,
FROM (((OrdersProducts INNER JOIN Orders ON OrdersProducts.orderID = Orders.orderID)
INNER JOIN suppliers ON Orders.SupID = suppliers.SupID)
INNER JOIN  SupplierCountry  on suppliers.SupCountry =  SupplierCountry.SupCountry)  
WHERE transportNo <> '' AND Orders. Customer = ‘" & request.form("Customer ") & "' ORDER BY SupplierCountry.cCode ASC

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35094947
SELECT DISTINCT SupplierCountry.cCodE
FROM Orders
INNER JOIN SupplierCountry  
ON oRDERS.SupID =  SupplierCountry.SupCountry
ORDER BY SupplierCountry.cCode ASC
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NaanPLS
ID: 35095139
I get Error 800a0cc1 with both.
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 250 total points
ID: 35095434
Mine wasn't accurate anyway. I skipped over a table. It should be like this:

SELECT DISTINCT s1.cCodE
FROM Orders o1
INNER JOIN Suppliers s2
ON o1.SupID=s2.SupID
INNER JOIN SupplierCountry s1
ON s2.SupCountry =  s1.SupCountry
ORDER BY SupplierCountry.cCode ASC

This works on Access and MS SQL
0
 

Author Comment

by:NaanPLS
ID: 35095599
Sorry, You are both right, and I'm a B..... I....
The 800a0cc1 Error wasn't related to the Select, but a recordset variable.

Thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question