1

So there's a database from which I regularly pull data for 200 Quarters. The Quarters are named as column Q0r, Q1r, Q2r,.....,Q200r.

So my query goes as Select Q0r, Q1r, Q2r, Q3r....., Q199r, Q200r from testdata where [condition];

I was wondering if there's an easy way to rewrite this query so I don't have to literally do counting from 0 to 200 in the select statement.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
user6879637
  • 41
  • 1
  • 3
  • 2
    You should normalize your table into long format with **one** Quarter column and **one** value column where rows are for each quarter by observation (i.e., person, company, etc.). While MS Access has a hard 255 column limit for tables/queries it does not maintain a row-limit. – Parfait Sep 30 '19 at 19:28

2 Answers2

1

Create a function to build the SQL and write it to the query:

Public Function CreateQuery()

    Dim Query           As DAO.QueryDef
    Dim SqlMaster       As String
    Dim Sql             As String
    Dim Id              As Integer
    Dim Names(0 To 200) As String

    Set Query = CurrentDb.QueryDefs("QueryQuarters")

    SqlMaster = "Select {0} From YourTable"

    For Id = LBound(Names) To UBound(Names)
        Names(Id) = "Q" & CStr(Id) & "r"
    Next

    Sql = Replace(SqlMaster, "{0}", Join(Names, ","))
    Query.Sql = Sql

    Debug.Print Sql

End Function
Gustav
  • 53,498
  • 7
  • 29
  • 55
0

You can use an asterisk to select all columns:

SELECT *
FROM tablename
K753
  • 378
  • 1
  • 12
  • can't use * since the data goes for 400 quarters. – user6879637 Sep 30 '19 at 18:29
  • 1
    @user6879637 Why is there a column for each quarter? The quarter could be specified in a single column called "Quarter" and it would simplify things. Anyway, I think you would have to get all the column names from the schema and try to join them with your query. It's probably more complicated than what your doing now but look at the second answer [here](https://stackoverflow.com/questions/5274594/sql-select-with-column-name-like) for an example. – K753 Sep 30 '19 at 19:23