Want to build a query to return quarterly data

[Prashant Rao]

Table with three columns - Order number, Created, Shipped. Order Number is
repeated because an order may have several line items.

I want to create a query that shows the number of orders that did NOT ship
in the quarter it was created. Please help - I am a light user of access but
I need this for a report this afternoon. Thanks a lot.

 

[MGFoster]

Table with three columns - Order number, Created, Shipped. Order Number is
repeated because an order may have several line items.

I want to create a query that shows the number of orders that did NOT ship
in the quarter it was created. Please help - I am a light user of access but
I need this for a report this afternoon. Thanks a lot.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this:

SELECT COUNT(*) As OrdersLines_NotShipped
FROM table_name
WHERE DatePart("q", Created) <> DatePart("q", Shipped)
AND Year(Created) = Year(Shipped)

The above will show the each line item. If you want to show just the
orders you'll need a COUNT(DISTINCT *). Unfortunately, Access SQL
doesn't have that function. So, you'll need to do something like this:

SELECT COUNT(*) As Orders_NotShipped
FROM
[SELECT OrderNumber
FROM table_name
WHERE DatePart("q", Created) <> DatePart("q", Shipped)
AND Year(Created) = Year(Shipped)
GROUP BY OrderNumber]. As C

Note that the derived table query (the query in the FROM clause) has
brackets around it with the last bracket having a period immediately
after it. Access will process this as if it were a table named "C."


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRsNIoechKqOuFEgEQJ8bQCdHNOWEC5eGBxJXqJKv7+kCFCnvWYAn0Up
GWO/+PrOF0Bdd8OKkUcjAzNu
=AmaG
-----END PGP SIGNATURE-----

 

[John Spencer]

SELECT Distinct [Order Number]
FROM [YourTable]
WHERE Format(Created,"yyyyq") <> Format(Shipped,"yyyyq")

That will show all the order numbers that had a least one shipped date that is
not in the same quarter as the created date.

If you want a count then you can use that query as the source for a totals query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County