I have 4 tables, say -

tblItems ID, Item, ClientID, ManufID, CatalogID
tblClients ID, ClientName
tblManufs ID, ManufName
tblCatalog ID, CatalogDesc

SELECT tblItems.Item, tblClients.ClientName,
tblManuf.ManufName, tblCatalog.CatalogDesc FROM
(((tblItems INNER JOIN tblClients ON
tblItems.ClientID = tblClients.ID) INNER JOIN
tblItems.ManufID=tblManufs.ID))
INNER JOIN tblItems.CatalogID=tblCatalog.ID)
ORDER BY Item

When I use a select query to return a recordset based on tblItems
which will return the various text fields identified by their ID,
everything works UNLESS any of the ID's are zero. How do I get
ALL the records in tblItems returned, irrespective
of whether some or all of ClientID, ManufID, CatalogID have
been assigned (eg ID > 0) or have
not yet been assigned (eg ID=0 or Null)

Many thanks for any help you can give

Ken Ashton

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On Mar 5, 8:20*pm, Ken Ashton <kash... (AT) terra (DOT) es> wrote:
Try outer join.

SELECT tblItems.Item, tblClients.ClientName,
tblManuf.ManufName, tblCatalog.CatalogDesc
FROM tblItems
LEFT JOIN tblClients ON tblItems.ClientID = tblClients.ID
LEFT JOIN tblItems.ManufID = tblManufs.ID
LEFT JOIN tblItems.CatalogID = tblCatalog.ID
ORDER BY Item

Many thanks Toby, I'll try that. Its 2:30am here in Canary
Islands so I was very surprised with the rapid early
hours reply, and then saw the .AU - Thanks sport
Ken


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Sorry I didn't mention I was using MS Access SQL, got a few
errors trying your example above, but finally got there with

SELECT tblItems.Item, tblClients.ClientName,
tblManuf.ManufName, tblCatalog.CatalogDesc
FROM (((tblItems
LEFT JOIN tblClients ON tblItems.ClientID = tblClients.ID)
LEFT JOIN tblManufs ON tblItems.ManufID = tblManufs.ID)
LEFT JOIN tblCatalog ON tblItems.CatalogID = tblCatalog.ID)
ORDER BY Item

Seems to be working perfectly now, many thanks again for putting me on
track Toby

Ken Ashton



--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On Mar 5, 10:25*pm, Ken Ashton <kash... (AT) terra (DOT) es> wrote:
You're welcome.

--Toby