Board index » delphi » SQLServer SQL->Access SQL problem

SQLServer SQL->Access SQL problem

Need help in converting this SQL to wrok with Access tables of the same
name with the same schema.

SELECT  LSClaimUnit.*,
      IDPolicy.StateCode,
      IDCrop.CountyCode, IDCrop.CropCode, IDCrop.IPlanCode,
      SUM(IDAcreage.Acres) AS SumAcres

FROM  LSClaimUnit

INNER JOIN  IDCrop
  ON  IDCrop.RID = LSClaimUnit.IDCropRID

INNER JOIN  IDPolicy
  ON  IDPolicy.RID = IDCrop.IDPolicyRID

LEFT OUTER JOIN  IDAcreage
  ON  LSClaimUnit.IDCropRID = IDAcreage.IDCropRID
 AND  LSClaimUnit.UnitNum = IDAcreage.UnitNum

WHERE  (LSClaimUnit.IDPolicyRID = 123224)
GROUP BY  LSClaimUnit.RID, LSClaimUnit.IDPolicyRID,
          LSClaimUnit.LSClaimRID, LSClaimUnit.IDCropRID,
          LSClaimUnit.UnitNum, LSClaimUnit.ClaimUnitStatus,
          LSClaimUnit.DmgDate1, LSClaimUnit.DmgDate2, LSClaimUnit.DmgDate3,
          LSClaimUnit.DmgCause1, LSClaimUnit.DmgCause2, LSClaimUnit.DmgCause3,
          LSClaimUnit.CausePercent1, LSClaimUnit.CausePercent2,
          LSClaimUnit.CausePercent3, LSClaimUnit.InspectionDate,
          LSClaimUnit.ModDate, LSClaimUnit.ModType, LSClaimUnit.ModSYUserRID,
          LSClaimUnit.SimplifiedClaim, LSClaimUnit.SelfCertClaim,
          IDPolicy.StateCode, IDCrop.CountyCode, IDCrop.CropCode,
          IDCrop.IPlanCode
ORDER BY  IDPolicy.StateCode, IDCrop.CountyCode,
          IDCrop.CropCode, LSClaimUnit.UnitNum

This particular query returns 3 rows of data.

Access doesn't allow the LEFT OUTER JOIN...

Tried this but it didn't return any rows:

SELECT LSClaimUnit.*,
       IDPolicy.StateCode,
       IDCrop.CountyCode,
       IDCrop.CropCode,
       IDCrop.IPlanCode,
       SUM(IDAcreage.Acres) AS SumAcres
FROM   LSClaimUnit, IDCrop, IDPolicy, IDAcreage

WHERE LSClaimUnit.IDPolicyRID = 123224 AND
      IDCrop.RID = LSClaimUnit.IDCropRID AND
      IDPolicy.RID = LSClaimUnit.IDPolicyRID AND
      (LSClaimUnit.IDCropRID = IDAcreage.IDCropRID
       AND  LSClaimUnit.UnitNum = IDAcreage.UnitNum)

GROUP BY  LSClaimUnit.RID, LSClaimUnit.IDPolicyRID,
          LSClaimUnit.LSClaimRID, LSClaimUnit.IDCropRID,
          LSClaimUnit.UnitNum, LSClaimUnit.ClaimUnitStatus,
          LSClaimUnit.DmgDate1, LSClaimUnit.DmgDate2, LSClaimUnit.DmgDate3,
          LSClaimUnit.DmgCause1, LSClaimUnit.DmgCause2, LSClaimUnit.DmgCause3,
          LSClaimUnit.CausePercent1, LSClaimUnit.CausePercent2,
          LSClaimUnit.CausePercent3, LSClaimUnit.InspectionDate,
          LSClaimUnit.ModDate, LSClaimUnit.ModType, LSClaimUnit.ModSYUserRID,
          LSClaimUnit.SimplifiedClaim, LSClaimUnit.SelfCertClaim,
          IDPolicy.StateCode, IDCrop.CountyCode, IDCrop.CropCode,
          IDCrop.IPlanCode
ORDER BY  IDPolicy.StateCode, IDCrop.CountyCode,
          IDCrop.CropCode, LSClaimUnit.UnitNum

 

Re:SQLServer SQL->Access SQL problem


Quote
>This particular query returns 3 rows of data.
>Access doesn't allow the LEFT OUTER JOIN...

Access allows a Left Outer Join although if you create the query in Access Left
Outer Join is abbreviated as Left Join

Generally to get a complex SQL query to work with Access you need to put
parentheses around all the joins

FROM ( (LSClaimUnit INNER JOIN  IDCrop
  ON  IDCrop.RID = LSClaimUnit.IDCropRID)
 INNER JOIN  IDPolicy  ON  IDPolicy.RID = IDCrop.IDPolicyRID)

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQLServer SQL->Access SQL problem


In article <nb3rcvgpa4o7ib4mhrh6jjlp6dioeou...@4ax.com>, BBus...@Nmpls.com
says...

Quote

> >This particular query returns 3 rows of data.
> >Access doesn't allow the LEFT OUTER JOIN...

> Access allows a Left Outer Join although if you create the query in Access Left
> Outer Join is abbreviated as Left Join

> Generally to get a complex SQL query to work with Access you need to put
> parentheses around all the joins

> FROM ( (LSClaimUnit INNER JOIN  IDCrop
>   ON  IDCrop.RID = LSClaimUnit.IDCropRID)
>  INNER JOIN  IDPolicy  ON  IDPolicy.RID = IDCrop.IDPolicyRID)

Thanks, Brian, I finally figured that one out...

OK, I have a working query, somewhat...

SELECT  LSClaimUnit.RID, LSClaimUnit.IDPolicyRID,
        LSClaimUnit.LSClaimRID, LSClaimUnit.IDCropRID,
        LSClaimUnit.UnitNum, LSClaimUnit.ClaimUnitStatus,
        LSClaimUnit.DmgDate1, LSClaimUnit.DmgDate2, LSClaimUnit.DmgDate3,
        LSClaimUnit.DmgCause1, LSClaimUnit.DmgCause2, LSClaimUnit.DmgCause3,
        LSClaimUnit.CausePercent1, LSClaimUnit.CausePercent2,
        LSClaimUnit.CausePercent3, LSClaimUnit.InspectionDate,
        LSClaimUnit.ModDate, LSClaimUnit.ModType,LSClaimUnit.ModSYUserRID,
        LSClaimUnit.SimplifiedClaim, LSClaimUnit.SelfCertClaim,
        IDPolicy.StateCode, IDCrop.CountyCode, IDCrop.CropCode,
        IDCrop.IPlanCode, SUM(IDAcreage.Acres) AS SumAcres

FROM  (((LSClaimUnit
INNER JOIN  IDCrop
  ON  IDCrop.RID = LSClaimUnit.IDCropRID)
INNER JOIN  IDPolicy
  ON  IDPolicy.RID = IDCrop.IDPolicyRID)

LEFT OUTER JOIN  IDAcreage
  ON  (LSClaimUnit.IDCropRID = IDAcreage.IDCropRID
 AND  LSClaimUnit.UnitNum = IDAcreage.UnitNum))

WHERE  (LSClaimUnit.IDPolicyRID = 123224)

GROUP BY  LSClaimUnit.RID, LSClaimUnit.IDPolicyRID,
          LSClaimUnit.LSClaimRID, LSClaimUnit.IDCropRID,
          LSClaimUnit.UnitNum, LSClaimUnit.ClaimUnitStatus,
          LSClaimUnit.DmgDate1, LSClaimUnit.DmgDate2, LSClaimUnit.DmgDate3,
          LSClaimUnit.DmgCause1, LSClaimUnit.DmgCause2, LSClaimUnit.DmgCause3,
          LSClaimUnit.CausePercent1, LSClaimUnit.CausePercent2,
          LSClaimUnit.CausePercent3, LSClaimUnit.InspectionDate,
          LSClaimUnit.ModDate, LSClaimUnit.ModType,LSClaimUnit.ModSYUserRID,
          LSClaimUnit.SimplifiedClaim, LSClaimUnit.SelfCertClaim,
          IDPolicy.StateCode, IDCrop.CountyCode, IDCrop.CropCode,
          IDCrop.IPlanCode
ORDER BY  IDPolicy.StateCode, IDCrop.CountyCode,
          IDCrop.CropCode, LSClaimUnit.UnitNum

Only problem is...

If I run the query in Enterprise manager on my SQL Server tables
with the same schema and data, it returns 3 rows.

If I run it in Access, with the same data and schema, it only returns
one row.

Any ideas?

Re:SQLServer SQL->Access SQL problem


Quote
>If I run it in Access, with the same data and schema, it only returns
>one row.

>Any ideas?

No
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads