Another Query problem

remove the comma's from the "from" statements, and each table only needs to be defined once...

Select a.ExternalWebServiceID as Job_Ref,
Product.OurCode as Report_Type,
plan.PointEastings as Site_Easting,
plan.PointNorthing as Site_Northing,
CaseFile.PlacedDate as Report_Date,
'\\blah\blah\' + a.ExternalWebServiceID + 'sxw' as FilePath

from

Searches a
join Product on Searches.ProductID = Product.ProductID
join CaseFile on a.CaseFileId = CaseFile.CaseFileID
join Plan on CaseFile.PlanId = Plan.PlandID

Currently, a record in the searches table that does not have a corresponding record in product (with same productid) will not be returned, as inner joins are being used. If you want to return all records from searches regardless of whether product information exists, then you need to use a left join.

Quote:

Originally Posted by neil-j

I need to retrieve data from three tables and every syntax error I solve seems to cause several more issues! (I am a basic user of queries)

Please could anyone read what I have done below and suggest a way of getting this to work? 'Searches' contains Job_Refs, and joins to 'Product' to identify the name of the product being requested. 'Searches' needs to join to 'Plan' via CaseFile which holds the PlanID number. ('Searches' therefore must also join CaseFile on CaseFileID

Select a.ExternalWebServiceID as Job_Ref,
Product.OurCode as Report_Type,
plan.PointEastings as Site_Easting,
plan.PointNorthing as Site_Northing,
CaseFile.PlacedDate as Report_Date,
'\\blah\blah\' + b.ExternalWebServiceID + 'sxw' as FilePath

from

Searches a
join Product on Searches.ProductID = Product.ProductID,
Searches b
join CaseFile on Searches.CaseFileId = CaseFile.CaseFileID,
Casefile join Plan on CaseFile.PlanId = Plan.PlandID