Join Help

[excalibur78]

(SELECT [Store #], [Datatec Posts BOM]
FROM [Masterfile]
WHERE [Datatec Posts BOM] >= '4/29/2003')

(SELECT [Masterfile].[Store #], [Masterfile].[Datatec Posts BOM], [Inventory Tracking].[Store #], [Inventory Tracking].[BOM Build Date], [Inventory Tracking].[DatatecSupp]
FROM [Masterfile], [Inventory Tracking]
WHERE [Masterfile].[Store #] = [Inventory Tracking].[Store #] and [Inventory Tracking].[BOM Build Date] Is Not Null
and [Masterfile].[Datatec Posts BOM] Is Not Null and [Inventory Tracking].[DatatecSupp] Is Null
and [Masterfile].[Datatec Posts BOM] >= '4/29/2003')

I'm trying to join these 2 record sets so that the fields from the second recordset join to the end of the first recordset even if they don't exist in the first recordset.
Having no luck I think I'm just over thinking things.

Thanks,

David

 

[SQLSister]

Try:
SELECT [Masterfile].[Store #], [Masterfile].[Datatec Posts BOM], [Inventory Tracking].[Store #], [Inventory Tracking].[BOM Build Date], [Inventory Tracking].[DatatecSupp]
FROM [Masterfile]Right Outer join [Inventory Tracking] on
[Masterfile].[Store #] = [Inventory Tracking].[Store #]
Where [Inventory Tracking].[BOM Build Date] Is Not Null
and [Masterfile].[Datatec Posts BOM] Is Not Null and [Inventory Tracking].[DatatecSupp] Is Null
and [Masterfile].[Datatec Posts BOM] >= '4/29/2003'

 

[mwolf00]

(SELECT [Masterfile].[Store #], [Masterfile].[Datatec Posts BOM], [Inventory Tracking].[Store #], [Inventory Tracking].[BOM Build Date], [Inventory Tracking].[DatatecSupp]
FROM [Masterfile], [Inventory Tracking]
WHERE [Masterfile].[Store #] =* [Inventory Tracking].[Store #] and [Inventory Tracking].[BOM Build Date] Is Not Null
and [Masterfile].[Datatec Posts BOM] Is Not Null and [Inventory Tracking].[DatatecSupp] Is Null
and [Masterfile].[Datatec Posts BOM] >= '4/29/2003')

to make an outer join you need to add a * to the side of the equation that you want "outer joined"....

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook