UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Selecting Data Table Columns Based On Column Location Instead Of Column Names, Any Version    
 
   
rashokku
post Nov 28 2017, 01:50 PM
Post#1



Posts: 3
Joined: 27-November 17



I have a table name "Patient" in MS Access and the table has following columns.

Member_ID (First Column)
Member_Name (Second Column)
Member_DOB (Third Column)
Member_Address (Fourth Column)
Member Zip (Fifth Column)

I would like to select the columns based on the column location/number/sequence than the column name.

What is the syntax in SQL to locate the column location?

Thanks for your help,
Ram
Go to the top of the page
 
DanielPineault
post Nov 28 2017, 01:56 PM
Post#2


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



I've never seen such syntax in SQL (it can be done in VBA though). Might I ask why since you know the name of the fields?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
River59
post Nov 28 2017, 02:10 PM
Post#3



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


I agree with Daniel in my curiosity as to why you are selecting column numbers rather than column names. This can be done if you can write the code to do it. This is the idea of what needs to be done but you will have to modify it to do whatever it is that you are attempting to do. The following code was used to pass the column names to the corresponding column in Excel. Only shown here so you can see how to reference the column numbers on your table.

Dim numOfCols As Integer
numOfCols = pPullListRs.Fields.count
Dim currentCol As Integer
Dim currentRow As Integer
currentRow = 1
For currentCol = 1 To numOfCols
currentSheet.Cells(currentRow, currentCol).Value = pPullListRs.Fields(currentCol - 1).Name
Next
Do Until pPullListRs.EOF
currentRow = currentRow + 1
For currentCol = 1 To numOfCols
currentSheet.Cells(currentRow, currentCol).Value = pPullListRs.Fields(currentCol - 1).Value
Next
pPullListRs.MoveNext
Loop

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
rashokku
post Nov 28 2017, 02:33 PM
Post#4



Posts: 3
Joined: 27-November 17



We have health plan data file that need to be imported in to MS Access table. The column header names keeping changing or not constant/fixed
but the column location/sequence/order is fixed. So, instead of selecting/reading the columns by column names/header names, I would like
to read the columns by column number/column locations and import it into exsiting table or drop existing table & create new table.

Please see the attachment for example.
Attached File(s)
Attached File  11_28_2017_11_24_41_AM.png ( 8.68K )Number of downloads: 0
 
Go to the top of the page
 
Jeff B.
post Nov 28 2017, 02:56 PM
Post#5


UtterAccess VIP
Posts: 9,884
Joined: 30-April 10
From: Pacific NorthWet


So, are you saying that you can be 100% certain that the third column will always contain, say, DOB? Where is the data coming from? Is there a chance you could use Access to "see" the data where it lives, and use a query to reformat the raw data for your purposes? What are your intentions? (that is, what will having the data in Access then allow you to do?) A potential concern is that the original data source could change, and the data therein could change. Is your copy of the data you imported into Access the correct version, or ...?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
RJD
post Nov 28 2017, 03:11 PM
Post#6


UtterAccess VIP
Posts: 7,816
Joined: 25-October 10
From: Gulf South USA


Hi: Just a thought ...

Import the file with whatever field names it comes in with - in a temporary table (the same table name each time, say "ImportRecords"). Since there will always be five fields, and always in the same order (I gather that from your discussion), just change the field names in the temporary table ...

CODE
Public Sub ChangeFieldNames()

CurrentDb.TableDefs("ImportRecords").Fields(0).Name = "Member_ID"
CurrentDb.TableDefs("ImportRecords").Fields(1).Name = "Member_Name"
CurrentDb.TableDefs("ImportRecords").Fields(2).Name = "Member_DOB"
CurrentDb.TableDefs("ImportRecords").Fields(3).Name = "Member_Address"
CurrentDb.TableDefs("ImportRecords").Fields(4).Name = "Member_Zip"

End Sub

Then you can import these records into the permanent table and delete the temporary table.

You should be able to automate all these steps and simplify your operation.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
River59
post Nov 28 2017, 03:21 PM
Post#7



Posts: 1,345
Joined: 7-April 10
From: Detroit, MI


Another solution would be to open the Excel file and change the column names to match the table names. If they really will always be in the same order. A1 = NameOne, A2 = NameTwo, etc. Save and close the file, then a standard import spec to bring in the changed Excel file. All of these suggestions require that you write some code to accomplish. The solution will depend on your knowledge of writing simple code.

CODE
Public Function TestImportColNames()

   Dim appExcel As Excel.Application
   Dim fDialog As Office.FileDialog
   Dim varFile As String
  
   Set appExcel = New Excel.Application
    
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      .AllowMultiSelect = False
            
      .Title = "Please select Excel file."
      
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xls; *.xlsx"
      If .Show = True Then
         'Loop through each file selected and add it to the list box.
         varFile = .SelectedItems(1)
      Else
         Exit Function
      End If
   End With

    appExcel.DisplayAlerts = False
    appExcel.Workbooks.Open varFile
    appExcel.Visible = True
    
    Dim ws As Excel.Worksheet
    
            With ws
                appExcel.Range("A1").FormulaR1C1 = "Member_ID"
                appExcel.Range("B1").FormulaR1C1 = "Member_Name"
                appExcel.Range("C1").FormulaR1C1 = "Member_DOB"
                appExcel.Range("D1").FormulaR1C1 = "Member_Address"
                appExcel.Range("E1").FormulaR1C1 = "Member_Zip"

            End With

    appExcel.ActiveWorkbook.Close SaveChanges:=True
    appExcel.Quit
    
End Function

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
DanielPineault
post Nov 28 2017, 08:01 PM
Post#8


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Why not use Excel automation to perform the import. The added bonus that you can perform data validation and ensure the data complies with your rules.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 11:50 AM