Linking two Tables and avoiding having to create an interim Query

[John Ortt]

I have a database with the tables "ORDERS" and "PROBLEMS".

The "PROBLEMS" table stores data where the progress of an order is stopped
due to an issue.

Due to this there can be many instances of a single order which is being
looked aton the "PROBLEMS" table, but only one will ever be live (i.e not
closed)

I wish to show the order detail and any problem information which is
outstanding on a query.

The difficulty I am having is that if I pull in the "ORDERS" table and link
it to the "PROBLEMS" table in the form
"Show all records on the ORDERS table and only those...." etc

With a criteria that the [PROBLEMS].[ClosedDate] is null, it does not show
all the records as some have never had any problems against them atall.

It I create the same link from the "ORDERS" table to a query I have created
which filters out the completed problems prior to pulling it in it works
fine.

Essentially I am asking if there is any way to use the two source files and
not have to create an interim query which filters out the completed
problems.

I hope this makes sense...

Thanks for your time,

John

 

[Gerald Stanley]

I would suggest that you try an outer join query along the
lines of
SELECT ........
FROM Orders LEFT JOIN Problems ON Orders.orderNo =
Problems.orderNo
WHERE Problems.closedDate IS NULL;

Hope This Helps
Gerald Stanley MCSD

 

[John Ortt]

Thankyou for the prompt response Gerald.

Unfortunately that does not solve the problem. The following is I believe
the same as your code.
It was my first attempt at the code (and the one I would like to get to work
if possible):

Desired Query
-----------------
SELECT Orders.*, Problems.Problem
FROM Orders LEFT JOIN Problems ON Orders.OrderNo = Problems.OrderNo
WHERE (((Problems.CloseDate) Is Null));

< The next two queries are my interim Query and the new query to replace the
one above. >

Query 1 (ProblemsActive)
-------------------------------
SELECT Problems.*
FROM Problems
WHERE (((Problems.CloseDate) Is Null));

Current Query
-----------------
SELECT Orders.*, ProblemsActive.Problem
FROM Orders LEFT JOIN ProblemsActive ON Orders.OrderNo =
ProblemsActive.OrderNo;

< The above solution gives the desired results with no omissions but I would
prefer to use only one query if it is possible. >

Thankyou once again for your time,

John

I would suggest that you try an outer join query along the
lines of
SELECT ........
FROM Orders LEFT JOIN Problems ON Orders.orderNo =
Problems.orderNo
WHERE Problems.closedDate IS NULL;

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a database with the tables "ORDERS" and "PROBLEMS".

The "PROBLEMS" table stores data where the progress of an order is stopped
due to an issue.

Due to this there can be many instances of a single order which is being
looked aton the "PROBLEMS" table, but only one will ever be live (i.e not
closed)

I wish to show the order detail and any problem information which is
outstanding on a query.

The difficulty I am having is that if I pull in the "ORDERS" table and link
it to the "PROBLEMS" table in the form
"Show all records on the ORDERS table and only those...." etc

With a criteria that the [PROBLEMS].[ClosedDate] is null, it does not show
all the records as some have never had any problems against them atall.

It I create the same link from the "ORDERS" table to a query I have created
which filters out the completed problems prior to pulling it in it works
fine.

Essentially I am asking if there is any way to use the two source files and
not have to create an interim query which filters out the completed
problems.

I hope this makes sense...

Thanks for your time,

John


.

 

[Gerald Stanley]

Was it the case that my initial solution was missing those
orders with problems that had all been resolved. In which
case, you could try something along the lines of

SELECT DISTINCT Orders.*, Problems.Problem
FROM Orders LEFT JOIN Problems ON Orders.OrderNo =
Problems.OrderNo
WHERE (((Problems.CloseDate) Is Null))
OR Orders.orderNo IN (SELECT orderNo FROM Orders)

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Thankyou for the prompt response Gerald.

Unfortunately that does not solve the problem. The following is I believe
the same as your code.
It was my first attempt at the code (and the one I would like to get to work
if possible):

Desired Query
-----------------
SELECT Orders.*, Problems.Problem
FROM Orders LEFT JOIN Problems ON Orders.OrderNo = Problems.OrderNo
WHERE (((Problems.CloseDate) Is Null));

< The next two queries are my interim Query and the new query to replace the
one above. >

Query 1 (ProblemsActive)
-------------------------------
SELECT Problems.*
FROM Problems
WHERE (((Problems.CloseDate) Is Null));

Current Query
-----------------
SELECT Orders.*, ProblemsActive.Problem
FROM Orders LEFT JOIN ProblemsActive ON Orders.OrderNo =
ProblemsActive.OrderNo;

< The above solution gives the desired results with no omissions but I would
prefer to use only one query if it is possible. >

Thankyou once again for your time,

John

I would suggest that you try an outer join query along the
lines of
SELECT ........
FROM Orders LEFT JOIN Problems ON Orders.orderNo =
Problems.orderNo
WHERE Problems.closedDate IS NULL;

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a database with the tables "ORDERS" and "PROBLEMS".

The "PROBLEMS" table stores data where the progress of an order is stopped
due to an issue.

Due to this there can be many instances of a single order which is being
looked aton the "PROBLEMS" table, but only one will ever be live (i.e not
closed)

I wish to show the order detail and any problem information which is
outstanding on a query.

The difficulty I am having is that if I pull in the "ORDERS" table and link
it to the "PROBLEMS" table in the form
"Show all records on the ORDERS table and only those...." etc

With a criteria that the [PROBLEMS].[ClosedDate] is null, it does not show
all the records as some have never had any problems against them atall.

It I create the same link from the "ORDERS" table to a query I have created
which filters out the completed problems prior to pulling it in it works
fine.

Essentially I am asking if there is any way to use the two source files and
not have to create an interim query which filters out the completed
problems.

I hope this makes sense...

Thanks for your time,

John


.

.

 

[John Ortt]

That was very close to what I was after Gerard, but it is showing the closed
problems aswell.

I wish to show all orders and only have an entry in the problem column if
there is an active problem.

What exactly does the "IN (SELECT orderNo FROM Orders)" section of the code
do?

I am not in work again until early Friday morning (GMT) so please don't
think I am ignoring you if it takes a while for me to reply.

Thankyou once again for assisting me with this problem, it is greatly
appreciated.

John

Was it the case that my initial solution was missing those
orders with problems that had all been resolved. In which
case, you could try something along the lines of

SELECT DISTINCT Orders.*, Problems.Problem
FROM Orders LEFT JOIN Problems ON Orders.OrderNo =
Problems.OrderNo
WHERE (((Problems.CloseDate) Is Null))
OR Orders.orderNo IN (SELECT orderNo FROM Orders)

<Snipped>

 

[John Ortt]

I've sorted it.....

I used the following expression:

SELECT DISTINCT Orders.*, (SELECT first(Problem) FROM Problems WHERE
((CLOSEDATE is null) and (Problems.OrderNo = Orders.OrderNo))) AS Problem
FROM Orders, Problems;

I thought of using a subquery thanks to your "IN" statement (although I
still don't completely understand it

Thanks again,

John