Quarterly taxes query

[Guest]

I am trying to create a query for computing quarterly sales taxes. Doing a
computation for one quarter is simple, then I run into problems when I try
for the second quarter. So a couple of questions.

I can do the query from the table for sales where I can get the SalesID,Tax
Amount and Sale Date. I can do a sum of Tax Amount and then where the Sale
Date is between 1/01/2006 AND 3/31/2006 for the first quarter. But I can only
do one quarter at a time.

SELECT Sum([TaxAmount]) AS FirstQuarter
FROM tblSale
WHERE (((tblSale.DateOfSale) Between #1/1/2006# And #3/31/2006#));

Or should I create a new table for Sales Taxes like this and make the field
for the quartes a calculated field?

tblSalesTaxes
SalesTaxID
SaleID (to get the date and tax Amount)

Thanks
CoachBarkerOJPW

 

[Guest]

Hi

Are really needing a query or do you want the results of a query ?

If you need a report with each quarter shown you can use the sorting and
grouping option which has a "Qtr" option on the drop down.


--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.

 

[John W. Vinson/MVP]

I am trying to create a query for computing quarterly sales taxes. Doing a
computation for one quarter is simple, then I run into problems when I try
for the second quarter. So a couple of questions.

I can do the query from the table for sales where I can get the
SalesID,Tax
Amount and Sale Date. I can do a sum of Tax Amount and then where the Sale
Date is between 1/01/2006 AND 3/31/2006 for the first quarter. But I can
only
do one quarter at a time.

SELECT Sum([TaxAmount]) AS FirstQuarter
FROM tblSale
WHERE (((tblSale.DateOfSale) Between #1/1/2006# And #3/31/2006#));

Try using a Group By totals query, using the Format function to get the
quarter:

SELECT Format(DateOfSale, "YYYY-\QQ") AS TheQuarter, Sum([TaxAmount]) AS
FirstQuarter
FROM tblSale
WHERE (((tblSale.DateOfSale) Between [Enter start date] And [Enter end
date]))
GROUP BY Format(DateOfSale, "YYYY-\QQ");

This will give you a range of dates with the quarters shown as 2005-Q4,
2006-Q1, 2006-Q2 and so on, with the total for that quarter.


John W. Vinson/MVP