Using column numbers instead of column names in query

[mmmbl1962]

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






Any help is greatly appreciated and thank you

[Welshgasman]

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

[NoellaG]

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.

[mmmbl1962]

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

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

[NoellaG]

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.

[Welshgasman]

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.

[June7]

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.

[CJ_London]

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

[Welshgasman]

Have a read of this
http://allenbrowne.com/casu-23.html

[madpiet]

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).

[mmmbl1962]

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.

[NoellaG]

I got it to work..

Congratz, you found the most slow and inefficient way to work with access.

[mmmbl1962]

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.

[NoellaG]

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.

[Micron]

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.