SYNTAX ERROR Summing Columns in Access SQL

[pnpez]

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)

[ranman256]

separate each 'block' into its own query, test it, then the query design will tell you the error.
the final union query will be:

select * from Q1
union

select * from Q2
union

select * from Q3

[pnpez]

Thanks ranman, the UNION queries work, but I get a "SYNTAX error in FROM CLAUSE", and it points to the last 'Select':
SELECT(
Sum(HMS.[CountOfClaim Number]) Count
Sum(HMS.[Other Payer Paid]) OtherPaid
Sum(HMS.Net_Recovery) Net


FROM HMS)

[Minty]

Have you tried running this as a separate query

SELECT(
Sum(HMS.[CountOfClaim Number]) Count
Sum(HMS.[Other Payer Paid]) OtherPaid
Sum (HMS.Net_Recovery) Net


FROM HMS)

Because it looks completely malformed and shouldn't work, there is no alias for the any of the fields and you aren't selecting anything from anything?

[Welshgasman]

If your DB was correctly structured, you would not get these problems?

[ssanfu]

My observations:


-- Code should be enclosed with code tags (the # on the quick reply tool bar).

-- Be aware that "File" is a reserved word in Access. "Count" is also a reserved word AND a built in function.

-- Should not use spaces in object names.

-- Your naming convention is terrible. Is "Recovery_Month3_Med" a table or a query? Better if you had "tblRecovery_Month3_Med" or "qryRecovery_Month3_Med".
So you have 24 tables?
Recovery_Month1_Med through Recovery_Month12_Med
Recovery_Month1_RX through Recovery_Month12_RX

A normalized table structure would be one table for each:
Recovery_Month_Med
Recovery_Month_RX

then filter by dates to get quarterly results.


-- It looks like there are missing commas or you didn't type them in if you manually entered the code

GROUP BY 
[CountOfClaim Number]
[Other Payer Paid]
Net_Recovery)

LEFT OUTER JOIN


SELECT(
 Sum(HMS.[CountOfClaim Number]) Count
 Sum(HMS.[Other Payer Paid]) OtherPaid
 Sum(HMS.Net_Recovery) Net


 FROM HMS)