Click here to Skip to main content
11,802,762 members (70,668 online)
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 8: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 KrunalRohit 366
1 OriginalGriff 365
2 F-ES Sitecore 295
3 Afzaal Ahmad Zeeshan 230
4 CPallini 230
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,695
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web02 | 2.8.151002.1 | Last Updated 11 Nov 2012
Copyright © CodeProject, 1999-2015
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