Hello, I can't figure out why I keep getting SYNTAX error for this query. Can someone help?
SELECT
HMS.[CountOfClaim Number],
HMS.[Other Payer Paid],
HMS.Net_Recovery
FROM
(SELECT "Med_Mth1" AS File,
Count(Recovery_Month1_Med.[Claim Number]) AS [CountOfClaim Number],
Format(Sum([Other Payer Amount Paid]*0.01), "Currency") AS [Other Payer Paid],
Format(Sum([Other Payer Amount Paid]-([Other Payer Amount Paid]*0.18))*0.01, "Currency") AS Net_Recovery
FROM Recovery_Month1_Med
union SELECT "Med_Mth2" AS File,
Count(Recovery_Month2_Med.[Claim Number]) AS [CountOfClaim Number],
Format(Sum([Other Payer Amount Paid]*0.01),"Currency") AS [Other Payer Paid],
Format(Sum([Other Payer Amount Paid]-([Other Payer Amount Paid]*0.18))*0.01,"Currency") AS Net_Recovery
FROM Recovery_Month2_Med
union SELECT "Med_Mth3" AS File,
Count(Recovery_Month3_Med.[Claim Number]) AS [CountOfClaim Number],
Format(Sum([Other Payer Amount Paid]*0.01),"Currency") AS [Other Payer Paid],
Format(Sum([Other Payer Amount Paid]-([Other Payer Amount Paid]*0.18))*0.01,"Currency") AS Net_Recovery
FROM Recovery_Month3_Med
union SELECT "RX_Mth1" AS File,
Count(Recovery_Month1_RX.[Claim Number]) AS [CountOfClaim Number],
Format(Sum([Other Payer Amount Paid]*0.01),"Currency") AS [Other Payer Paid],
Format(Sum([Other Payer Amount Paid]-([Other Payer Amount Paid]*0.18))*0.01,"Currency") AS Net_Recovery
FROM Recovery_Month1_RX
union SELECT "RX_Mth2" AS File,
Count(Recovery_Month2_RX.[Claim Number]) AS [CountOfClaim Number],
Format(Sum([Other Payer Amount Paid]*0.01),"Currency") AS [Other Payer Paid],
Format(Sum([Other Payer Amount Paid]-([Other Payer Amount Paid]*0.18))*0.01,"Currency") AS Net_Recovery
FROM Recovery_Month2_RX
UNION SELECT "RX_Mth3" AS File,
Count(Recovery_Month3_RX.[Claim Number]) AS [CountOfClaim Number],
Format(Sum([Other Payer Amount Paid]*0.01),"Currency") AS [Other Payer Paid],
Format(Sum([Other Payer Amount Paid]-([Other Payer Amount Paid]*0.18))*0.01,"Currency") AS Net_Recovery
FROM Recovery_Month3_RX
GROUP BY
[CountOfClaim Number]
[Other Payer Paid]
Net_Recovery)
AS HMS
LEFT OUTER JOIN
SELECT(
Sum(HMS.[CountOfClaim Number]) Count
Sum(HMS.[Other Payer Paid]) OtherPaid
Sum (HMS.Net_Recovery) Net
FROM HMS)