Please help with this quey

[Cabong]

This is surely simple, but I can't figure it out.
Basically here are the 3 tables in my query with their respective fields.
tblItems (ItemNo,Location)
tlbDescriptions (ItemNo, ItemDesc)
tblOrders (ItemNo, OrderQty)

I want to list all items with their related descriptions and list any orders associated with the items.

Here's what I have so far (in sql):

SELECT tblItems.ItemNo, tblItems.Location, tblDescription.ItemNo, tblDescription.ItemDesc
FROM tblDescriptions INNER JOIN tblItems ON tblDescriptions.ItemNo=tblItems.ItemNo
WHERE tblItems.Location = "1";

This seems to work fine, except that when I try to include the last table (tblorders) to find out which items have orders, it gives me only those items that have orders. What I want is all the items and see the ordered qty if there is for that item.

Now this is an external database so I can't change any of the tables and I have to do this in one query.

Can anyone help me on this?
TIA

 

[Uncle Gizmo]

Is "ItemNo" the same number in each table? What I mean is, you are storing the location in one table, the description of the item in another table and the order quantity in another table.

If that's the case, then you may benefit by pulling all this information into one table.

 

[mresann]

Actually, he should have two tables based on the information given. Of course it's still an oversimplification of a typical market transaction process, but the reasoning is as follows.

An ITEM can have ONE description.
An ITEM can have ONE location (at least, based on your own process)

MANY ORDERS can have MANY ITEMS

Therefore, you need three tables: an ITEM table, an ORDER table, and a table that JOINS the ITEM and ORDER tables.

ITEM table:
ITEMNo
Description
Location

ORDERS table
OrderNo
Customer (I added this)
Salesperson (I added this)

ITEM_ORDER_JOIN
ITEMNo (Foreign key to ITEM table) This table co-Primary key
OrderNo (Foreign key to ORDERS table) This table co-Primary key
ItemQuantity (number of items on this order)

Hope this helps you in the direction you want to go.

 

[Cabong]

You are right. I just noticed that I forgot an important detail.
I have to get a recordset of items that match a specific location. So I use the location table (which I call tblItems), as being the source. Then I lookup the description in the actual item table (that I call tlbDesc).

I'm sorry I thought it would make it easier to understand, my mistake.

My prlm was that I also have to look up if the item has been ordered.
Here's how I was suggested to do it:

SELECT tblItems.ItemNo, tblDescriptions.ItemDesc, tblOrders.OrderQty FROM (tblDescriptions INNER JOIN tblItems ON tblDescriptions.ItemNo=tblItems.ItemNo) LEFT JOIN tblOrders ON tblOrders.ItemNo = tblItems.ItemNo;

It seems to work but access can't display the relationship? Weird.

Thanks all the same