Unique Values query

[Support]

I have a database with 3 tables linked - Customers - Orderslines - Orders.
I want to run a query on those that bought from dept menswear/ accessories
and footwear so I have created the below query:

SELECT DISTINCT tblCustomers_2SV.UserID, tblCustomers_2SV.Salutation,
tblCustomers_2SV.[First Name], tblCustomers_2SV.[Last Name],
tblCustomers_2SV.[Billing Address Line 1], tblCustomers_2SV.[Billing Address
Line 2], tblCustomers_2SV.[Billing Address Line 3],
tblCustomers_2SV.[Billing Post Code], tblOrderlines_2.Dept,
tblCustomers_2SV.[Receive In-House Direct Marketing]
FROM (tblCustomers_2SV INNER JOIN tblOrders_2SV ON
tblCustomers_2SV.UserID=tblOrders_2SV.UserID) INNER JOIN tblOrderlines_2 ON
tblOrders_2SV.[Order Num]=tblOrderlines_2.[Order Num]
WHERE (((tblOrderlines_2.Dept)="footwear" Or
(tblOrderlines_2.Dept)="menswear" Or (tblOrderlines_2.Dept)="accessories")
AND ((tblCustomers_2SV.[Receive In-House Direct Marketing])="yes"));

This almost gives me the result I want however I am getting duplicate
entries for customers who bought items from more than one queried dept. How
would I rectfy this so I can get one result for the customer?
thanks

 

[Brian]

I have a database with 3 tables linked - Customers - Orderslines - Orders.
I want to run a query on those that bought from dept menswear/ accessories
and footwear so I have created the below query:

SELECT DISTINCT tblCustomers_2SV.UserID, tblCustomers_2SV.Salutation,
tblCustomers_2SV.[First Name], tblCustomers_2SV.[Last Name],
tblCustomers_2SV.[Billing Address Line 1], tblCustomers_2SV.[Billing Address
Line 2], tblCustomers_2SV.[Billing Address Line 3],
tblCustomers_2SV.[Billing Post Code], tblOrderlines_2.Dept,
tblCustomers_2SV.[Receive In-House Direct Marketing]
FROM (tblCustomers_2SV INNER JOIN tblOrders_2SV ON
tblCustomers_2SV.UserID=tblOrders_2SV.UserID) INNER JOIN tblOrderlines_2 ON
tblOrders_2SV.[Order Num]=tblOrderlines_2.[Order Num]
WHERE (((tblOrderlines_2.Dept)="footwear" Or
(tblOrderlines_2.Dept)="menswear" Or (tblOrderlines_2.Dept)="accessories")
AND ((tblCustomers_2SV.[Receive In-House Direct Marketing])="yes"));

This almost gives me the result I want however I am getting duplicate
entries for customers who bought items from more than one queried dept. How
would I rectfy this so I can get one result for the customer?
thanks

What you ask is logically impossible. The department is one of the fields
your query is outputting, so if there is more than one department for a
customer, how can the query do anything other than output more than one
result?

The DISTINCT keyword in a query eliminates duplicates where ALL of the
output fields are identical. In this case, although the customer fields may
be identical, the Dept field is not.

 

[Support]

Thanks Brian, I wasn't sure it was possible so I wanted to check.
I've got round it by exporting then importing to another table and running
an append query onto a copied structure only table with primary key set for
userID.
Seems to be ok.

I have a database with 3 tables linked - Customers - Orderslines - Orders.
I want to run a query on those that bought from dept menswear/ accessories
and footwear so I have created the below query:

SELECT DISTINCT tblCustomers_2SV.UserID, tblCustomers_2SV.Salutation,
tblCustomers_2SV.[First Name], tblCustomers_2SV.[Last Name],
tblCustomers_2SV.[Billing Address Line 1], tblCustomers_2SV.[Billing Address
Line 2], tblCustomers_2SV.[Billing Address Line 3],
tblCustomers_2SV.[Billing Post Code], tblOrderlines_2.Dept,
tblCustomers_2SV.[Receive In-House Direct Marketing]
FROM (tblCustomers_2SV INNER JOIN tblOrders_2SV ON
tblCustomers_2SV.UserID=tblOrders_2SV.UserID) INNER JOIN tblOrderlines_2 ON
tblOrders_2SV.[Order Num]=tblOrderlines_2.[Order Num]
WHERE (((tblOrderlines_2.Dept)="footwear" Or
(tblOrderlines_2.Dept)="menswear" Or (tblOrderlines_2.Dept)="accessories")
AND ((tblCustomers_2SV.[Receive In-House Direct Marketing])="yes"));

This almost gives me the result I want however I am getting duplicate
entries for customers who bought items from more than one queried dept. How
would I rectfy this so I can get one result for the customer?
thanks

What you ask is logically impossible. The department is one of the fields
your query is outputting, so if there is more than one department for a
customer, how can the query do anything other than output more than one
result?

The DISTINCT keyword in a query eliminates duplicates where ALL of the
output fields are identical. In this case, although the customer fields may
be identical, the Dept field is not.

 

[Brian]

Thanks Brian, I wasn't sure it was possible so I wanted to check.
I've got round it by exporting then importing to another table and running
an append query onto a copied structure only table with primary key set for
userID.
Seems to be ok.

I don't understand you, and I don't think you understand me. I didn't say
that what you wanted was technically impossible, I said it was LOGICALLY
impossible. If you have a query that returns the following rows:

Customer ID Customer Name Dept
========= =========== ====
1 Smith Apples
1 Smith Oranges

then how can you possibly get just one row for customer 1? Answer: you
can't, because of the different departments. However, if you removed the
Dept field from the query, and used the DISTINCT keyword, you would indeed
only get one row, as follows:

Customer ID Customer Name
========= ===========
1 Smith

I don't understand your description of what you've done, but if you've
finished up with only one row per customer, then somewhere along the line
you must have got rid of the department field. You could easily have done
this by simply removing it from your original query, thus:

SELECT DISTINCT tblCustomers_2SV.UserID, tblCustomers_2SV.Salutation,
tblCustomers_2SV.[First Name], tblCustomers_2SV.[Last Name],
tblCustomers_2SV.[Billing Address Line 1], tblCustomers_2SV.[Billing Address
Line 2], tblCustomers_2SV.[Billing Address Line 3],
tblCustomers_2SV.[Billing Post Code],
tblCustomers_2SV.[Receive In-House Direct Marketing]
FROM (tblCustomers_2SV INNER JOIN tblOrders_2SV ON
tblCustomers_2SV.UserID=tblOrders_2SV.UserID) INNER JOIN tblOrderlines_2 ON
tblOrders_2SV.[Order Num]=tblOrderlines_2.[Order Num]
WHERE (((tblOrderlines_2.Dept)="footwear" Or
(tblOrderlines_2.Dept)="menswear" Or (tblOrderlines_2.Dept)="accessories")
AND ((tblCustomers_2SV.[Receive In-House Direct Marketing])="yes"));

 

[Support]

I had been giving some data from a couple of different sources (our old
mailorder system and our new mailorder system).
I had been asked to find out which customers had ordered either from depts
footwear, menswear or accessories. Obviously a customer could order many
items from any of these depts which is why I got duplicates from this query.
It's highly likely that there could have been a much better query to run
however I'm still new to Access and SQL.
To get round this I followed:
http://office.microsoft.com/en-us/assistance/HA010345581033.aspx
final part near the end.
Anyway's done now.
Thanks

Thanks Brian, I wasn't sure it was possible so I wanted to check.
I've got round it by exporting then importing to another table and running
an append query onto a copied structure only table with primary key set for
userID.
Seems to be ok.

I don't understand you, and I don't think you understand me. I didn't say
that what you wanted was technically impossible, I said it was LOGICALLY
impossible. If you have a query that returns the following rows:

Customer ID Customer Name Dept
========= =========== ====
1 Smith Apples
1 Smith Oranges

then how can you possibly get just one row for customer 1? Answer: you
can't, because of the different departments. However, if you removed the
Dept field from the query, and used the DISTINCT keyword, you would indeed
only get one row, as follows:

Customer ID Customer Name
========= ===========
1 Smith

I don't understand your description of what you've done, but if you've
finished up with only one row per customer, then somewhere along the line
you must have got rid of the department field. You could easily have done
this by simply removing it from your original query, thus:

SELECT DISTINCT tblCustomers_2SV.UserID, tblCustomers_2SV.Salutation,
tblCustomers_2SV.[First Name], tblCustomers_2SV.[Last Name],
tblCustomers_2SV.[Billing Address Line 1], tblCustomers_2SV.[Billing Address
Line 2], tblCustomers_2SV.[Billing Address Line 3],
tblCustomers_2SV.[Billing Post Code],
tblCustomers_2SV.[Receive In-House Direct Marketing]
FROM (tblCustomers_2SV INNER JOIN tblOrders_2SV ON
tblCustomers_2SV.UserID=tblOrders_2SV.UserID) INNER JOIN tblOrderlines_2 ON
tblOrders_2SV.[Order Num]=tblOrderlines_2.[Order Num]
WHERE (((tblOrderlines_2.Dept)="footwear" Or
(tblOrderlines_2.Dept)="menswear" Or (tblOrderlines_2.Dept)="accessories")
AND ((tblCustomers_2SV.[Receive In-House Direct Marketing])="yes"));