Prev: Help with a query...
Next: DSUM Syntax Error
From: acss on 30 Jun 2008 15:30
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?
From: Jerry Whittle on 30 Jun 2008 15:54
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?
From: KARL DEWEY on 30 Jun 2008 16:54
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?
 | 
Pages: 1
Prev: Help with a query...
Next: DSUM Syntax Error