how to group data in month?

[sallyGals]

Now i had group my data in year, my report will only show all the data in year.
But if i want to group data in month to make all data show in that month only, how to modifyt my sql statement?
Example: now the database consits of MARCH data, when it reach APRIL, it will no data to show in report.


report: rptstockonhand1
source: qrystockOnHand1 (make up of qryLotOutput and qryTaken)

this report is to show the stockonhand, it is quantity created - quantity taken..


below is my query


qryLOtOutput:
SELECT [tblProduct].[LineStator], [tblLotCreated].[ProductNo], [tblProduct].[Model], Sum([tblLotCreated].[QtyCreated]) AS SumOfQtyCreated, [tblLotCreated].[StoreLocation], Format$([tblLotCreated].[DateCreated],'yyyy') AS [Year], [tblLotCreated].[LotID]
FROM tblProduct INNER JOIN tblLotCreated ON [tblProduct].[ProductNo]=[tblLotCreated].[ProductNo]
GROUP BY [tblProduct].[LineStator], [tblLotCreated].[ProductNo], [tblProduct].[Model], [tblLotCreated].[StoreLocation], Format$([tblLotCreated].[DateCreated],'yyyy'), [tblLotCreated].[LotID];


qryTaken::
SELECT [tblTaken].[ProductNo], Sum([tblTaken].[QtyTaken]) AS SumOfQtyTaken, Format$([tblTaken].[DateTaken],'yyyy') AS [Year]
FROM tblTaken
GROUP BY [tblTaken].[ProductNo], Format$([tblTaken].[DateTaken],'yyyy');


qryStockOnHand1::
SELECT [qryLotOutput].[Year], [qryLotOutput].[LotID], [qryLotOutput].[ProductNo], [qryLotOutput].[LineStator], [qryLotOutput].[SumOfQtyCreated] AS SumOfSumOfQtyCreated, IIf([qryTaken].[SumOfQtyTaken] Is Null,0,[qryTaken].[SumOfQtyTaken]) AS SumOfSumOfQtyTaken, [qryLotOutput].[Model]
FROM qryLotOutput LEFT JOIN qryTaken ON ([qryLotOutput].[ProductNo]=[qryTaken].[ProductNo]) AND ([qryLotOutput].[Year] Like [qryTaken].[Year])
WHERE [qryLotOutput].[Year]=year(now())
ORDER BY [qryLotOutput].[Year];


how to modify it?
thx

 

[HarleyQuinn]

You can change the Format() of the Columns you called Year. E.g.

Format$([tblTaken].[DateTaken],'mm') AS [Month]

You can play around with the format of the output ('mm') so it uses full month names etc.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SBerthold]

If the data is restricted to a year, you can also use the MONTH() function.

If not, then you have to use

YEAR(DateCreated),MONTH(DateCreated)

 

[Golom]

Change

Format$([SomeField],'yyyy')

to

Format$([SomeField],'yyyy[COLOR=red]mm[/color]')

everywhere that you are formatting a date field to "yyyy

 

[SBerthold]

Either the Format or the YEAR/MONTH should work.
Could even do
YEAR(DateCreated)& MONTH(DateCreated)
to make it 200602

If you have a lot of records then I would use YEAR/MONTH if possible, as they are much faster than the Format function.

 

[sallyGals]

that means i have to change all the year to month in these three query?

 

[HarleyQuinn]

In short, yes.

You will also have to modify your WHERE clause in qryStockOnHand1 to look for your new foramtting of the data, that should probably include the year as well as the month (like every example except mine).

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.