I Combined 2 queries, but some records were missing.

[Aldred@office]

Hi all,
I have 2 queries. I comebined them but some records are missing. The
situation is as follow:

Query 1:
SELECT Distinct PartNumID AS PID, Manu, POOutNum, PartNum, Quantity,
DueDate, tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID;

Result of Query 1:
PID Manu POOutNum PartNum Quantity DueDate DeliveryDate SpecialInst
95 HH 4729 108-X13 4730 23/9/2009 18/9/2009
33 HH 4729 108-R06 20000 23/9/2009 18/9/2009
38 HH 4729 108-F84 20000 23/9/2009 18/9/2009

Query 2:
SELECT Sum(tDelivered.DQuantity) AS Delivered
FROM tPOitems, tPOin, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOitems.POinID
And tPOitems.ID=tDelivered.POitemsID
And tDelivered.PartNumID=38;

Reult of Query 2:
Delivered
5000

Combined:
SELECT tPOItems.PartNumID AS PID, Manu, POOutNum, PartNum, Quantity,
Sum(DQuantity) As Delivered, DueDate, tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID
and tDelivered.PartNumID = tPOItems.PartNumID
and tPOitems.ID = tDelivered.POItemsID
Group by tPOItems.PartNumID, Manu, POOutNum, PartNum, Quantity, DueDate,
tPOItems.DeliveryDate, SpecialInst

Result of Combined Query:
PID Manu POOutNum PartNum Quantity Delivered DueDate DeliveryDate
SpecialInst
33 HH 4729 108-R06 20000 9000 23/9/2009 18/9/2009
38 HH 4729 108-F84 20000 5000 23/9/2009 18/9/2009


The problem is that in the Combined Query, the record with PID 95 is
missing. I know this makes sense because there is not delivered information
stored in the table "tDelivery". So how could I make my query shows all 3
records in the combined query just like the Query 1 does? In the Delivered
field, it will be best if it shows a zero but leaving it as blank is just
fine. Can some one please help me?

Thanks.

 

[Dale Fye]

Aldred,

Instead of using the WHERE X = Y construct to join your tables, you need to
start using JOIN clauses. This will give you the ability to generate
unmatched queries (value exists in one table, but not the other). As an
example of this, lets assume you have two tables (T1, and T2), each with the
field PartNum and data as follows:

T1.PartNum
1
2
3
4

T2.PartNum
2
3

Using your technique:

SELECT T1.PartNum, T2.PartNum
FROM T1, T1
WHERE T1.PartNum = T2.PartNum

will get:

T1.PartNum T2.PartNum
2 2
3 3

But when you use a JOIN you can set it up to give you this result (INNER
JOIN)

SELECT T1.PartNum, T2.PartNum
FROM T1 INNER JOIN T2 ON T1.PartNum = T2.PartNum

but you also have the option of setting it up to return all the values from
one table, and only those that match from the other:

SELECT T1.PartNum, T2.PartNum
FROM T1 LEFT JOIN T2 ON T1.PartNum = T2.PartNum

which would return:

T1.PartNum T2.PartNum
1
2 2
3 3
4

The challenge of using Left Joins is to make sure you join the tables in the
correct sequence, to get exactly the results you want.

The syntax of this can get a bit confusing if you are trying to write it
directly in the SQL view of the query, so I recommend that you add the joins
in the query design view.
a. Add your tables to the query design view.
b. Click and drag from the field in one table to the matching field in the
other table.
c. Right click on the line that was created and select the Join option that
you want to use.

Not knowing the role of each of the tables in this mix, I'm not exactly sure
what the query should look like. If you could explain the role of each table
in this query, I might be able to help you a bit more. Could you please
confirm the following:

-tPOIn: identifies specific purchase orders
-tPOItems: is a details table that indicates the individual items assigned
to each PO
-tPOOut: is this your record of POItems that have been shipped?
-tPODelivered: is the the record of POItems that have been delivered?
-tManu: I assume this is the table that defines the manufacturer for each of
the items in a PO.

 

[John Spencer]

You need to change your query so you are using joins instead of a where
clause. Then you can use a left join to the tDelivered table. Your SQL would
probably look something like the following.

SELECT tPOItems.PartNumID AS PID, Manu, POOutNum, PartNum, Quantity,
Sum(DQuantity) As Delivered, DueDate, tPOItems.DeliveryDate, SpecialInst
FROM (((tPOin
INNER JOIN tPOItems ON tPOin.ID=tPOItems.POinID)
INNER JOIN tPOOut ON tPOOut.ID=tPOItems.POOutID)
INNER JOIN TManu ON tPOOut.ManuID=tManu.ID)
LEFT JOIN tDelivered ON tDelivered.PartNumID = tPOItems.PartNumID
AND tPOitems.ID = tDelivered.POItemsID
WHERE POinNum=Forms!fReport!POinNumDelivered
Group by tPOItems.PartNumID, Manu, POOutNum, PartNum, Quantity, DueDate,
tPOItems.DeliveryDate, SpecialInst


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi all,
I have 2 queries. I comebined them but some records are missing. The
situation is as follow:

 

[Dale Fye]

John,

As long as I've been writing SQL, I've always had problems with the way that
Access forces us to put ( ) in the joins when more than two tables are
involved.

I've never seen the joins formatted quite the way you did in this example,
which is entirely readable.

 

[John Spencer]

Blush. Thanks for words.

I wish that MS would fix the SQL editor so that it wouldn't change the
formatting and would allow comments in the SQL. I've asked, suggested, and
pleaded for this but it is rather low on the list of items they look at for
fixes/enhancements. Most of the users work in the query design view, so ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Aldred@office]

Yes Dale, the solution involves with Joins.

Thanks.

 

[Aldred@office]

John,
Your query works very well. Thank you. I didn't know that I could Inner
Join and left join used altogether in a single query.

Thanks.