Present output of the below SQL query is :
Time | SumValue(CE+PE)
2 columns – ensuring the number of output is exactly ten.
How can I transpose it to :
Time | CE1Value | CE2Value |... |CE5Value | PE1Value | ... PE5Value
11 columns.
Ensuring that there are all the ten records for the same specific time , else skip to the next minute (next record). I am assuming that it is sorted by time,by default else can I use order by – is it optimum.
Using a standalone vbs file in windows against data in MS Access.
mySQL = "SELECT [Data.Time],"& Quantity &"*Sum([Data.Price]) AS SumOfPrice FROM Data WHERE ( ( [Data.Ticker] Like '"& CE1 &"' Or [Data.Ticker] Like '"& CE2 &"' Or [Data.Ticker] Like '"& CE3 &"' Or [Data.Ticker] Like '"& CE4 &"' Or [Data.Ticker] Like '"& CE5 &"' Or [Data.Ticker] Like '"& PE1 &"' Or [Data.Ticker] Like '"& PE2 &"' Or [Data.Ticker] Like '"& PE3 &"' Or [Data.Ticker] Like '"& PE4 &"' Or [Data.Ticker] Like '"& PE5 &"' ) AND ([Data.DateTr]=#"& DateIn &"#)) GROUP BY [Data.Time] HAVING [Data.Time] > #"& startTime(i) &"# and (((Count([Data.Ticker]))=10))"
Eg. For understanding : The data table is :
DateTr|Ticker|Time|Price 22July2016|Apple|3:20 AM|15 22July2016|Apple|3:21 AM|16 22July2016|Apple|3:23 AM|14 22July2016|Amazon|3:20 AM|16 22July2016|Amazon|3:21 AM|14 22July2016|Amazon|3:22 AM|14 22July2016|Amazon|3:23 AM|14
Eg. Apple data is for 3:22 , so skip and move onto next. But ensure for the same time – the count is two (in this case Apple+Amazon) ten in my case. CE1 = Apple , PE1 = Amazon Earlier Output :
3:20|31 3:21|30 3:23|28
New Output required :
3:20|15|16 3:21|16|14 so on.
Ticker is matched with CE1 , CE2 , PE2 etc.
Thanks
Advertisement
Answer
If data resides in MS Access, consider the crosstab query which you can explicitly specify the 10 columns with PIVOT clause:
TRANSFORM SUM(t.Price) As SumPrice
SELECT t.[Time]
FROM mytable t
GROUP BY t.[Time]
PIVOT t.ticker IN ('Apple', 'Cisco', 'Oracle', 'Microsoft', 'IBM',
'Amazon', 'Alphabet', 'Intel', 'Adobe', 'SAP')
Alternatively, run the conditional aggregation, again explicitly specifying the 10 columns with 10 expressions. This approach allows the flexibility of renaming columns such as suffixing _Value.
SELECT t.Time,
SUM(IIF(t.ticker = 'Apple', t.Price, NULL)) AS CE1_Value,
SUM(IIF(t.ticker = 'Cisco', t.Price, NULL)) AS CE2_Value,
SUM(IIF(t.ticker = 'Oracle', t.Price, NULL)) AS CE3_Value,
...
SUM(IIF(t.ticker = 'Amazon', t.Price, NULL)) AS PE1_Value,
SUM(IIF(t.ticker = 'Alphabet', t.Price, NULL)) AS PE2_Value,
SUM(IIF(t.ticker = 'Intel', t.Price, NULL)) AS PE3_Value,
...
FROM mytable t
GROUP BY t.Time
Should values need to be passed into query, as I recommended in your last question consider ADO parameterization. Alternatively, with DAO (Access’ default API) you can save a prepared statement as an Access query object using PARAMETERS clause and bind values in code using QueryDefs at runtime. Both approaches avoid messy concatenation and punctuation as seen in your mysql variable.
DAO
SQL (save as an Access query; no quotes or concatenation)
PARAMETERS [DateIn] Date, [Qty_Param] Long,
[CE1_Param] Text, [CE2_Param] Text, [CE3_Param] Text, [CE4_Param] Text, [CE5_Param] Text,
[PE1_Param] Text, [PE2_Param] Text, [PE3_Param] Text, [PE4_Param] Text, [PE5_Param] Text;
SELECT t.DateTr, t.[Time],
[Qty_Param] * SUM(IIF(t.ticker = [CE1_Param], t.Price, NULL)) AS CE1_Value,
[Qty_Param] * SUM(IIF(t.ticker = [CE2_Param], t.Price, NULL)) AS CE2_Value,
[Qty_Param] * SUM(IIF(t.ticker = [CE3_Param], t.Price, NULL)) AS CE3_Value,
...
[Qty_Param] * SUM(IIF(t.ticker = [PE1_Param], t.Price, NULL)) AS PE1_Value,
[Qty_Param] * SUM(IIF(t.ticker = [PE2_Param], t.Price, NULL)) AS PE2_Value,
[Qty_Param] * SUM(IIF(t.ticker = [PE3_Param], t.Price, NULL)) AS PE3_Value,
...
FROM mytable t
WHERE t.DateTr = [DateIn])
GROUP BY t.DateTr, t.[Time]
VBA
Dim db As DAO.Database, rst As DAO.Recordset, qdef As DAO.QueryDef
Set db = CurrentDb
Set qdef = db.QueryDefs("mySavedQuery")
With qdef
.Parameters("DateIn") = CDate("2016-07-22")
.Parameters("Qty_Param") = ###
.Parameters("CE1_Param") = "Apple"
.Parameters("CE2_Param") = "..."
.Parameters("CE3_Param") = "..."
...
.Parameters("PE1_Param") = "Amazon"
.Parameters("PE2_Param") = "..."
.Parameters("PE3_Param") = "..."
...
End With
Set rst = qdef.OpenRecordset()
...
rst.Close: qdef.Close
Set rst = Nothing: Set qdef = Nothing: Set db = Nothing