help with querry

[Supicek]

Hi

I have three tables -
tbOrdes (orProduct, orDate, orQty),
tbProduction (prProduct, prDate, prQty),
tbExpedition (exProduct, exDate, exQty)

I want to display for each day selected on the form - lblDate a simple table
which would show products in rows and three columns showing qty ordered,
produced and sold (exQty from tbExpedition).

Querry which i have built works but only for days where I have some qty in
all three tables, hower if qty is entered i.e. only in tbOrder, the querry
does not show the product.

your advice is welcomed.

 

[Jerry Whittle]

You have an inner join between the tables so there must be a record in each
table to have a return.

In design view, double click on the black line between the tables until you
see a dialog box. It can be a little tricky. Select one of the other two
options and see what happens. You probably want all the records from the
tbOrder table. Since you have three tables, you might need to modify both
joins.

 

[Supicek]

well, I want even more, let's simplify the issue just to 2 tables (tbOrders
and tbProdution)

orProduct, orDate, orQty
17/1/2006

 

[Supicek]

Jerry, thanks for your reply, I tried to play with type of joins, but it did
not give me the answer I was looking for. I am not sure if the expected
output is available via SQL, but let's do another try and let's simplify the
issue into 2 tables
tbOrder
orProduct orDate orQty
1 7/1/2006 10
2 7/1/2006 20
2 7/2/2006 10

tbProduction
prProduct prDate prQty
4 7/3/2006 4
2 7/1/2006 20
2 7/3/2006 10

I expect result like this:

Date Product Ordered Produced
-------------------------------------------------------
7/1/2006 1 10
7/1/2006 2 20 20
7/2/2006 2 10
7/3/2006 2 10
7/3/2006 4 4

should I use a subquerry?