By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,072 Members | 1,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,072 IT Pros & Developers. It's quick & easy.

Need Help With Query

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Nov 14 '05 #2

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

Arno R

"Tom" <th********@my.email> schreef in bericht news:u4*****************@newsread1.news.atl.earthl ink.net...
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

Nov 14 '05 #3

P: n/a
"Tom" <th********@my.email> wrote in message
news:u4*****************@newsread1.news.atl.earthl ink.net...
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?


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.
Nov 14 '05 #4

P: n/a
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

Nov 14 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.