I am trying to create an advanced query that requires sub-queries IN MS Access. I have managed to get the query to work but only if I input the specific WHERE and HAVING clause.
Here is the table I am using: Mock Trades Table
This is what the query looks like: My attempt at the query (I entered EURUSD)
SELECT Symbol,
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = [Input];) AS [Buy Lot],
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = [Input];) AS [Sell Lot],
(SELECT SUM([Lot Size]) FROM [Mock Trades] WHERE Symbol = [Input];) AS [Net Lots],
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Buy" AND Symbol = [Input];) AS [Buy Profits],
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE [Trade Type] = "Sell" AND Symbol = [Input];) AS [Sell Profits],
(SELECT SUM([Profit]) FROM [Mock Trades] WHERE Symbol = [Input];) AS [Net Profits]
FROM [Mock Trades]
GROUP BY Symbol
HAVING Symbol = [Input];
This makes the query only display the data for that specific input but I want it to show for every other possible input and have it calculate each column correctly. I tried implementing a GROUP BY in each embedded SELECT but it gave me an error.
I made a mock table to show what I want the query to look like: The ideal result
Any assistance would be greatly appreciated!
You can do it the following way:
SELECT Symbol,
(SELECT SUM([Lot Size]) FROM [Mock Trades] T2
WHERE T2.[Trade Type] = "Buy"
AND T2.Symbol = T1.symbol) AS [Buy Lot]
FROM [Mock Trades] t1
GROUP BY Symbol
note: I have removed the semicolon, you can place it back if the query actually runs like that.