Hi,
I keep reading the posts on this forum and they have helped me a lot. This is the first time I am posting a question.My apologies if this has been asked before but i Searched extensively and cud not find an answer.

I am trying to update a field with multiple values based on a criteria(date). Basically the table holds monthend benchmark data for last 10 years for various indices and everytime a new benchmark is added,I have to add a new column to the table which I h ave done succesfully using the ALTER TABLE query . The next step is to populate the column with last 10 yrs month end data which is in EXcel. I am trying to use UPDATE query but I cant figure out how update all 10 yrs at once based on onthend dates.

tablename- benchmarkdata
Hers is the query I se to ALTER the table to add a column( I am picking up new column name from a cell in Excel)- it works

dbcon.Execute "ALTER TABLE benchmarkdata ADD COLUMN [" & Range("Iv" & i & "").value & "] Double"


The next query is to Update the values in the new column

This query qorks but it only updates one value ata a time by matching one date field( all from excel)
dbcon.Execute "UPDATE benchmarkdata SET benchmarkdata.[" & Range("Iv" & i & "").value & "] = " & Range("a" & i + 1 & "").value & " WHERE (([Monthyear]=#" & Range("c" & i + 1 & "").value & "#))"

HOW DO I EXTEND IT to be able toupdfadte multiple values
???
this is a query I wrote( newupload is the name of the sheet, E:E has the update values and D:D has the dates whcih cxorrosp[ond tot he dates in the benchmark table)- THIS IS GIVING AN ERROR


dbcon.Execute "UPDATE benchmarkdata,[newupload$] SET benchmarkdata.[r index] = [newupload$E:E] WHERE (([benchmarkdata].[Monthyear]=[newupload$D:D])) IN '" & sXlsPath1 & "' 'EXCEL 8.0;'"

pls help!!!!

Welcome to the forum!

I'm a little worried that you have to add a new field to your table for each new benchmark. It sounds like you are creating a table that has repeating fields benchmark2000, benchmark2001, benchmark2002 etc. I would think that each year's data should just be a record in a table not a field. Could you explain a little further what your application is designed to do and what your table structure looks like?

Welcome to the forum!

I'm a little worried that you have to add a new field to your table for each new benchmark. It sounds like you are creating a table that has repeating fields benchmark2000, benchmark2001, benchmark2002 etc. I would think that each year's data should just be a record in a table not a field. Could you explain a little further what your application is designed to do and what your table structure looks like?

I am not repeating fields... Let me tell you about my table structure..The first field is date and teh other fields are actual indces/benchmark names( eg. SPX INDEX,RUO INDEX) each field has month end values for the benchmarks gong back 10 years i.e each record is ne months data'. Every time the user adds a new benchmark I add a new field and have to populate the field wth last 10 yrs data. I want to be able to update the field with one update query whoch I execute from Excel.The dates which are teh critera for each updated vaue and the actual update value are to eb taken from Excel..Let me know ur thoughts.Thanks for the help.

By this statement, it sounds like you have repeating groups:

..each field has month end values for the benchmarks gong back 10 years

I'm thinking a structure like this might work, but it would be easier if I could see some actual data. Would it be possible to zip and post your database?

tblBenchmarks
-pkBenchmarkID primary key, autonumber
-txtBenchmarkName


tblBenchmarkMonthlyData
-pkBMDataID primary key, autonumber
-fkBenchmarkID
-dteMonthEnd
-BenchmarkValue