However when I plug the coldcard Q directly into the computer (changing the USB port setting to allow connection) and create a 'Connected Hardware Wallet' wallet, it can't find the transactions. Likewise when I export the wallet json from the coldcard device and import it using 'Airgapped Hardware Wallet' I also do not see the transactions.
To what node Sparrow is connected, public or your own on? Did you check if the connection to that node is established before you plug in Coldcard?
After your coldcard is plugged in you should select "Import Keystore". Did you do this?
Check also if both Policy Type and Script Type
shown by Sparrow window is relevant to wallet you want ta access.
There should be no problem if everything done correctly.