Referring to a column number (location) in a query

[Shairal]

I receive a .txt file each week where the first few columns are basic product data, and the next eight columns are upcoming weekly forecast numbers. The column headings for the forecast numbers is the appropriate Sunday's date, ie 8/19/2007, 8/26/2007. I am appending this data to a table, and this naming convention requires me to go into the query and add the latest date in the query to append.

What I'd like to create is a union query saying something like this: (the [File Date] field is always the first Sunday of data in the file)

SELECT Mid([Item Number],2) & Mid([Mfg Part],2) & Facility AS UniqueID,
[File Date], [Product Code], [Product ID], COLUMN 6 AS MAX( [File Date])
FROM Download

UNION ALL SELECT Mid([Item Number],2) & Mid([Mfg Part],2) & Facility AS UniqueID,
[File Date], [Product Code], [Product ID], COLUMN 7 AS AS DateAdd("w", 1,[File Date])
FROM Download

UNION ALL SELECT Mid([Item Number],2) & Mid([Mfg Part],2) & Facility AS UniqueID,
[File Date], [Product Code], [Product ID], COLUMN 8 AS AS DateAdd("w", 2,[File Date])
FROM Download

etc … ;

I can only find using column numbers (locations) when using combo/list boxes in forms. Is there any way to do this in a query? I'd like to break away from the manual selecting of each new week's data before appending. Using Access 2003.

Thanks for the help!

 

[Remou]

Would you be content to build the query in VBA?

[Code Typed, not tested]Dim rs As DAO.Recordset
Set rs=CurrentDB.OpenRecordset("Download")
strSQL="SELECT Mid([Item Number],2) & " _
& "Mid([Mfg Part],2) & Facility AS UniqueID, " _
& "[File Date], [Product Code], [Product ID], " _
& rs!Fields(5).Name & " AS MAX( [File Date]) FROM Download"[/code]

 

[Shairal]

I've never tried before, but I'd like to give it a try! I'm not sure I understand how to run it. I've used VBA in with my forms quite a bit, but I always link it to a button or process - can you give me a quick explanation on where & how to use the code?
Thanks!!

 

[Remou]

You could add code to a command button on a form or to a sub in a new module, very roughly:

Sub ImportDownload
Dim rs As DAO.Recordset
Dim db As Database
Dim qdf As DAO.QueryDef

Set db=CurrentDB
Set rs=db.OpenRecordset("Download")
strSQL="SELECT Mid([Item Number],2) & " _
& "Mid([Mfg Part],2) & Facility AS UniqueID, " _
& "[File Date], [Product Code], [Product ID], " _
& rs!Fields(5).Name & " AS MAX( [File Date]) FROM Download"

'If you has an action SQL statement, Update, for example
'you could run it with
'db.Execute strSQL, dbFailOnError

'There are a number of things you can do with a Select 
'statement. Here is a query:

'May be version dependent. Does the query exists?
If DLookup("Name", "MSysObjects", "Name= 'tmpImport'") <> "" Then
'If it exists, permanently change it.
   Set qdf = CurrentDb.QueryDefs("tmpImport")
   qdf.SQL = strSQL
Else
'If it does not exist, create it.
   Set qdf = CurrentDb.CreateQueryDef("tmpImport", strSQL)
End If
End Sub