Neil> This is what happens when you make up examples. No I meant that a Dept. has an additional team, not that it matters really. tblUnits are organizations not locations (I see the confusion now). I think you missed "
Both UnitFK and SubUnitFK have a relationship (1 to M) with UnitID"
no I didn't , just assumed you had that the wrong way round - unitID has a 1-M relationship with UnitFK and SubUnitFK
Neil> MS Access will not allow a relationship with itself (I have tried).
it does
TblName
TblNamePK
Desc
TblNameFK
Code:
SELECT *
FROM
TblName as P INNER JOIN tblName AS C ON P.TblNamePK=C.TblNamePK
it doesn't help that your field names imply a relationship along these lines which, together with your thread title, implies a recursive relationship