First Instance 1

[MeanJoeGreen13]

I posted this in SQL forum in error ... should be in Access forums (apologies ... still new to this site).

I use the following code to pull a list of customers who have placed an order for a particular product between two dates. However, if the customer has ordered this item more than once in the date period multiple records appear for the customer. How can I change the SQL to just show me one record per customer?

The SQL so far is:

SELECT Customers.Title, Customers.[First Name], Customers.[Last Name], Customers.Address1, Customers.Address2, Customers.Town, Customers.County, Customers.Postcode, Customers.Country, Sales.[Sales Date], Sales.[Sales Date], SalesD.[Item Code]
FROM Customers INNER JOIN (Sales INNER JOIN SalesD ON Sales.SalesID = SalesD.SalesID) ON Customers.ID = Sales.CustomerID
WHERE (((Sales.[Sales Date])>=#10/1/2007# And (Sales.[Sales Date])<=#10/31/2007#) AND ((SalesD.[Item Code])="BA0002"));

 

[HarleyQuinn]

I'd take out one of the Sales.[Sales Date] that you return, then decide which of the multiple records you want to display. To cut a long story short, I think my answer would be the same as ca8msm's in thread183-1439664 (except I'd advise to take out one of the returned fields as per above).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MeanJoeGreen13]

Sorry my error. Needs only the one Sales Date column

 

[HarleyQuinn]

No problem, just use MIN() or MAX() function on your Sales.[Sales Date] and group by the rest of the fields, then you'll get the one row per customer you require.

Hope that helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MeanJoeGreen13]

Forgive me but i'm a beginner at this - could you please help with the syntax? I deeply appreciate all the help!

 

[HarleyQuinn]

Go on then, I'm assuming you want to return the earliest Sales Date

Something like:

SELECT Customers.Title, Customers.[First Name], Customers.[Last Name], Customers.Address1, Customers.Address2, Customers.Town, Customers.County, Customers.Postcode, Customers.Country, SalesD.[Item Code], MIN(Sales.[Sales Date]) as FirstSale
FROM Customers INNER JOIN (Sales INNER JOIN SalesD ON Sales.SalesID = SalesD.SalesID) ON Customers.ID = Sales.CustomerID
WHERE (((Sales.[Sales Date])>=#10/1/2007# And (Sales.[Sales Date])<=#10/31/2007#) AND ((SalesD.[Item Code])="BA0002"))
GROUP BY Customers.Title, Customers.[First Name], Customers.[Last Name], Customers.Address1, Customers.Address2, Customers.Town, Customers.County, Customers.Postcode, Customers.Country, SalesD.[Item Code];

While that should do what you want it'll be worth (by that I mean essential) reading up on GROUP BY and the Aggregate functions (MAX, MIN, SUM, COUNT etc.) as they are generally used alot in queries (plus it'll help you understand what your query is doing ).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.