DSum function

[maella]

Hi, for example I've got a table like this:

Id User Date Amount

1 Name1 # # 1000
2 Name2 # # 600
3 Name3 # # 300
4 Name1 # # 500

First I've got a query for choosing the date, all ok, and then I would like
another query based on the first one, to get the total amount of each user.
Like this

Id User TotalAmount

1 Name1 1500
2 Name2 600
3 Name3 500

Is this possible? I've used de function DSum on the secong query and always
get the following results:

Id User TotalAmount

1 Name1 2600
2 Name2 2600
3 Name3 2600
4 Name1 2600


Thanks

 

[Michel Walsh]

You have to use a criteria, the third argument of DSum, but it should be
easier, in this case, to simply use SUM. In SQL view:



SELECT user, SUM(amount)
FROM tableName
GROUP BY user


Vanderghast, Access MVP

 

[maella]

I preffer to use the criteria, the table and the queries I've explained are
quite more complicated. Which [Used] field do I have to use, the table one or
the first query? and how could I introduce it?

 

[Michel Walsh]

SELECT user, ..., DSum("Amount", "tableNameHere", "user=" & user )
....


that is assuming that user is a number. If it is a string, rather than a
number, the syntax is:


SELECT user, ..., DSum("Amount", "tableNameHere", "user=""" & user & """" )
....


and even that, assumes the name does not have " in it.




Vanderghast, Access MVP

I preffer to use the criteria, the table and the queries I've explained are
quite more complicated. Which [Used] field do I have to use, the table one
or
the first query? and how could I introduce it?

You have to use a criteria, the third argument of DSum, but it should be
easier, in this case, to simply use SUM. In SQL view:



SELECT user, SUM(amount)
FROM tableName
GROUP BY user


Vanderghast, Access MVP

 

[maella]

Ok, I´ve seen my problem. I´ve used you sentence SQL and in the Design view,
I´ve understood all.

Thanks