Still can't get quantiy on hand for all records

[nightangel73]

Okay I have this tables that look like this



Lots
Lot Number
QuantityIN

Retain Orders
OrderID
Requested by

OrderIDDetails
Lot Number
RequestID
Quantity Withdrawn
Quanity Returned

So I build a query that gives me quantity on hand for all the lots where there was an order of withdrawn. But if there is no orders then quantity on hand is blank in the query because there is no records. Now I would like the query to show me that the quantity on hand for the lots with no orders to be the Quantity in.

Is this possible to do in a simple way? Help appreciated.

[lfpm062010]

If you use the an outter join (left or right join), it will give you an NULL for the field that does not have matching key value.

[nightangel73]

If you use the an outer join (left or right join), it will give you an NULL for the field that does not have matching key value.

I don't know how to do this.

[lfpm062010]

For example, if you left join Table "Lots" and Table "OrderIDDetails". If there is Lot Number that is not in table "OrderIDDetails", then the NULL will be filled in for those fields in "OrderIDDetails".

SELECT Lots.[Lot Number], Lots.QuantityIN, OrderIDDetails.[Quantity Withdrawn], OrderIDDetails.[Quanity Returned]
FROM Lots LEFT JOIN OrderIDDetails ON Lots.[Lot Number] = OrderIDDetails.[Lot Number];

[nightangel73]

I tried editing the join type with all possible ways and it will not give me the null values. If no withdrawn it still blank fields.

[nightangel73]

I tried editing the join type with all possible ways and it will not give me the null values. If no withdrawn it still blank fields.

The left join is pulling the records correctly it just not putting the null value. Why is this?

[lfpm062010]

The null value will only show if table "OrderIDDetails" does not have the lot number.

For example:
LOTS:
Lot Number-----QuantityIN
=======-----=======
1234 100
5678 200
9012 10

OrderIDDetails
Lot Number---RequestID---Quantity Withdrawn---Quanity Returned
=======---=======---============---===========
1234 1 50 25
4567 2 20 10

In the above case, the Lot Number 9012 does not have matching key in Table "OrderIDDetails". Then the NULL value will be filled in for RequestID, Quantity Withdrawn, and Quanity Returned.

If you still have problem, can you post your sample data and your SQL. If possible, post your sample Access database will be even better.