Sum of Year To Dates

[kbarbuto]

I am using a form to get a history of suppliers that our company is using and we wanted to know how much money we have spent on them during the year 2001. I have calculated a field called ExtendedPrice which took the unit price * quantity now I want to add up the extended price for the year 2001 and be able to do that for each year. So I guess it is a sort of Year to Date addition. I know there has to be an easy way to do it. I am not sure how to put it in my query or use it as a control source.

 

[K9Boxer]

I would create a query (Q_SumExtendedPrice)
that resulted in :
Supplier_ID, Sum(ExtendedPrice)
Where Date => '01/01/2001'

Base your form on Q_SumExtendedPrice

If you need additional Supplier Information, Create another query (Q_SumExtendedPriceWithSupplierInfo) by joining your master Supplier Table to Q_SumExtendedPrice.
Your form can then be based on Q_SumExtendedPriceWithSupplierInfo

I hope I understood your delemia correctly, and that this is what you are looking for.

 

[braindead2]

I think only half the problem is solved above.
kbarbuto said "and be able to do that for each year".

create a query like below to display the cost each year

Select year([yourdatefield])as CstYearly, supplierid, Sum(unit price * quantity )as ExtendedPrice from yourtable
group by Year([yourdatefield]),supplierid

then you can limit it to just one year buy saying
where year([yourdatefield])= 2001

 

[braindead2]

I think only half the problem is solved above.
kbarbuto said "and be able to do that for each year".

create a query like below to display the cost each year

Select year([yourdatefield])as CstYearly, supplierid, Sum(unit price * quantity )as ExtendedPrice from yourtable
group by Year([yourdatefield]),supplierid

then you can limit it to just one year by saying
where year([yourdatefield])= 2001