Count Total occurance in 2 concatenated tables

[43918084]

 I need to concatenate below Table 1 and Table 2, then add calculated field "Total TradeID", and "Status"

But I keep getting 'Invalid expression' Error.  Hope I can get some help.

Table1&2temp:

Load

Date(Date#([Reportdate], 'YYYYMMDD') ) AS [Reportdate],
 [TradeID],
 Date(Date#([MatDate], 'YYYYMMDD') ) AS [MatDate],
 Date(Date#([TradeDate], 'YYYYMMDD') ) AS [TradeDate],
 [Notional],
    Count(DISTINCT TradeID) As Total_TradeID 
 RESIDENT [Table1&2] Group by Period, Reportdate, MatDate,TradeDate;
DROP TABLE [Table1&2]; 

Table1
Report date	TradeID	MatDate	TradeDate	Notional		Total TradeID	Status
20191231	ex1	20201230	20181030	100		4	Exist
20191231	ex1	20201230	20181030	100		4	Exist
20191231	mat1	20191230	20181030	10		1	matured
20191231	Term1	20201230	20181030	1		1	Exist
Table2
Report date	TradeID	MatDate	TradeDate	Notional
20200131	new1	20201230	20200101	50		1	Exist
20200131	new2	20201230	20200101	2		1	Exist
20200131	ex1	20201230	20181030	100		4	Exist
20200131	ex1	20201230	20181030	100		4	Exist

[Vegar]

Table1&2temp:

Load

Date(Date#([Reportdate], 'YYYYMMDD') ) AS [Reportdate],
 [TradeID],
 Date(Date#([MatDate], 'YYYYMMDD') ) AS [MatDate],
 Date(Date#([TradeDate], 'YYYYMMDD') ) AS [TradeDate],
 [Notional]

 RESIDENT [Table1&2]; 

Left join Load TradeID, 

Count(TradeID) as TotalNoOfTradeIDs

Resident [Table1&2]

Group by TradeID; 

Drop table  [Table1&2];

[43918084]

Thank you so much.  This solution really helps. 

Sorry, I have one more question relating to the Status field.  The status field is a calculated field based on the

Field TotalNoOfTradeIDs.  I need to build formula like  " If( TotlaNoOfTradeIDs = 1 and MatDate >Reportdate, 'Exist', 'Mature).

May I know how I can add the 'STatus' field into the Table1&2?  Many thanks again