If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Group By Qtr
I cant figure out how to group my records by quarters or months.Is there a
special query to otain these results? My current SQL is pretty basic using two tables as the following: SELECT VendorInv.EnterDate, VendorInv.InvDesc, VendorInv.InvCode, VendorInv.InvAmt FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID; The format in EnterDate field is 01/01/2008 yet i am lost on the grouping.Can this be done? |
#2
|
|||
|
|||
Group By Qtr
It can be done readily in a Report. Sort and Group on the EnterDate field in
the report. In the properties you can choose to group by day, week, month, and quarter. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "acss" wrote: I cant figure out how to group my records by quarters or months.Is there a special query to otain these results? My current SQL is pretty basic using two tables as the following: SELECT VendorInv.EnterDate, VendorInv.InvDesc, VendorInv.InvCode, VendorInv.InvAmt FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID; The format in EnterDate field is 01/01/2008 yet i am lost on the grouping.Can this be done? |
#3
|
|||
|
|||
Group By Qtr
You say the format in EnterDate field is 01/01/2008 but did not say if it was
a DateTime datatype field. Below assumes that it is -- SELECT Format([VendorInv].[EnterDate], "yyyy q") As [Vendor QTR], VendorInv.InvDesc, VendorInv.InvCode, Sum([VendorInv].[InvAmt] AS Amount FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID; GROUP BY Format([VendorInv].[EnterDate], "yyyy q"), VendorInv.InvDesc, VendorInv.InvCode; -- KARL DEWEY Build a little - Test a little "acss" wrote: I cant figure out how to group my records by quarters or months.Is there a special query to otain these results? My current SQL is pretty basic using two tables as the following: SELECT VendorInv.EnterDate, VendorInv.InvDesc, VendorInv.InvCode, VendorInv.InvAmt FROM Vendor INNER JOIN VendorInv ON Vendor.VendorID = VendorInv.VendorID; The format in EnterDate field is 01/01/2008 yet i am lost on the grouping.Can this be done? |
Thread Tools | |
Display Modes | |
|
|