Need Help With Query

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
Dear Tom:

It seems to me your definition of "ever shipped" might be one of two things:

- a ShipDate has been entered, or
- ReasonNotShipped is NULL or empty

or perhaps it is when both of these occur. I'll work with this last case.

SELECT O.OrderID, O.OtherColumns, S.ShipDate
FROM TblOrder O
INNER JOIN TblOrderShip S ON S.OrderID = O.OrderID
WHERE S.ShipDate IS NOT NULL AND Nz(S.ReasonNotShipped, "") = ""

Now, is this anywhere close to what you want? Please let me know if this
helped, and if there is any other assistance required.

Tom Ellison
 
Tom said:
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
Click to expand...

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 <[email protected]>
Please feel free to quote anything I say here.
 
Steve,

Why the need to post using a false identity? You blew it by posting an
answer as "Tom" to the thread titled "Custom Menu Bars Question", and
including your PC Datasheet contact info., without first changing your
identity back.

If you need help as PC Datasheet, just say so. No one will hold it against
you. However, using a false identity to post a question, while anwering other
questions with your true identity, is just wrong.

Tom Wickerath
___________________________________________
 
There are MVPs who do it!!


Tom Wickerath said:
Steve,

Why the need to post using a false identity? You blew it by posting an
answer as "Tom" to the thread titled "Custom Menu Bars Question", and
including your PC Datasheet contact info., without first changing your
identity back.

If you need help as PC Datasheet, just say so. No one will hold it against
you. However, using a false identity to post a question, while anwering
other
questions with your true identity, is just wrong.

Tom Wickerath
___________________________________________
Click to expand...
 
There are MVPs who do it!!

Really? I'd sure be interested in knowing who. In any case, two wrongs
doesn't make a right. Post questions as yourself. People will have more
respect for you if you do so. And if anyone tries to flame you for posting as
yourself, they're just opening themselves up to getting flamed 100 times
worse in return.

Just my 2 cents worth.

Tom
__________________________________________
 
He is trying to preserve the illusion that he only answers questions.

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

Arno R
 
Tom Wickerath said:
Steve,

Why the need to post using a false identity? You blew it by posting an
answer as "Tom" to the thread titled "Custom Menu Bars Question", and
including your PC Datasheet contact info., without first changing your
identity back.
Click to expand...
Now *that's* funny.
 
Tom said:
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?
Click to expand...

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.
 
Master santos has annoyed some of the regulars with his constant soliciting
that they said would not answer any of his questions until he stops
soliciting. So he is trying to fool them into answering his questions by
pretending to be someone else.

John... Visio MVP
 
Arno said:
Ask Steve ...
It seems to me that this is a *real difficult* question. We need a Resource here....

Arno R
Click to expand...

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
 
Hi John,

While I find the later responses from Arno, Jim Fortune and Keith W
(Shirley) funny, these types of replies don't exactly encourage Steve to post
questions as himself. Yes, I'm aware of his soliciting. I've even answered
(unwittingly) some questions for him in the past, when he used a female name.
I wasn't very happy about that situation when I found out the truth. I'd much
rather have known that it was Steve Santos that had asked the question, and
then I could decide whether or not to answer it. I'd likely still answer the
question, if I knew the answer and it had not already been answered.


Tom
______________________________________________

:

Master santos has annoyed some of the regulars with his constant soliciting
that they said would not answer any of his questions until he stops
soliciting. So he is trying to fool them into answering his questions by
pretending to be someone else.

John... Visio MVP
 
The aim is not to encourage him to use his own name, but to discourage his
solicitations. If he stops his solicitations, he should have no problems
with posting with his own name.

John... Visio MVP
 
Back
Top