Expression Building (1 Viewer)

luckydougy · Sep 26, 2012

I have a query that has names, payments, and dates. I need to know how to build an expression in expression builder in order to total the column of values if that value is associated with certain year, eg. 2010.

Example data
john, $30, 3/2/2010
fred, $25, 6/3/2011
sam, $20, 7/22/2010
kyle, $35, 1/5/2012

My answer needs to be $50(30+20) in the new query.

How would I do this?

pbaldy · Sep 26, 2012

Is this what you're after?

SELECT Year(DateField) As TheYear, Sum(AmountField) AS TotalAmount
FROM TableName
GROUP BY Year(DateField)

replacing the table and field names with yours.

luckydougy · Sep 26, 2012

This lets me input both the year and the value. I want to just input the year, and then it adds the values of rows that have that corresponding year in the date field.

pbaldy · Sep 26, 2012

So you'd add a criteria to restrict the year.

luckydougy · Sep 26, 2012

How would you restrict year? I thought that is what I am doing by inputing the year 2010.

There are rows that have dates in 1 column and $ values in another column.
I want to add all the $ values where the years of the dates are all 2010.

pbaldy · Sep 26, 2012

Does this work?

SELECT Year(DateField) As TheYear, Sum(AmountField) AS TotalAmount
FROM TableName
WHERE Year(DateField) = 2010
GROUP BY Year(DateField)

luckydougy · Sep 27, 2012

Why is it that when I run the query it asks for *Value*'s Paramater amount in a little text box? Sum(*Value*)

I want it to pull the values from the table and sum them all where year is 2010. Not for me input the value.

Thanks for all your help though. I am making some progress

pbaldy · Sep 27, 2012

It sound like the file name is spelled wrong. It shouldn't ask for anything.

luckydougy · Sep 27, 2012

Yes it was not the right column title. I figured it out though. This is what I wanted.

SELECT Sum(Value) AS TotalAmount
FROM Table1
WHERE Year(DATEOFSERVICE) = Int(YearInput);

Thanks Alot though!

Expression Building (1 Viewer)

luckydougy

Registered User.

pbaldy

Wino Moderator

luckydougy

Registered User.

pbaldy

Wino Moderator

luckydougy

Registered User.

pbaldy

Wino Moderator

luckydougy

Registered User.

pbaldy

Wino Moderator

luckydougy

Registered User.

Similar threads

Users who are viewing this thread