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 Zoltán Zörgő 220
1 Peter Leow 160
2 CPallini 120
3 BillWoodruff 85
4 bling 80
0 Sergey Alexandrovich Kryukov 9,428
1 OriginalGriff 6,901
2 Peter Leow 4,737
3 Zoltán Zörgő 4,349
4 CHill60 2,932

Advertise | Privacy | Mobile
Web03 | 2.8.150129.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