Excluding a month of records ONLY after all fields are updated.

J

julostarr

I have a query for Sales Orders that has a [FinalShipDate] field and an
[Invoiced] field that uses a Yes/No checkbox. This is connected to a
report. In the report all entries are separated by month. Is there a way to
exclude an entire month from displaying ONLY once all the [FinalShipDate]s
are entered AND all the [invoice] checkboxes are check for that month? In
other words I still want all entries to display for that month until all the
[FinalShipDate]s are entered and each one has the [Invoiced] box checked.
The way I have to exclude them now is to go into the query at the end of
every month to check if all these things have been done and then exclude the
whole month from displaying by using <> "Month1" And <> "Month2" etc.
 
K

KARL DEWEY

Do you have an OrderDate field so as to know which month the record is in?
Post your table structure - field names with datatype. Post sample data.
 
J

John Spencer

If you have a large number of records this will be slow.

SELECT *
FROM SALES
WHERE Format(FinalShipDate,"yymm") NOT IN
(SELECT DISTINCT Format(FinalShipDate,"yymm")
FROM SALES
WHERE Invoiced = False
and FinalShipDate is Not Null)

In query design view
-- add calculated field
Format(FinalShipDate,"yymm")
-- Set the criteria under the field to
NOT IN (SELECT DISTINCT Format(FinalShipDate,"yymm")FROM SALES WHERE
Invoiced = False and FinalShipDate is Not Null)

If this is TOO slow then you can use an alternative solution IF your table and
field names consist of only letters, numbers, and underscore characters. That
should look something like the following:

SELECT Sales.*
FROM Sales INNER JOIN
(SELECT DISTINCT Format(FinalShipDate,"yymm") as TheDate
FROM SALES
WHERE Invoiced = False and FinalShipDate is Not Null) as Q
ON FORMAT(Sales.FinalShipDate,"yymm") = Q.TheDate



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

julostarr

Here's an example. The [ShipMonth] shows the month that the record is
scheduled to ship, [Invoiced] is checked if it has been invoiced and
[FinalSD] (or Final Ship Date) tells the date that it was shipped out.

[Ship Month]...[Invoiced?]...[Final SD]
08 - AUG (x) 8/8/08
08 - AUG (x) 8/25/08
09 - SEP (x) 9/8/08
09 - SEP ( ) __/__/__
10 - OCT ( ) __/__/__

[ShipMonth] is a Text field, [Invoiced] is a Yes/No field and [FinalSD] is a
Date/Time field.

An example of what I would want to do with this is to exclude a month like
August (08 - AUG) ONLY when all [Invoiced] fields are checked Yes AND all
[FinalSD] fields are filled in. In the above example I would not want August
to display because every entry is complete, but all records in September
should display because the second entry is not complete. Right now I have
criteria in my query that goes like this <> "O1 - JAN" And "02 - FEB"....and
so on to exclude months. I have to add the new months to this criteria once
I see that all entries have been completed for that month. It would make it
easier if the query could automatically exclude the month once all records
are complete.

--
julostarr


KARL DEWEY said:
Do you have an OrderDate field so as to know which month the record is in?
Post your table structure - field names with datatype. Post sample data.
--
KARL DEWEY
Build a little - Test a little


julostarr said:
I have a query for Sales Orders that has a [FinalShipDate] field and an
[Invoiced] field that uses a Yes/No checkbox. This is connected to a
report. In the report all entries are separated by month. Is there a way to
exclude an entire month from displaying ONLY once all the [FinalShipDate]s
are entered AND all the [invoice] checkboxes are check for that month? In
other words I still want all entries to display for that month until all the
[FinalShipDate]s are entered and each one has the [Invoiced] box checked.
The way I have to exclude them now is to go into the query at the end of
every month to check if all these things have been done and then exclude the
whole month from displaying by using <> "Month1" And <> "Month2" etc.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try these two queries --
Query named ShipComp ---
SELECT [ShipMonth]
FROM [Sales Orders]
WHERE Sum(Abs([Invoiced?])) = Count([ShipMonth]) And Sum(IIF([Final SD] Is
Not Null, 1, 0)) = Count([ShipMonth])
GROUP BY [ShipMonth];

SELECT [ShipMonth], [Invoiced?], [Final SD]
FROM [Sales Orders], [ShipComp]
WHERE [Sales Orders].[ShipMonth] <> [ShipComp].[ShipMonth];

--
KARL DEWEY
Build a little - Test a little


julostarr said:
Here's an example. The [ShipMonth] shows the month that the record is
scheduled to ship, [Invoiced] is checked if it has been invoiced and
[FinalSD] (or Final Ship Date) tells the date that it was shipped out.

[Ship Month]...[Invoiced?]...[Final SD]
08 - AUG (x) 8/8/08
08 - AUG (x) 8/25/08
09 - SEP (x) 9/8/08
09 - SEP ( ) __/__/__
10 - OCT ( ) __/__/__

[ShipMonth] is a Text field, [Invoiced] is a Yes/No field and [FinalSD] is a
Date/Time field.

An example of what I would want to do with this is to exclude a month like
August (08 - AUG) ONLY when all [Invoiced] fields are checked Yes AND all
[FinalSD] fields are filled in. In the above example I would not want August
to display because every entry is complete, but all records in September
should display because the second entry is not complete. Right now I have
criteria in my query that goes like this <> "O1 - JAN" And "02 - FEB"....and
so on to exclude months. I have to add the new months to this criteria once
I see that all entries have been completed for that month. It would make it
easier if the query could automatically exclude the month once all records
are complete.

--
julostarr


KARL DEWEY said:
Do you have an OrderDate field so as to know which month the record is in?
Post your table structure - field names with datatype. Post sample data.
--
KARL DEWEY
Build a little - Test a little


julostarr said:
I have a query for Sales Orders that has a [FinalShipDate] field and an
[Invoiced] field that uses a Yes/No checkbox. This is connected to a
report. In the report all entries are separated by month. Is there a way to
exclude an entire month from displaying ONLY once all the [FinalShipDate]s
are entered AND all the [invoice] checkboxes are check for that month? In
other words I still want all entries to display for that month until all the
[FinalShipDate]s are entered and each one has the [Invoiced] box checked.
The way I have to exclude them now is to go into the query at the end of
every month to check if all these things have been done and then exclude the
whole month from displaying by using <> "Month1" And <> "Month2" etc.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Pardon me, but I think you need a slight modification to the first query

SELECT [Ship Month]
FROM [Sales Orders]
GROUP BY [ShipMonth]
HAVING Sum(Abs([Invoiced?])) = Count([Ship Month])
AND Count([Final SD]) = Count([Ship Month])

And actually, you could probably do this all in one query as follows:

SELECT *
FROM [Sales Orders]
WHERE [Ship Month] in
(SELECT [Ship Month]
FROM [Sales Orders]
GROUP BY [ShipMonth]
HAVING Sum(Abs([Invoiced?])) <> Count([Ship Month])
OR Count([Final SD]) <> Count([Ship Month]) )


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

julostarr

So I need to build a new query or is there a way to use my existing query and
just modify it? Here is my query in SQL view:

SELECT [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].ShipMonth)<>"01 - JAN" And ([Shipping
Information].ShipMonth)<>"02 - FEB" And ([Shipping
Information].ShipMonth)<>"03 - MAR" And ([Shipping
Information].ShipMonth)<>"04 - APR" And ([Shipping
Information].ShipMonth)<>"05 - MAY" And ([Shipping
Information].ShipMonth)<>"06 - JUN" And ([Shipping
Information].ShipMonth)<>"07 - JUL" And ([Shipping
Information].ShipMonth)<>"08 - AUG") AND (([Shipping
Information].ShipYear)=2008))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName, [Shipping
Information].ShipYear;
--
julostarr


John Spencer said:
If you have a large number of records this will be slow.

SELECT *
FROM SALES
WHERE Format(FinalShipDate,"yymm") NOT IN
(SELECT DISTINCT Format(FinalShipDate,"yymm")
FROM SALES
WHERE Invoiced = False
and FinalShipDate is Not Null)

In query design view
-- add calculated field
Format(FinalShipDate,"yymm")
-- Set the criteria under the field to
NOT IN (SELECT DISTINCT Format(FinalShipDate,"yymm")FROM SALES WHERE
Invoiced = False and FinalShipDate is Not Null)

If this is TOO slow then you can use an alternative solution IF your table and
field names consist of only letters, numbers, and underscore characters. That
should look something like the following:

SELECT Sales.*
FROM Sales INNER JOIN
(SELECT DISTINCT Format(FinalShipDate,"yymm") as TheDate
FROM SALES
WHERE Invoiced = False and FinalShipDate is Not Null) as Q
ON FORMAT(Sales.FinalShipDate,"yymm") = Q.TheDate



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a query for Sales Orders that has a [FinalShipDate] field and an
[Invoiced] field that uses a Yes/No checkbox. This is connected to a
report. In the report all entries are separated by month. Is there a way to
exclude an entire month from displaying ONLY once all the [FinalShipDate]s
are entered AND all the [invoice] checkboxes are check for that month? In
other words I still want all entries to display for that month until all the
[FinalShipDate]s are entered and each one has the [Invoiced] box checked.
The way I have to exclude them now is to go into the query at the end of
every month to check if all these things have been done and then exclude the
whole month from displaying by using <> "Month1" And <> "Month2" etc.
Click to expand...
Click to expand...
 
J

John Spencer

You need to build a new query and save it.

SELECT DISTINCT ShipMonth as TheDate
FROM [Shipping Information] Inner JOIN [Orders]
ON [Shipping Information].[Job#]= [Orders].[Job#]
WHERE Invoiced = False OR FinalShipDate is Null

Call that qOpenMonth

Now add that query into your existing query and join on
qOpenMonth.TheDate to [Shipping Information].ShipMonth.

Drop all the screening of ship month in the where clause.

That should end up looking something like the following.

SELECT [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#])
INNER JOIN qOpenMonth
ON qOpenMonth.TheDate = [Shipping Information].ShipMonth



WHERE [Shipping Information].ShipYear=2008

ORDER BY [Shipping Information].ShipMonth, Products.ProductName,
[Shipping Information].ShipYear;

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

So I need to build a new query or is there a way to use my existing query and
just modify it? Here is my query in SQL view:

SELECT [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].ShipMonth)<>"01 - JAN" And ([Shipping
Information].ShipMonth)<>"02 - FEB" And ([Shipping
Information].ShipMonth)<>"03 - MAR" And ([Shipping
Information].ShipMonth)<>"04 - APR" And ([Shipping
Information].ShipMonth)<>"05 - MAY" And ([Shipping
Information].ShipMonth)<>"06 - JUN" And ([Shipping
Information].ShipMonth)<>"07 - JUL" And ([Shipping
Information].ShipMonth)<>"08 - AUG") AND (([Shipping
Information].ShipYear)=2008))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName, [Shipping
Information].ShipYear;
Click to expand...
 
J

julostarr

Thanks! That worked. The only problem is that the query won't allow me to
make any modifications to the records. Will I have to use another query to
modify records?
--
julostarr


John Spencer said:
You need to build a new query and save it.

SELECT DISTINCT ShipMonth as TheDate
FROM [Shipping Information] Inner JOIN [Orders]
ON [Shipping Information].[Job#]= [Orders].[Job#]
WHERE Invoiced = False OR FinalShipDate is Null

Call that qOpenMonth

Now add that query into your existing query and join on
qOpenMonth.TheDate to [Shipping Information].ShipMonth.

Drop all the screening of ship month in the where clause.

That should end up looking something like the following.

SELECT [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#])
INNER JOIN qOpenMonth
ON qOpenMonth.TheDate = [Shipping Information].ShipMonth



WHERE [Shipping Information].ShipYear=2008

ORDER BY [Shipping Information].ShipMonth, Products.ProductName,
[Shipping Information].ShipYear;

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

So I need to build a new query or is there a way to use my existing query and
just modify it? Here is my query in SQL view:

SELECT [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].ShipMonth)<>"01 - JAN" And ([Shipping
Information].ShipMonth)<>"02 - FEB" And ([Shipping
Information].ShipMonth)<>"03 - MAR" And ([Shipping
Information].ShipMonth)<>"04 - APR" And ([Shipping
Information].ShipMonth)<>"05 - MAY" And ([Shipping
Information].ShipMonth)<>"06 - JUN" And ([Shipping
Information].ShipMonth)<>"07 - JUL" And ([Shipping
Information].ShipMonth)<>"08 - AUG") AND (([Shipping
Information].ShipYear)=2008))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName, [Shipping
Information].ShipYear;
Click to expand...
Click to expand...
 
J

John Spencer

Was that query updatable before? Usually (not always) a query with more than
3 tables will not be updatable.

If you are going to change data you should be using forms based on queries.
Often you will need a form with one or more subforms.



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

julostarr

Yes, it was updatable before. It has the Orders table that pulls in the
Shipping Information and the Customer ID then the Orders Detail stems off of
Orders and then Products stems off of that. I can use another query to
update these fields, but I did wonder why it doesn't allow updates now. I do
have an Order Entry form, which is great for new orders, but since it has
subforms it is easier to use Ctrl + F to find the records faster for updating
the FinalShipDate and Invocied fields than using the form. Using Ctrl + F in
a form like this doesn't search all fields for the numbers to update.
 
J

John Spencer

Ok, try this.

Remove the query from the join

and change the WHERE clause to use

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

That construct should work. You can try it with and without DISTINCT to see
if there is any performance difference.

SELECT [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 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
, [Shipping Information].ShipYear;

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

John said:
You need to build a new query and save it.

SELECT DISTINCT ShipMonth as TheDate
FROM [Shipping Information] Inner JOIN [Orders]
ON [Shipping Information].[Job#]= [Orders].[Job#]
WHERE Invoiced = False OR FinalShipDate is Null

Call that qOpenMonth

Now add that query into your existing query and join on
qOpenMonth.TheDate to [Shipping Information].ShipMonth.

Drop all the screening of ship month in the where clause.

That should end up looking something like the following.

SELECT [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#])
INNER JOIN qOpenMonth
ON qOpenMonth.TheDate = [Shipping Information].ShipMonth



WHERE [Shipping Information].ShipYear=2008

ORDER BY [Shipping Information].ShipMonth, Products.ProductName,
[Shipping Information].ShipYear;

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

So I need to build a new query or is there a way to use my existing
query and just modify it? Here is my query in SQL view:

SELECT [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].ShipMonth)<>"01 - JAN" And ([Shipping
Information].ShipMonth)<>"02 - FEB" And ([Shipping
Information].ShipMonth)<>"03 - MAR" And ([Shipping
Information].ShipMonth)<>"04 - APR" And ([Shipping
Information].ShipMonth)<>"05 - MAY" And ([Shipping
Information].ShipMonth)<>"06 - JUN" And ([Shipping
Information].ShipMonth)<>"07 - JUL" And ([Shipping
Information].ShipMonth)<>"08 - AUG") AND (([Shipping
Information].ShipYear)=2008))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName,
[Shipping Information].ShipYear;
Click to expand...
Click to expand...
 
J

julostarr

That did make it updatable. Thanks!

Is the other query I created still neccesary to keep? Is it still a factor
in this query? It doesn't appear that it is, but I want to make sure so I
don't delete it if it is still a factor.
--
julostarr


John Spencer said:
Ok, try this.

Remove the query from the join

and change the WHERE clause to use

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

That construct should work. You can try it with and without DISTINCT to see
if there is any performance difference.

SELECT [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 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
, [Shipping Information].ShipYear;

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

John said:
You need to build a new query and save it.

SELECT DISTINCT ShipMonth as TheDate
FROM [Shipping Information] Inner JOIN [Orders]
ON [Shipping Information].[Job#]= [Orders].[Job#]
WHERE Invoiced = False OR FinalShipDate is Null

Call that qOpenMonth

Now add that query into your existing query and join on
qOpenMonth.TheDate to [Shipping Information].ShipMonth.

Drop all the screening of ship month in the where clause.

That should end up looking something like the following.

SELECT [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#])
INNER JOIN qOpenMonth
ON qOpenMonth.TheDate = [Shipping Information].ShipMonth



WHERE [Shipping Information].ShipYear=2008

ORDER BY [Shipping Information].ShipMonth, Products.ProductName,
[Shipping Information].ShipYear;

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

So I need to build a new query or is there a way to use my existing
query and just modify it? Here is my query in SQL view:

SELECT [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].ShipMonth)<>"01 - JAN" And ([Shipping
Information].ShipMonth)<>"02 - FEB" And ([Shipping
Information].ShipMonth)<>"03 - MAR" And ([Shipping
Information].ShipMonth)<>"04 - APR" And ([Shipping
Information].ShipMonth)<>"05 - MAY" And ([Shipping
Information].ShipMonth)<>"06 - JUN" And ([Shipping
Information].ShipMonth)<>"07 - JUL" And ([Shipping
Information].ShipMonth)<>"08 - AUG") AND (([Shipping
Information].ShipYear)=2008))
ORDER BY [Shipping Information].ShipMonth, Products.ProductName,
[Shipping Information].ShipYear;
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

No, I don't think you need to keep the other query.


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

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

Select Subquery: To only display one year at a time 11
To find missing entries - Delivery Note Nos 13
Return all dates for 'next month' irrespective of year or day 4
How to get all records in a Query even you select a record from a conbox box 2
Date Records 1
count() in a query 5
Counts of dates and sum of sales by month 2
Query based on values in fields 7

Top