Unnormalized data? can you use column number instead of name.

[jboomer]

I'm at my wits end tryting to figure out how to get this to work. I am trying to build an access database that looks at an outside csv file. The CSV file gets updated by an out side piece of software and the data in this tab;e looks like this:

SKU | 7-25-2010 | 7-26-2010 | 7-27-2010| And so on
1234 | 1 | 9| 15| and so on

it's update multiople times a day, and as the days go by the dataset always shows 90 days out from current date. I'm trying to create a query that will show what values are for 10 days out or the 11th coulmn. I thought to use the coulmn(11) function but that doesn't seam to work in SQL. since the name changes i'm not sure excatly what to do.
I can't change the data as it comes in, so i'm not sure i can even normalize the data.
Any one ever run into this?

 

[Galaxiom]

Get the field names from the tabledef object and build the query in VBA

 Currentdb.TableDefs("tablename").fields(10).Name

Note the numbering starts at zero hence 10 rather than 11.

Use a loop to get them all if required.

 

[jboomer]

Get the field names from the tabledef object and build the query in VBA

 Currentdb.TableDefs("tablename").fields(10).Name

Note the numbering starts at zero hence 10 rather than 11.

Use a loop to get them all if required.

how would i do this in the query view since i have to use SQL?

 

[MarkK]

To get at the data, if you have a table, you can open a recordset and access it's fields collection by index similar to Galaxiom's post

dim rst as dao.recordset
set rst = currentdb.openrecordset("tablename")
with rst
  msgbox .fields(10).name & ": " * .fields(10)
  .close
end with

But you can access fields by name too, like ...

  msgbox .fields("7-25-2010")

To get at only specific SKUs and/or dates you can do ...

set rst = currentdb.openrecordset( _
  "SELECT [7-25-2010], [7-26-2010] " & _
  "FROM tYourCSVTable " & _
  "WHERE SKU = '1234'")

But in the longer run I would write tools to get the data out of the csv files and into permanent tables so the data are normalized and you have easier access.

 

[jboomer]

To get at the data, if you have a table, you can open a recordset and access it's fields collection by index similar to Galaxiom's post

dim rst as dao.recordset
set rst = currentdb.openrecordset("tablename")
with rst
  msgbox .fields(10).name & ": " * .fields(10)
  .close
end with

But you can access fields by name too, like ...

  msgbox .fields("7-25-2010")

To get at only specific SKUs and/or dates you can do ...

set rst = currentdb.openrecordset( _
  "SELECT [7-25-2010], [7-26-2010] " & _
  "FROM tYourCSVTable " & _
  "WHERE SKU = '1234'")

But in the longer run I would write tools to get the data out of the csv files and into permanent tables so the data are normalized and you have easier access.

I am wanting to use it in a query not a measagebox? would i still use this?

 

[Galaxiom]

Your query will have to be generated in VBA. There is no way around this as you need a dynamic query.

The msgbox shown above is just a demonstration of the syntax to address the object properties. You will need to set the value of a variable to the name retreived by the expression then concatenate that variable into the sql of a query string where the fieldname is normally written.

 

[jboomer]

Im not sure how to do it right now this is what i have and i'm not use what i'm missing:

Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT * from

"
set rst = db.openrecordset("Table")
with rst
msgbox .fields(10).name & ": " * .fields(10)
.close
end with
'close recordset
rst.Close
Set db = Nothing
End Sub

Please help this is afairly new to me.

 

[Galaxiom]

You will probably want the query as the Record Source for a form or report.

Use this code in the module associated with a form.

Private Sub Form_Load()
 
Dim strFieldName As String
Dim strSQL As String
 
   strFieldName = CurrentDb.TableDefs("yourtablename").Fields(10).Name
 
   strSQL = "SELECT " & strFieldName & " AS [somename] FROM [Tablename]"
 
Me.RecordSource = strSQL
 
End Sub

Include a control in the form with the control source [somename].

 

[jboomer]

You will probably want the query as the Record Source for a form or report.

Use this code in the module associated with a form.

Private Sub Form_Load()
 
Dim strFieldName As String
Dim strSQL As String
 
   strFieldName = CurrentDb.TableDefs("yourtablename").Fields(10).Name
 
   strSQL = "SELECT " & strFieldName & " AS [somename] FROM [Tablename]"
 
Me.RecordSource = strSQL
 
End Sub

Include a control in the form with the control source [somename].

Is there anyway to use this in a query instead of a form or report?

 

[MSAccessRookie]

I'm at my wits end tryting to figure out how to get this to work. I am trying to build an access database that looks at an outside csv file. The CSV file gets updated by an out side piece of software and the data in this tab;e looks like this:

SKU | 7-25-2010 | 7-26-2010 | 7-27-2010| And so on
1234 | 1 | 9| 15| and so on

it's update multiople times a day, and as the days go by the dataset always shows 90 days out from current date. I'm trying to create a query that will show what values are for 10 days out or the 11th coulmn. I thought to use the coulmn(11) function but that doesn't seam to work in SQL. since the name changes i'm not sure excatly what to do.
I can't change the data as it comes in, so i'm not sure i can even normalize the data.
Any one ever run into this?

Does the data in the example above represent two lines of a CSV File, or is it one line that is wrapped for display. The resolution would need to be different depending in the situation. Can you provide a sample data in the format of the CSV file (two full lines (or pairs of lines) should be enough).

To Answer your question regarding not having to use a Form or Report, VBA SubRoutines and Functions can be designed to be called directly from an SQL Query. Since I am not sure of your actual need, I cannot advise the best way for you to do this at this time. Perhaps someone else will have some additional suggestions

 

[Galaxiom]

Is there anyway to use this in a query instead of a form or report?

You could set the strSQL as the SQL property of a QueryDef.
http://office.microsoft.com/en-gb/access-help/HV080753700.aspx

Then you could run or export that query using VBA.

However there is no way to use the code in a static query because you need to dynamically control the name of the field.

 

[Galaxiom]

Another alternative would be to set standard names for the fields during the csv import rather than just using the first row as name. Then you could use a static query.