3

I currently have a query in MS Access named Quarterly_Growth_Rates which produces the table below:

Ticker  Year    Qtr Qtr_Growth  
AAPL    2013    3   21.46  
AMZN    2013    3   12.59  
BBBY    2013    3   4.11  
GOOG    2013    3   0.04  
V       2013    3   5.13  
AAPL    2013    2   -10.27  
AMZN    2013    2   4.01  
BBBY    2013    2   10.98  
GOOG    2013    2   10.74  
V       2013    2   7.66  
AAPL    2013    1   -20.07  
AMZN    2013    1   4.07  
BBBY    2013    1   14  
GOOG    2013    1   10.39  
V       2013    1   10.17

I need to create a CrossTab Query in my VB.net program that will produce this table:

Ticker  2013-3  2013-2  2013-1  
AAPL    21.46   -10.27  -20.07  
AMZN    12.59   4.01    4.07  
BBBY    4.11    10.98   14  
GOOG    0.04    10.74   10.39  
V       5.13    7.66    10.17

So right now the table shows columns: Ticker, Year, Qtr, and Qtr_Growth a row for every quarter for every year of every ticker.

I need it to show columns Ticker, 2013-1, 2012-4, 2012-3 and only one row for every ticker.

So far I have this code in my program:

Dim cmd4 As OleDbCommand = New OleDbCommand("SELECT Ticker FROM Quarterly_Growth_Rates GROUP BY Ticker PIVOT Qtr ", Nordeen_Investing_3.con)
cmd4.ExecuteNonQuery()

What is the correct SQL statement for this MS Access CrossTab query?

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
gromit1
  • 577
  • 2
  • 14
  • 36
  • 2
    Use the Query Designer to create the query that does what you want, and then capture the resulting SQL in the SQL view. – Robert Harvey Sep 25 '13 at 18:18
  • @RobertHarvey That is an option but I don't have access to Microsoft Access right now so I'd like to be able to get this done from within my program. – gromit1 Sep 25 '13 at 18:20

1 Answers1

3

Use a cross tab query so you don't need to "hard-wire" Year and Qtr values in your Access SQL.

Also I think ExecuteNonQuery is the wrong method to retrieve data from a SELECT query. But, that's on the VB.Net side of this task, and I can only help you with Access SQL.

I stored the sample data from your query source in a table named YourQuery.

In Access 2007, the first query below gave me this result set:

Ticker  2013-1  2013-2  2013-3
AAPL    -20.07  -10.27   21.46
AMZN      4.07    4.01   12.59
BBBY     14      10.98    4.11
GOOG     10.39   10.74    0.04
V        10.17    7.66    5.13

It differs from your requested output only in the column order. If you want columns in descending time period order, use the second query.

Note also my data source is a table, but yours is a query. I mentioned that difference because it's possible there might a more direct way to get what you need by starting from your query's source table(s) when building the cross tab. 

TRANSFORM First(y.Qtr_Growth) AS FirstOfQtr_Growth
SELECT y.Ticker
FROM YourQuery AS y
GROUP BY y.Ticker
PIVOT y.Year & '-' & y.Qtr;

TRANSFORM First(y.Qtr_Growth) AS FirstOfQtr_Growth
SELECT y.Ticker
FROM YourQuery AS y
GROUP BY y.Ticker
ORDER BY y.Year & '-' & y.Qtr DESC 
PIVOT y.Year & '-' & y.Qtr;
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • This looks great and it runs without an error. But since it doesn't have a name. How do I call it from my program to add it to a DataGridView so that I can see it? – gromit1 Sep 25 '13 at 20:30
  • 1
    You had some other `SELECT` statement before. Use the SQL from one of these 2 cross tab queries where you were previously using something like `New OleDbCommand("SELECT ...` So make it `New OleDbCommand("TRANSFORM ...` But I don't know how to write VB.Net stuff --- I can only help with the Access part. – HansUp Sep 25 '13 at 20:34
  • 1
    I've got it figured out. I'll test it and come back and mark your answer! Thanks! – gromit1 Sep 25 '13 at 20:38