Personally, I use inverted an Y-axis whenever it's about absolute movement relative to the game world, i.e. for controlling the pitch of vehicles like airplanes, helicopters or cars. Pressing down raises the nose and lowers the tail.
For everything where it's about aiming with a crosshair or cursor, I use controls relative to my computer monitor, to have the same controls you have with a mouse cursor on a flat surface.
In vehicular combat games where a weapon is mounted on the vehicle, I prefer the former control, but if the weapon can be moved independently from the vehicle, I prefer the latter control scheme.
