SQL Problem with Grouping and Left/Right-Joins

[Kumba1]

I have a report that I use to tell me locations of items... It relates the sales table to items I have in production... when I run the following SQL, I get a "No Current Record" error... if I disable the grouping, I get the correct output, but without a history table returning the last record entered (tblSalesContact)... Any ideas?

SELECT tblSales.OrderNumber, tblSales.CreationDate, tblSalesParts.TrackingNumber, tblSalesParts.releasedate, tblSalesParts.ReleasedTo, tblBuilds.BuildCode, tblSalesParts.TrackingNumber, tblBuilds.Location, tblBuilds.BuildDate, tblBuilds.Built, Last(tblSalesContact.ContactDate) AS LastOfContactDate

FROM (tblSalesContact RIGHT JOIN tblSales ON tblSalesContact.tblSalesPrimaryKey = tblSales.Index) INNER JOIN (tblSalesParts LEFT JOIN tblBuilds ON tblSalesParts.TrackingNumber = tblBuilds.TrackingNumber) ON tblSales.Index = tblSalesParts.SalesIndex
GROUP BY tblSales.OrderNumber, tblSales.CreationDate, tblSalesParts.TrackingNumber, tblSalesParts.releasedate, tblSalesParts.ReleasedTo, tblBuilds.BuildCode, tblSalesParts.TrackingNumber, tblBuilds.Location, tblBuilds.BuildDate, tblBuilds.Built

HAVING (((tblSalesParts.ReleasedTo)="Hold&quot)

ORDER BY tblSales.OrderNumber, tblSalesParts.TrackingNumber;

I also tried disabling grouping, and did "LastOfContactDate: Last(ContactDate), but that told me I didn't include a field as a part of an aggregate function... basically, I'm trying to return all fields from tblSales, and tblSalesParts... and the few related fields, if they exist, from tblBuilds and tblSalesContact... the problem is, tblSalesContact contains historical contact information, and I only want the Last (Newest) entry to return... any ideas?

 

[Morgance]

SELECT tblSales.OrderNumber, tblSales.CreationDate, tblSalesParts.TrackingNumber, tblSalesParts.releasedate, tblSalesParts.ReleasedTo, tblBuilds.BuildCode, tblSalesParts.TrackingNumber, tblBuilds.Location, tblBuilds.BuildDate, tblBuilds.Built, Max(tblSalesContact.ContactDate) AS LastOfContactDate

FROM tblSales
INNER JOIN tblSalesParts
ON tblSales.Index = tblSalesParts.SalesIndex
Left Join tblSalesContact
ON tblSalesContact.tblSalesPrimaryKey = tblSales.Index
LEFT JOIN tblBuilds
ON tblSalesParts.TrackingNumber = tblBuilds.TrackingNumber
where tblSalesParts.ReleasedTo="Hold"
GROUP BY tblSales.OrderNumber, tblSales.CreationDate, tblSalesParts.TrackingNumber, tblSalesParts.releasedate, tblSalesParts.ReleasedTo, tblBuilds.BuildCode, tblSalesParts.TrackingNumber, tblBuilds.Location, tblBuilds.BuildDate, tblBuilds.Built
ORDER BY tblSales.OrderNumber, tblSalesParts.TrackingNumber;


Everything from tblSales and TblSalesPart
Left join = Matches if they exist from tblBuilds and tblsalescontact

Advise on Having - Having is basically a where after the totals and grouping are done. Havings are rarely required and don't use them unless you really understand grouping, otherwise you get wrong results and you won't even know it.

Morgance