So far, we've only seen what you want to insert into the temp
tables.  We need to also see what you want to pull out of them
in the end, and then we may be able to rewrite it in a form
that can be used as a view.

A VIEW is just a SELECT statement. You could use a multi-valued table
function, although I don't know what Access think of that.

You can probably make it without the temp tables though, by using derived
like below.

I would also question your use of DISTINCT. In my experience DISTINCT
is a keyword that you only occasionally have need for. If you find that
you need to use DISTINCT, it may be that you have insufficient join
conditions, or that you join against a subtable do an existennce, for
which you should use EXISTS instead. Or you are just putting it in to
cover your rear parts. However, DISTINCT calls for sorting operations, and
can be costly in performance.

Select Distinct
ins.DATE,
ins.ID,
ins.NAME ,
insIND,
ins.AMOUNT/100 as AmountIn,
outs.IND,
outs.AMOUNT/100 as AmountOut,
outs.NAME

From (Select Distinct
    BIV.DATE, BIV.ID, CA.NAME, BIV.IND, BIV.AMOUNT, BIV.UNITS, BIV.INAME,
MB.NO
    from Cars                               BIV
    LEFT JOIN MountainBikes                 MB
      ON MB.ID = BIV.ID
     AND MB.CLASS = BIV.CLASS
     AND MB.NUMBER = BIV.NUMBER
     AND MB.DATE = BIV.DATE
    LEFT JOIN  Caterpillars  CA
     ON CA.ID = MB.NO
    where BIV.CLASS = 'SWCH'
     and BIV.IND = 'IN'
     AND BIV.UNITS = 0
     AND BIV.AMOUNT <> 0) AS ins

Join (Select Distinct
     BIV.DATE, BIV.ID, CA.NAME, BIV.IND, BIV.AMOUNT, BIV.UNITS, BIV.NAME,
MB.NO
     from Cars  BIV
     LEFT JOIN Mountainbikes    MB
     ON MB.ID = BIV._ID
     AND MB.CLASS = BIV.CLASS
     AND MB.NUMBER = BIV.NUMBER
     AND MB.DATE = BIV.DATE
     LEFT JOIN Caterpillars   CA ON CA.ID = MB.NO
     where BIV.CLASS = 'SWCH'
     and BIV.IND = 'OUT'
     AND BIV.UNITS = 0
     AND BIV.AMOUNT <> 0) AS outs
ON OUTS.ID = INS.ID
where outs.NAME = ins.NAME
and outs.NO = ins.NO