Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL
Respected sir/s,
i m using ms-access as backend and as front end for developing windows application...
In which i m using query for generate monthly report..
like bellow
Item_Name   TotQntyPurchsed    TotQntySale    TotQntyInStock
abc            15                 10              5
xyz            20                 18              2
here all the columns are grouped by for getting respective item total...
but i want this for every month..
then how can i do this with group by clause because sale and perchase will be on daily basis
any other idea to achieve this...?
plz help me..
thank you
Posted 11-Nov-12 9:43am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

use this query:
YEAR(DatePurchased)*100+MONTH(DatePurchased) AS YearMonth,
Sum(QtyInStock) as TotQtyInStock
FROM Stock
GROUP BY Item_Name,

Then you'd have this:
Item_Name   YearMonth  TotQntyInStock
abc         201201      5
abc         201202      2
abc         201203      1
xyz         201201      1
xyz         201205      5
Milind Thakkar at 12-Nov-12 0:11am
I don;t know Access. trying to understand and confused by the multiplication. Why year multiplied by 100 ?
Andrew Cherednik at 12-Nov-12 0:20am
This is a trick to allow grouping by month. See, how the result magically become 'YYYYMM'
Sunil Bansode at 12-Nov-12 21:24pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 245
1 Kamal Rocks 184
2 Sergey Alexandrovich Kryukov 175
3 BillWoodruff 173
4 PIEBALDconsult 160
0 OriginalGriff 5,655
1 DamithSL 4,506
2 Maciej Los 3,997
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180

Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 11 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100