No duplicates please

  • Thread starter Thread starter ericb
  • Start date Start date
E

ericb

I have a problem with a query.

I have 2 tables : Customer with a ID_customer field and a Product table with
ID_product, REF_customer, date_purchase fields.

I want a list of the customers that purchased in the last 30 days.

That I made and it works. My Problem is if the same costumer purchased more
than once. I don't want him 2 or 3 or 4 or X times in the list. I want him
once and only once.

How to I arrange my query

This is what I have :

SELECT [Client Extended].ID_client, [Client Extended].nom_client,
Produit.date_livraison
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((DateDiff("d",[date_livraison],Date())<=60));


Thank you for the help.
 
SELECT DISTINCT [Client Extended].ID_client,
[Client Extended].nom_client
FROM [Client Extended] RIGHT JOIN Produit
ON [Client Extended].ID_client = Produit.REF_client
WHERE ((DateDiff("d",[date_livraison],Date())<=60));

or

SELECT [Client Extended].ID_client,
[Client Extended].nom_client,
Max(Produit.date_livraison)
FROM [Client Extended] RIGHT JOIN Produit
ON [Client Extended].ID_client = Produit.REF_client
WHERE DateDiff("d",[date_livraison],Date())<=60
GROUP BY [Client Extended].ID_client,
[Client Extended].nom_client
ORDER BY 1,2 ;
 
Thank you

Would you have an SQL book to recommend ?
--
eric


Jerry Whittle said:
SELECT DISTINCT [Client Extended].ID_client,
[Client Extended].nom_client
FROM [Client Extended] RIGHT JOIN Produit
ON [Client Extended].ID_client = Produit.REF_client
WHERE ((DateDiff("d",[date_livraison],Date())<=60));

or

SELECT [Client Extended].ID_client,
[Client Extended].nom_client,
Max(Produit.date_livraison)
FROM [Client Extended] RIGHT JOIN Produit
ON [Client Extended].ID_client = Produit.REF_client
WHERE DateDiff("d",[date_livraison],Date())<=60
GROUP BY [Client Extended].ID_client,
[Client Extended].nom_client
ORDER BY 1,2 ;


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ericb said:
I have a problem with a query.

I have 2 tables : Customer with a ID_customer field and a Product table with
ID_product, REF_customer, date_purchase fields.

I want a list of the customers that purchased in the last 30 days.

That I made and it works. My Problem is if the same costumer purchased more
than once. I don't want him 2 or 3 or 4 or X times in the list. I want him
once and only once.

How to I arrange my query

This is what I have :

SELECT [Client Extended].ID_client, [Client Extended].nom_client,
Produit.date_livraison
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((DateDiff("d",[date_livraison],Date())<=60));


Thank you for the help.
Click to expand...
Click to expand...
 
http://www.viescas.com/Info/books.htm#Database Design
--
Duane Hookom
Microsoft Access MVP


ericb said:
Thank you

Would you have an SQL book to recommend ?
--
eric


Jerry Whittle said:
SELECT DISTINCT [Client Extended].ID_client,
[Client Extended].nom_client
FROM [Client Extended] RIGHT JOIN Produit
ON [Client Extended].ID_client = Produit.REF_client
WHERE ((DateDiff("d",[date_livraison],Date())<=60));

or

SELECT [Client Extended].ID_client,
[Client Extended].nom_client,
Max(Produit.date_livraison)
FROM [Client Extended] RIGHT JOIN Produit
ON [Client Extended].ID_client = Produit.REF_client
WHERE DateDiff("d",[date_livraison],Date())<=60
GROUP BY [Client Extended].ID_client,
[Client Extended].nom_client
ORDER BY 1,2 ;


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ericb said:
I have a problem with a query.

I have 2 tables : Customer with a ID_customer field and a Product table with
ID_product, REF_customer, date_purchase fields.

I want a list of the customers that purchased in the last 30 days.

That I made and it works. My Problem is if the same costumer purchased more
than once. I don't want him 2 or 3 or 4 or X times in the list. I want him
once and only once.

How to I arrange my query

This is what I have :

SELECT [Client Extended].ID_client, [Client Extended].nom_client,
Produit.date_livraison
FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client =
Produit.REF_client
WHERE ((DateDiff("d",[date_livraison],Date())<=60));


Thank you for the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top