Results 1 to 12 of 12
  1. #1
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64

    Programmatically changing the source of a query based on user input


    I have 19 tables. Each table contains one year of data. All of them are formatted identically. I want to design a query based on these tables. The query will switch from table to table depending on user input. For instance, if the user selects "2014" (probably on a form control), the query will run from the 2014 table. Is this something that can be done?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,770
    You should have 1 table with a field for date value and apply filter to that field.

    What you ask for can be done but would be simpler if you had 1 table instead of 19.
    Last edited by June7; 12-08-2019 at 02:18 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,027
    I concur with June - especially if the 19 tables you have are online most/all of the time.
    As you have indicated the tables are identical, save for the Date/Year of records. So, a structural adjustment to add a YearOfRecords field would reduce your structure to 1 table.
    Good luck with your project.

  4. #4
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    Quote Originally Posted by June7 View Post
    You should have 1 table with a field for date value and apply filter to that field.

    What you ask for can be done but would be simpler if you had 1 table instead of 19.
    Quote Originally Posted by orange View Post
    I concur with June - especially if the 19 tables you have are online most/all of the time.
    As you have indicated the tables are identical, save for the Date/Year of records. So, a structural adjustment to add a YearOfRecords field would reduce your structure to 1 table.
    Good luck with your project.
    Thank you both. I should have specified that converting to one table is not an option. The tables are so big that I can't even upload all of them into access. Each table is 1 gb. So I have linked to all of them in the database. The files are CVS in their original form. I can't combine them in excel because they would exceed the row limit.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    For instance, if the user selects "2014" (probably on a form control), the query will run from the 2014 table. Is this something that can be done?
    Yes. You would probably want to use a combo that lists the tables. I would use a button rather than the combo AfterUpdate event because you don't want a query to run if they accidentally choose the wrong item from the list. In the button code, you'd substitute a variable with the list item value. That variable would be used (concatenated) in the sql statement so that if you were to debug.print it out for inspection, it would look like any regular sql statement but would reference whichever table name was chosen. As for the fields, I assume "formatted identically" means that in every table the field names are the same. You could either assign this sql statement to the recordsource of a form, or alter the SQL property of a stored query as a couple of options.

    Your button click code should handle the possibility that it gets clicked without a combo value being chosen, or you could open the form with it disabled, using the combo after update event to enable it.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    Ganymede is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2011
    Posts
    64
    Quote Originally Posted by Micron View Post
    Yes. You would probably want to use a combo that lists the tables. I would use a button rather than the combo AfterUpdate event because you don't want a query to run if they accidentally choose the wrong item from the list. In the button code, you'd substitute a variable with the list item value. That variable would be used (concatenated) in the sql statement so that if you were to debug.print it out for inspection, it would look like any regular sql statement but would reference whichever table name was chosen. As for the fields, I assume "formatted identically" means that in every table the field names are the same. You could either assign this sql statement to the recordsource of a form, or alter the SQL property of a stored query as a couple of options.

    Your button click code should handle the possibility that it gets clicked without a combo value being chosen, or you could open the form with it disabled, using the combo after update event to enable it.
    Thank you! I am currently planning to use a listbox. The query will run when they double click the selected table from the listbox. (I figure people are less likely to accidentally double click than single click.)

    I'm having a little difficulty following the SQL part. If I use VBA code to create a variable based on the selected table, I can put that variable in the SQL code in place of the table name?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    Yes, but you have to concatenate, otherwise the variable would be interpreted as a literal string. So it might look like
    Code:
    Dim strTable As String, sql As String
    
    strTable = item from listbox here, delimited with double quotes because the name property will be a string. If you need [] around it, have to add it in this example.
    
    sql = "SELECT field1, field2, field3.. FROM " & strTable & " WHERE ..."
    do something with sql
    where field# is the name of each needed field, which you imply are named the same in every table.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,027

  9. #9
    Ganymede is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2011
    Posts
    64
    Quote Originally Posted by Micron View Post
    Yes, but you have to concatenate, otherwise the variable would be interpreted as a literal string. So it might look like
    Code:
    Dim strTable As String, sql As String
    
    strTable = item from listbox here, delimited with double quotes because the name property will be a string. If you need [] around it, have to add it in this example.
    
    sql = "SELECT field1, field2, field3.. FROM " & strTable & " WHERE ..."
    do something with sql
    where field# is the name of each needed field, which you imply are named the same in every table.
    Thanks again! I believe I am following the code in the example. The one thing I'm struggling with is what you mean by concatenate in this context, and I want to make sure I understand it because you are saying it is a critical step. I understand generally how to concatenate strings but I'm not sure what there is to concatenate here because there will be only one string value (the value in the listbox). Maybe I'm in way over my head!

  10. #10
    Ganymede is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2011
    Posts
    64
    Quote Originally Posted by orange View Post
    Can you provide a few more details of the files and the application?
    Sure. Here's the context. For the past 19 years, the federal government has required certain entitles to file "reports" each year. The government publishes the data from these reports annually in tables: one table per year. All of the tables contain the following columns (in the same order): UniqueID, Worksheet (there are several worksheets in the reports and each has its own identifier), Line, Column, and Value. These tables are basically massive CSV documents. How massive you ask? The table for 2017 contains 19,338,455 rows, for example. This is because there are over 3,000 line/column combinations in each report and about 6,000 reporting entities.

    The data contained in these tables is very useful to my business because it helps us identify potential clients. I've been using this data for the past several years on my own. Basically, whenever I want to see a certain "Worksheet" from an entity's report for a given year, I load the relevant table for that year into access and run a crosstab query. That has worked to date, but it is very time-consuming and currently I'm the only one in my office who knows how to do this so people are constantly asking me to run queries for them. I want to design a database that will allow my colleagues to run these queries themselves. But I need to make it as simple as possible because my colleagues are not very familiar with how access works, which is why I'm the only one who currently runs these queries.

    So the final product will be an access database that is linked to the CSV tables. Those tables will be stored in a network folder. The database will open to a splash screen where my colleagues will enter the unique identifier for the entity that they want to look up. Then they will select the "Worksheet" and the year that they want to look up. I need to make it so that when they select the year, that will determine which of the 19 tables that the crosstab query looks at.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    I don't find that link particularly useful for your situation. Either try writing your version (whether or not you actually attempt to run it) and post your attempt for validation or see if you can choose the correct version below (where field# represents a field name).

    a) "SELECT field1, field2, field3 FROM strTableName ORDER BY field1 DESC;"
    b) "SELECT field1, field2, field3 FROM " & strTableName & " ORDER BY field1 DESC;"

    I advise to always assign the sql to a variable so that you can debug.print to the immediate window what the variable holds. If necessary you can copy/paste it to a new query to test. It's very easy make mistakes doing this, and leaving just one quote mark out is enough to mess up a statement.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,770
    Quote Originally Posted by Ganymede View Post
    I need to make it so that when they select the year, that will determine which of the 19 tables that the crosstab query looks at.
    Table and field names in query object cannot be dynamic (well, CROSSTAB column headers are exception). Requires VBA and QueryDefs collection to modify query structure.
    Code could change form or report RecordSource property SQL statement. So if you can design CROSSTAB to be stable (always same column headers), could base a form or report on that structure and use code to change RecordSource to reference selected table as source.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-10-2018, 07:22 AM
  2. Programmatically changing login credentials on ODBC connections
    By Glenn_Suggs in forum Import/Export Data
    Replies: 2
    Last Post: 07-11-2016, 10:51 AM
  3. Replies: 2
    Last Post: 12-17-2012, 03:46 PM
  4. Replies: 8
    Last Post: 03-16-2012, 11:04 AM
  5. Changing Query Source
    By EricF in forum Queries
    Replies: 8
    Last Post: 08-26-2011, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums