Tom wrote:
An order may be rescheduled to ship multiple times. The tables look like:
TblOrder
OrderID
<order fields>
TblOrderShip
OrderShipID
OrderID
ShipDate
ReasonNotShipped
The ReasonNotShipped may be entered before a new ShipDate is rescheduled.
How do I write the query to determine if a specific OrderID was ever
shipped?
Thanks!
Tom
Making some assumptions about what the fields mean, I came up with this
scenario:
[TblOrder] Table Datasheet View:
OrderID Status
----------- -------
-1152314661 Shipped
1945178752 Stalled
This shows two orders, only the first of which has been shipped.
(Actually, you might not want to store current status in the [TblOrder]
Table; I just put those labels there to keep track of which order got
shipped.)
[TblOrderShip] Table Datasheet View:
OrderShipID OrderID ShipDate ReasonNotShipped
----------- ----------- ---------- ----------------
-418235218 1945178752 Fungus
402604161 -1152314661 11/10/2005
(Normally, I would set a lookup property on [TblOrderShip].[OrderID], so
it would display as "Shipped" instead of as "-1152314661", but I thought
the linkage between the Tables would be clearer if I left the raw keys
visible here.)
Assuming that either the [ShipDate] field or else the [ReasonNotShipped]
field, but not both, contains a value, only one of these orders has a
shipping date. We want a Query that will list all orders and indicate,
for each one, if it has ever been shipped (at least once). This should
do it:
[Q_Shipped?] SQL:
SELECT TblOrder.OrderID, TblOrder.Status,
Count(TblOrderShip.ShipDate) AS CountOfShipDate,
[CountOfShipDate]>0 AS [IsShipped?]
FROM TblOrder INNER JOIN TblOrderShip
ON TblOrder.OrderID = TblOrderShip.OrderID
GROUP BY TblOrder.OrderID, TblOrder.Status
HAVING (((Count(TblOrderShip.ShipDate)) Is Not Null))
ORDER BY TblOrder.OrderID;
[Q_Shipped?] Query Datasheet View:
OrderID Status CountOfShipDate IsShipped?
----------- ------- --------------- ----------
-1152314661 Shipped 1 -1
1945178752 Stalled 0 0
The [CountOfShipDate] field can be 0 or a positive number indicating the
number of records with [TblOrderShip].[ShipDate] specified; there could
be several. The [IsShipped?] field is a Yes/No type, with a value of
either -1 (true) or 0 (false), and it can be displayed as a check box or
used to filter some other Query, &c.
It's of course possible that I misapprehended your business rules. For
example, can a future [ShipDate] be recorded, intended to be ignored as
long as it is in the future? Could a record specify both a [ShipDate]
and a [ReasonNotShipped]? (You may notice that I ignored
[ReasonNotShipped], as it appeared to be immaterial here.) It would be
easy to take these into account, but you didn't say that they were
necessary.
-- Vincent Johns <vj****@alumni.caltech.edu>
Please feel free to quote anything I say here.
