Howto contruct this kind of query?

[Guest]

Okay. This query, I have at lot of problems constructing.

I have a table A, containing records each with the fields ID, Amount, Type
and Date.
There is to values of Type: 1 and 2. For each month I want to make two sums
- one for each type, so that I'm able to show how much is spend on Type 1 and
Type 2 each month.

How do I do that?

 

[Lynn Trapp]

SELECT Type, Sum(Amount)
From YourTable
Where Date >= #1/1/2005# AND Date < #2/1/2005#
Group By Type;

Suggestion: Change the name of your Date field to something other than
"Date." Date is a reserved word and can cause you some problems in queries
like this.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Sorry... I think I kinda missed a point. I want to group the data by months,
so that I have N (the number of months) buckets with two sums (one for each
Type) in each bucket.

Can I do that?

Concerning the number of months N, I might later want to group by months per
year, so that I can gather statistics on data from more than a year.

/Chau

PS: I know that my question have some problems concerning reserved words -
it is just a problem description.

 

[Guest]

Sorry... I think I kinda missed a point. I want to group the data by months,
so that I have N (the number of months) buckets with two sums (one for each
Type) in each bucket.

Can I do that?

Concerning the number of months N, I might later want to group by months per
year, so that I can gather statistics on data from more than a year.

/Chau

PS: I know that my question have some problems concerning reserved words -
it is just a problem description.

 

[Guest]

Sorry... I think I kinda missed a point. I want to group the data by months,
so that I have N (the number of months) buckets with two sums (one for each
Type) in each bucket.

Can I do that?

Concerning the number of months N, I might later want to group by months per
year, so that I can gather statistics on data from more than a year.

/Chau

PS: I know that my question have some problems concerning reserved words -
it is just a problem description.

 

[Guest]

Sorry... I think I kinda missed a point. I want to group the data by months,
so that I have N (the number of months) buckets with two sums (one for each
Type) in each bucket.

Can I do that?

Concerning the number of months N, I might later want to group by months per
year, so that I can gather statistics on data from more than a year.

/Chau

PS: I know that my question have some problems concerning reserved words -
it is just a problem description.

 

[Lynn Trapp]

Then add the Month() function to your query.

SELECT Type, Sum(Amount), Month(Date)
From YourTable
Group By Type, Month(Date);


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html