Select Subquery: To only display one year at a time

J

julostarr

Access 2003

I'm using the following subquery:

In (SELECT DISTINCT S.ShipMonth FROM [Shipping Information] AS S Inner JOIN
[Orders] as O ON .[Job#]= [O].[Job#] WHERE O.Invoiced = False OR
S.FinalShipDate is Null)

which does not select SHIP MONTHS in which every entry for that month has
completed INVOICED and FINAL SHIP DATE fields. I also have a SHIP YEAR
field, which currently only has 2008 & 2009. I have 2 almost identical
queries and in each I have criteria specifying for only one year to display.
In my 2008 query the month of FEB is displaying because I now have an entry
with a SHIP MONTH of FEB for the SHIP YEAR 2009 that has not been completed
even though it does not display in the query because of the criteria 2008
under the SHIP MONTH.

My question: Is there a way to specify within this subquery or add another
subquery to only select the SHIP MONTH of those entries with a SHIP YEAR of
2008?

Note* I also have an entry for JAN 2009, but it strangely does not display
in my 2008 query for some reason unknown to me.



julostarr
 
L

Lord Kelvan

just add and s.shipyear = 2008 but also make sure in the main query
you put shipyear = 2008 as well.

ie

select * from [Shipping Information]
where shipyear = 2008
and shipmonth In (SELECT DISTINCT S.ShipMonth FROM [Shipping
Information] AS S Inner JOIN
[Orders] as O ON .[Job#]= [O].[Job#] WHERE (O.Invoiced = False OR
S.FinalShipDate is Null) and shipyear = 2008)

hope this helps

Reagrds
Kelvan
 
J

julostarr

I appreciate your help. Can you help me out with how to put it in my SQL?

This is my SQL:

SELECT [Shipping Information].ShipYear, [Shipping Information].ShipMonth,
Orders.CustomerID, Products.ProductName, [Order Details].Quantity, [Shipping
Information].OriginalShipDate, [Order Details]!Quantity*[Order
Details]!UnitPrice AS ExtPrice, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber, [Shipping Information].ShipDateChg1, [Shipping
Information].ShipDateChg2, [Shipping Information].ShipDateChg3, [Shipping
Information].FinalShipDate
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ProductID = [Order Details].ProductID) ON Orders.[Job#] = [Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
WHERE ((([Shipping Information].ShipYear)=2008) AND (([Shipping
Information].ShipMonth) In (SELECT DISTINCT S.ShipMonth FROM [Shipping
Information] AS S Inner JOIN [Orders] as O ON .[Job#]= [O].[Job#] WHERE
O.Invoiced = False OR S.FinalShipDate is Null)))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName


I tried putting it in, but I'm not sure I got it in there right. Just
adding in the shipyear=2008 doesn't do anything to stop FEB from displaying
so I wasn't sure I put it in the right place or the right way. I tried a
couple different ways of putting that in there, but I got errors.
 
L

Lord Kelvan

as i said you need to add it to both the sub query and the main query

SELECT [Shipping Information].ShipYear, [Shipping
Information].ShipMonth,
Orders.CustomerID, Products.ProductName, [Order Details].Quantity,
[Shipping
Information].OriginalShipDate, [Order Details]!Quantity*[Order
Details]!UnitPrice AS ExtPrice, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber, [Shipping Information].ShipDateChg1,
[Shipping
Information].ShipDateChg2, [Shipping Information].ShipDateChg3,
[Shipping
Information].FinalShipDate
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ProductID = [Order Details].ProductID) ON Orders.[Job#] =
[Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
WHERE ((([Shipping Information].ShipYear)=2008) AND (([Shipping
Information].ShipMonth) In (SELECT DISTINCT S.ShipMonth FROM
[Shipping
Information] AS S Inner JOIN [Orders] as O ON .[Job#]= [O].[Job#]
WHERE
(O.Invoiced = False OR S.FinalShipDate is Null) and s.shipyear =
2008)))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName

hope this helps

Regards
Kelvan
 
J

julostarr

Well, I got it to take the subquery, but it still displays FEB.

As I said before I had discovered that it was selecting FEB because there is
an entry for FEB 2009 that doesn't have it's Invoiced and Final Ship Date
fields completed as the subquery specifies to select only the months without
all records completed in these 2 fields, but that particular record doesn't
display in the query itself because I specified 2008 as the Ship Year
criteria. I know this because if I change that 2009 record's Ship Month to
MAR then this query displays MAR instead of FEB. The funny thing is that I
also have an entry for JAN 2009, but JAN doesn't display in this query as
being incomplete.

Is there any other way to tell this subquery to only run these on records
for 2008 Ship Year?
 
L

Lord Kelvan

please show me a sample of the raw data so i can see what the problem
is

Regards
Kelvan
 
J

julostarr

Ship Year Ship Month...Invoiced...Final Ship Date
2008 JAN X 1/24/2008
2008 JAN X 1/24/2008
2008 JAN X 1/24/2008
2008 FEB X 2/28/2008
2008 FEB X 2/28/2008
2008 FEB X 2/28/2008
2008 OCT 10/24/2008
2008 OCT X 10/10/2008
2008 OCT 10/21/2008
2008 NOV 11/19/2008
2008 NOV 11/6/2008
2008 DEC 12/11/2008


JAN & FEB should not display because they are completed months. The 2009
records do not display in this query because of the 2008 criteria in the Ship
Year, but their presents causes the records for the Ship Months (JAN & FEB),
that now have records for 2009 in the database, to display in this query
dispite the subquery. I can add "And Not JAN And Not FEB" but that would
defeat the purpose of the subquery.
 
L

Lord Kelvan

you may have to add the conditions of the completed things in the main query
as well as the sub query

try

SELECT [Shipping Information].ShipYear, [Shipping
Information].ShipMonth,
Orders.CustomerID, Products.ProductName, [Order Details].Quantity,
[Shipping
Information].OriginalShipDate, [Order Details]!Quantity*[Order
Details]!UnitPrice AS ExtPrice, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber, [Shipping Information].ShipDateChg1,
[Shipping
Information].ShipDateChg2, [Shipping Information].ShipDateChg3,
[Shipping
Information].FinalShipDate
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ProductID = [Order Details].ProductID) ON Orders.[Job#] =
[Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
WHERE ((O.Invoiced = False OR S.FinalShipDate is Null) and (([Shipping
Information].ShipYear)=2008) AND (([Shipping
Information].ShipMonth) In (SELECT DISTINCT S.ShipMonth FROM
[Shipping
Information] AS S Inner JOIN [Orders] as O ON .[Job#]= [O].[Job#]
WHERE
(O.Invoiced = False OR S.FinalShipDate is Null) and s.shipyear =
2008)))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName

if this dosnt work ill try again

Regards
Kelvan
 
J

julostarr

Thanks! That worked.

The only thing is that the query now prompts me to enter parameters for
O.Invoiced & S.FinalShipDate. Is there a way to stop that?
 
L

Lord Kelvan

sorry that was an oversite a copy paste error this should work


SELECT [Shipping Information].ShipYear, [Shipping
Information].ShipMonth,
Orders.CustomerID, Products.ProductName, [Order Details].Quantity,
[Shipping
Information].OriginalShipDate, [Order Details]!Quantity*[Order
Details]!UnitPrice AS ExtPrice, Orders.[Job#], Orders.Invoiced,
Orders.PurchaseOrderNumber, [Shipping Information].ShipDateChg1,
[Shipping
Information].ShipDateChg2, [Shipping Information].ShipDateChg3,
[Shipping
Information].FinalShipDate
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ProductID = [Order Details].ProductID) ON Orders.[Job#] =
[Order
Details].[Job#]) INNER JOIN [Shipping Information] ON Orders.[Job#] =
[Shipping Information].[Job#]
WHERE ((nvoiced = False OR FinalShipDate is Null) and (([Shipping
Information].ShipYear)=2008) AND (([Shipping
Information].ShipMonth) In (SELECT DISTINCT S.ShipMonth FROM
[Shipping
Information] AS S Inner JOIN [Orders] as O ON .[Job#]= [O].[Job#]
WHERE
(O.Invoiced = False OR S.FinalShipDate is Null) and s.shipyear =
2008)))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName


Regards
Kelvan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Year to Date subquery 3
Select Max and Next to Max Dates 2
Subquery for running sum blues. 1
simplifying a query with SQL 1
Query - Count of Cases and Count of where Resolution Not Completed 1
Error: Cannot use the crosstab of a non-fixed column as a subquery 4
Query By Month and Year - Access 2007 2
Parameter to Select Year in Date column 1

Top