I have table JobCost_Hdr and ShopOrd_Hdr with Job# and Shop# the matching fields. JobCost has one record per Job but ShopOrd can have multiple records per Job. I am trying to pull records from Job and one field from the first matching record from Shop. I have tried putting a Select Top 1 sub query but continue to get a syntax error regardless of what I try.
This is the latest SQL try from Microsoft Query (Excel 2008).
SELECT JOBCST_HDR.ID_JOB_ODBC, JOBCST_HDR.NAME_CUST,
(SELECT TOP 1 SHPORD_HDR.ID_ITEM_PAR
FROM PUBLIC.SHPORD_HDR SHPORD_HDR
WHERE JOBCST_HDR.ID_JOB_ODBC=SUBSTRING(SHPORD_HDR.ID_SO_ODBC,4,6))
FROM PUBLIC.JOBCST_HDR JOBCST_HDR,
WHERE JOBCST_HDR.STAT_JOB='O'
Thak you in advance for any help you can offer.