No duplicates please

[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.

 

[Jerry Whittle]

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 ;

 

[ericb]

Thank you

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

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.

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.

 

[Duane Hookom]

http://www.viescas.com/Info/books.htm#Database Design
--
Duane Hookom
Microsoft Access MVP

Thank you

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

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.

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.