Top Date per Date in another table

[danneedham]

Hi All



This has really stumped me!

I have two tables, one a Sales Table which holds store number, sales date, sales amount. And also a status table which holds store no, status date, and status name.

I would like a query which gives a top status date per sales date.

I have tried the following query, but it just drops sales dates.

SELECT Sales.StoreID, Sales.Date, Max(Status.StatusDate) AS MaxOfStatusDate, Sum(Sales.Amount) AS SumOfAmount
FROM Sales, Status
GROUP BY Sales.StoreID, Sales.Date
HAVING (((Max(Status.StatusDate))<=[Sales].[Date]));

Can someone please help me???


Many Thanks


Dan

[narayanis]

the SQL looks correct; i have run a similar one on another database and it works as expected, e.g.
1, '2008-1-1', '2008-1-1 19:38.00.00'
4, '2008-1-1', '2008-1-1 13:07.00.00'

What do you mean by "drops sales dates"?