Need Help With Query

[Tom]

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

[Vincent Johns]

Tom wrote:

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 <vjo...@alumni.caltech.edu>
Please feel free to quote anything I say here.

[Arno R]

Ask Steve ...
It seems to me that this is a *real difficult* question. We need a Resource here....

Arno R

"Tom" <thios...@my.email> schreef in bericht news:u4Pdf.8738$m81....@newsread1.news.atl.earthlink.net...

[Keith W]

"Tom" <thios...@my.email> wrote in message
news:u4Pdf.8738$m81....@newsread1.news.atl.earthlink.net...

Hi Tom,

No-one here can help you, we're all as thick as pig doo, you need a
super-hero like ... oh, what's his name now ... wears his underpants on the
outside ... PC something or other ... anyone?

Regards,
Shirley.

[jimfo...@compumarc.com]

Arno R wrote:
> Ask Steve ...
> It seems to me that this is a *real difficult* question. We need a Resource here....
>
> Arno R

LOL. He's so busted. Good catch Arno. I'm detecting a pattern here.
Contact him to buy software written (and supported!) by others.

James A. Fortune