Want to add special column to Query

[breukelen]

Good Morning,

I like to add an extra column in a query which is the
total cost by category of the last 12 months.My skills are
not sufficient to solve this problem.Advice is very much appreciated.My query is like this :

Category Year Month Cost Cost last 12 month by Category
-------- ---- ----- ---- ------------------------
Car 1999 1 500
Car 1999 2 450
Car ----- - ---
etc
Car 2002 1 365


Tel 1999 1 200
etc
Tel 2002 1 340

I like to have this extra colum "Cost last 12 month by Category".

Thanks in advance for tour help.

Gunter

 

[RichUK]

You need a second "Totals" query which has the first query as its record source, GroupBy Category,Year,Month, Sum Cost
HTH

 

[tlbroadbent]

Use a query like the following...

Select
Category,
[Year],
[Month],
[Cost],
(Select Sum(Cost) From MyTable
Where Category=t.Category
And [Date]>Dateadd("mm", -12, date())) As 12MonthCost

From MyTable As t
Where [Date]>Dateadd("mm", -12, date()))

If your columns are actually named "date", "month", and "year", I recommend changing them because these are reserved words in Access. That is the reason I included brackets in the example. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[breukelen]

Terry ,

Thanks for your help.It is a step in the right direction.
However I want the sum of the last 12 month cost calculated
from the date in the record.I changed date() to [date]
I did get after every category (of the same type) now the same amount,which is not correct.Any suggestion is very much
appreciated.(by the way I changed "mm" in "m&quot

Loved the opening ceremony yesterday and would like to be
in Salt Lake City now.Enjoy the Games !!
Thanks again,

Gunter

 

[tlbroadbent]

Select
Category,
[Year],
[Month],
[Cost],
(Select Sum(Cost) From MyTable
Where Category=t.Category
And [Date]>Dateadd("m", -12, t.[Date])) As 12MonthCost

From MyTable As t
Where <criteria>

You might want to add criteria such as selecting a date range.
Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[breukelen]

Terry ,

Thanks ,everything works !!

Gunter