Queries sum -up question

[dutchguy]

Hello all,

I'm very new to Acces so please stay with me even if this question is very dumb.


i have a datebase with items "let say bottle of water"
on different date i record the amount used on that date.
(some times several times 1 day )

I made a Querie that shows :

Date (>=[BeginDate] and <=[EndDate])

Items (i used in properties Unique values : Yes)

Total usage ( sum [amount])

Now my problem is that the querie still shows several records over 1 item
and it doesn't sum these records.

example what my querie shows :

Invisible
Date - item - amount
1-1-12 - water23 - 2
2-1-12 - water23 - 1


It should show

item - amount
Water23 - 3


Anybody knows how to slove this ?

Thanks

Peter

 

[recyan]

Just check out if below gives some guidelines :

SELECT 
	myTable.TheItem, 
	Sum(myTable.TheAmount) AS SumOfTheAmount
FROM 
	myTable
WHERE 
	(
		(
			(myTable.TheDate)>=[BeginDate] 
			And 
			(myTable.TheDate)<=[EndDate]
		)
	)
GROUP BY 
	myTable.TheItem;

Thanks

 

[dutchguy]

Hi Recyan,

Sorry i'm a newbie,
so i not quite understand where to put in your code.

But i played with the Where function

just like you mentioned and it seems working.

i did the following :

instead of group by >=[beginDate] and <=[EndDate]
i used Where >=[beginDate] and <=[EndDate]

So many thanks for you help !!

 

[recyan]

Glad you got it working.
Sorry, I gave you the SQL version of the query. Did not realise you were working in Design Mode.
If you go to your query in sql mode, you should be able to see the underlying sql query code.

Thanks