Query problem

[silverxx12]

Hi

I have three criteria in my table

name
shipdate
qty (£ amount)

using the following code i am able to filter by period eg jan - dec to see in that period the name and dates, quantites bought.

SELECT [Table].Name, [Table].nShipDate, Sum([Table].Qty) AS SumOfQty
FROM [Table]
GROUP BY [Table].Name, [Table].nShipDate


HAVING ((([Table].nShipDate) Between #1/1/2012# And #12/31/2012#));


However i am faced with the following issues:

I need firstly to sum all the qty's per name - ie i dont want to see two entries on different dates for the same name with two amounts.

And secondly I only want to see the last date in the sequence of the nshipdate relating to each name for eg: say Jim has the following data

Name nshipdate qty
Jim 12/1/12 $200
Jim 18/1/12 $800

I would like to see the following

Jim 18/1/12 $1000

[pbaldy]

Try

SELECT [Table].Name, Max([Table].nShipDate) As MaxDate, Sum([Table].Qty) AS SumOfQty
FROM [Table]
WHERE [Table].nShipDate Between #1/1/2012# And #12/31/2012#
GROUP BY [Table].Name

A WHERE clause is more appropriate than a HAVING clause in this situation (Access often guesses wrong).

[ssanfu]

Try

SELECT [Table].Name, Max([Table].nShipDate) As MaxDate, Sum([Table].Qty) AS SumOfQty
FROM [Table]
WHERE [Table].nShipDate Between #1/1/2012# And #12/31/2012#
GROUP BY [Table].Name

A WHERE clause is more appropriate than a HAVING clause in this situation (Access often guesses wrong).

Also, note that "Name" is a reserved word in Access - it is a property of many objects - and shouldn't be used for object names. Plus, it is not very descriptive ... "Name" of what?? Your dog, the city,...

You could try putting brackets around it :

SELECT [Table].[Name], Max([Table].nShipDate) As MaxDate,

[silverxx12]

GENIUS!!!!!!!

this was driving me mad for a while - ty so much

i orginally placed the date criteria under the sort by max and this was messing me up then went back to basics

really appreciate it :-)

[pbaldy]

Happy to help, and welcome to the site by the way!