I'm trying to create a box total column in a table by counting distinct cases from multiple columns in the same table. I can do this properly when the box total column is in another table, but I can't figure out the self-join or whatever is required when it's in the same table.
SET BoxTotal =
(SELECT DISTINCT f_WellLocationID, AGS_ID, BOX_NUM, WH_LOCATION, f_SAMPLETYPEID
WHERE tblWhCoreSample.f_WellLocationID = tblWhCoreBoxTotal.f_WellLocID
AND tblWhCoreSample.AGS_ID = tblWhCoreBoxTotal.AGS_ID