Click here to Skip to main content
13,139,834 members (43,308 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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 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 12-Nov-12 0:20am
This is a trick to allow grouping by month. See, how the result magically become 'YYYYMM'
Sunil Bansode 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
Top Experts
Last 24hrsThis month

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