Results 1 to 15 of 15
  1. #1
    mmmbl1962 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2024
    Posts
    4

    Using column numbers instead of column names in query

    I have a table with our store numbers vendor numbers as data for rows and beer brands as the headers. I also have a drop down list where I can select a beer brand. How can i create a table for the beer brand selected from the drop down list using the column number that belongs to that brand. For example Corona will be in column 1, Miller will be column 2, Budweiser column 3, etc. When I select Miller from the list i want to create a temporary table for just the vendors for our stores listed under Miller column? I tried using this Sql but i keep getting an error

    Dim varColno As Variant
    Dim mySQL As String

    varColno = Me.txtColumnNo 'text field in the form that looks for the column number for the brand selected

    mySQL = "SELECT [xls_Beer Vendors].[Store #], [Forms]![NEW STORES AND REMODELS]![txtBrandName] AS BRAND,[xls_BEER VENDORS].[column]("
    mySQL = mySQL & varColno
    mySQL = mySQL & ") as vendor INTO [_tmp Brand Vendor]FROM [xls_Beer Vendors];"

    DoCmd.RunSQL mySQL

    Screenshot of Brand selection
    Click image for larger version. 

Name:	FormShot.PNG 
Views:	43 
Size:	1.7 KB 
ID:	51820





    Any help is greatly appreciated and thank you

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    Stop right there and normalise your tables, else you will be banging your head against a brick wall for most eveything you are trying to do.
    Always say what the error is as well, we are not mind readers.

    Normalization
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,173
    Or if you want to work further the way you started, use Excel (names like xls_Beer Vendors suggest you already do so). With a macro in Excel you should be able to create new tables easily.

  4. #4
    mmmbl1962 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2024
    Posts
    4
    Quote Originally Posted by Welshgasman View Post
    Stop right there and normalise your tables, else you will be banging your head against a brick wall for most eveything you are trying to do.
    Always say what the error is as well, we are not mind readers.

    Normalization
    Click image for larger version. 

Name:	table.PNG 
Views:	37 
Size:	26.8 KB 
ID:	51821
    Thank you for the reply. As far as I can tell my table is normal because it has regular column headers. What I am trying to do is when I select a brand from the drop down it will create a temporary table from that column. If select Budweiser from the list then it will create a temporary table of all the vendors for each store in the Budweiser column. Then a query will run to compare the vendors that we currently have in our system to this temporary table to find out if the stores have the right vendor assigned for all the Budweiser brands. When i ran the query I get this message
    Click image for larger version. 

Name:	error.PNG 
Views:	37 
Size:	5.3 KB 
ID:	51822

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,173
    Hi, the table can look 'normal' to you, but it certainly isn't normalized. If you want to continue working with Access I suggest you read the link provided by Welshgasman, otherwise you'd better continue with Excel.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    Far from normal I am afraid unless you are an Excel user.
    Access and Excel are completely two different beasts and you have to think differently with each one.

    You are trying to use Access with an Excel mentality.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    And no, cannot use column numbers in SQL.

    I agree with other comments. Your data is not relational database normalized.

    However, could probably make it work if understood what you were trying to accomplish. By 'temporary table' do you mean a query? Can use VBA and QueryDefs to modify query object.

    Also, advise to not use spaces nor punctuation/special characters in naming convention, nor ALL UPPER CASE.
    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.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    Only way you can reference a column is using vba to loop through the recordset fields collection

    e.g.

    for I =0 to recordset.fields.count
    Debug.print recordset.fields(i).name
    Next I

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    madpiet is offline Expert
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    551
    I'd unpivot this mess in Excel using PowerQuery, so you end up with a simple table with the following columns (Store, State, Brand(?), and whatever the last column is (UPC?) Then you can import that into Access or whatever... And for the love of god, don't use Access to normalize this. It hasn't changed a bit since like 1996 when it comes to dealing with data transformations. (Well, if you like pain and creating recordsets to do stupid things, knock yourself out).

  11. #11
    mmmbl1962 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2024
    Posts
    4
    Quote Originally Posted by Welshgasman View Post
    Far from normal I am afraid unless you are an Excel user.
    Access and Excel are completely two different beasts and you have to think differently with each one.

    You are trying to use Access with an Excel mentality.
    I got it to work.. instead of using the column number I used the value from the dropdown box and concatenate "[" & "]" as the header

    VENDOR: DLookUp("[" & [Forms]![NEW STORES AND REMODELS]![txtBrandName] & "]","XLS_beer vendors","[xls_Beer Vendors]![Store #] =" & [Forms]![NEW STORES AND REMODELS]![txtStoreNo])

    I save the query and did a VBA to loop to all the stores and append them to a table.

  12. #12
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,173
    Quote Originally Posted by mmmbl1962 View Post
    I got it to work..
    Congratz, you found the most slow and inefficient way to work with access.

  13. #13
    mmmbl1962 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2024
    Posts
    4
    Quote Originally Posted by NoellaG View Post
    Congratz, you found the most slow and inefficient way to work with access.
    Since I am not an expert like you are so I was not sure what you meant most slow and inefficient way. The total process takes less than a minute to complete and gives me the result for all stores and flavors that we needed. To create the table for all stores took less than 5 seconds.

  14. #14
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,173
    Just a warning: every helper here told you to change your data structure. Using Access the way you do is like eating your soup with a fork . Eventually you will get there. With a correct structure you could do your task with a query and no programming in a sec. More important: future tasks will get more and more difficult and complex. So if you like to continue working with access I would advise you to invest some time in learning how a good data structure is build.

  15. #15
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    You might consider that fast, but at most it probably should take about 2 or 3 seconds. Even faster if you didn't do DLookups inside a query so there is one inefficiency that NoellaG might have been referring to. Just wait until you drop a beer brand or add a new one and see what kind of work that turns out to be because of your design. If designed properly there would be no re-working of tables/queries/reports/forms, but your approach will require the design of every one of those objects to be altered.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-29-2017, 10:01 AM
  2. Replies: 1
    Last Post: 11-12-2013, 02:19 AM
  3. How to look up column names using a query
    By debmil02 in forum Queries
    Replies: 6
    Last Post: 04-06-2012, 05:04 PM
  4. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 02:38 PM
  5. Query to get column names in a table
    By access in forum Forms
    Replies: 1
    Last Post: 07-01-2009, 02:50 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