Dynamic SQL statement

[EL-g]

Hey guys,

Ive been enjoying a lot being part of this community. 1st of all i want to thank all the memebers who replyed for my previous doubts.

I guess i still have one last one. My database (tables) are being automatically updated by a module. So its not unusuall that i have at least one new table a day. I was wondering if tehre is anyway i could make a dynamic SQL statement that would join all the tables, including new ones and group by date. Basically each one of my tables have only 2 columns (date and value)

Can anyone help me out?

Thx a lot in advance

 

[grnzbra]

What is going into these tables such that there is a new one created every day?

 

[EL-g]

its for stocks
when a new stock is lanched in the market a new .txt file is created with the data series from it

 

[grnzbra]

Are you stuck with this design or can do you have the ability to change it? You would be better off with two tables: Stocks with fields StockID, StockName, and any other pertenant info, and StockPrices with fields PriceID, StockID, Date, Value. Then you could easily write a query, joining them on StockID.

Do you have the ability to change this?

 

[gemma-the-husky]

You could give the main table (ie the one with all the values) a standard name. You may have to change your app to query this table differently etc

Now you can import the incoming tables one at a time into a blank table, tblNewData, or something simillar. Validate the data in tblNewData if you have to, and then just run an append query to add all the data in tblNewData to the main table.

The next import will then overwrite the NewData table. You can even use standard windows file open dialogs to pick the table to import, and then import with docmd.transfertext etc.

 

[EL-g]

@grnzbra - Actually im kinda stuck with the .txt files design. So i have only 2 columns for them (DATE,VALUE)

@gemma-the-husky - What im trying to do is to get them all in a main table. I still dont have this main table with all the data unortunatly. What i have so far are many individual tables

 

[gemma-the-husky]

its bound to be a bit of work.

EITHER

create another table with the names of all the tables you want to concatenate.

then write code to open the tasble with a recordset

and do (in macrocode)

rs.movefirst
while not rs.eof
append (data from rs.name) into (maintable)
rs.movenext
wend

OR

if you have a naming convention for your date/value tables then you could just iterate the tabledefs collection

dim tdf as tabledef
for each tdf in currentdb.tabledefs
if tdf.name (meets certain criteria) then
append tdf to maintable
next tdf

 

[grnzbra]

Actually, you do have a third column, the name of the table.
Are you getting a whole new set of tables every day or are you getting one or two new ones and then daily updates for each existing table?

If you set up two tables, Tables (or Stocks) with an autonumber id field and a text field for the name of the original table from which the date and value are drawn, and a DateValue table which will have an autonumber field for id, a foreign key field for the link to the Tables table and the date and value field

Then, using gemma's second solution, loop through the table definitions and open up a recordset of all records in the Tables table which contain the name of the current table def. If the recordset is at EOF, add the new table name to the table. Then query it to get the corresponding ID field from the Tables table and us it and the table name to modify the SQL of an existing append query that will add the table id to the foreign key id field of the DateValue table along with the Date and Value. If you get a response, of course, you would just use the id straight without adding a record to the Tables table.

You will have to set up the query to ignore duplicate recrords (I would assume that you would search on unmatched dates)

By doing this as a loop into a pair of tables, any future analysis of the data will be fairly simple. If you want to create a query that keeps growning as new tables are added, you will eventually have an unbelievably complex query.

What is the macro doing, downloading .txt files to the computer or actually creating tables from the .txt files? I am assuming it is actully creating tables.

 

[EL-g]

@grnzbra - The macro works like this: I make an array with the name of the .txt files inside of the folder and if it does not exist as a table in access it creates a nem one. And it also updates the old ones.

So far i dont have a ID field because i could not set the date as ID field and some tables have dif date ranges. So if the id field would be (1,2,3,...,oo) i would eventually get to dif dates for 1 when joining the tables.

The main problem on building my query so far is exactally what you pointed out. It needs to add up the new tables that are created in an automatic way. Otherwise it would make no sence having to type in the SQL code for every new table that is created. So basically that is being my main problem so far. Because writing the query manually with the tables i have so far takes a few hours of work but can easily be done. But what im trying to do is to set this automatically in order that the query will creat a new column in the main table for every nem table that is created. And i actually must admit that i have no clue how to do that. Im trying to write a dynamic loop query in VB.NET which does not work so far. I also tryied to write a access module for it with gemmas tip but i keep getting an error at this code "append tdf to maintable".

 

[EL-g]

Im trying this code:

'Âssuming you put it all in a table called combined_table with the columns : date, value, name of file

'Build an array to play like I had your files
arrFileNames = Array("a.txt","b.txt","c.txt","d.txt")


For i = 0 to ubound(arrFileNames)

strTableName = Left(arrFileNames(i), InStr(1, arrFileNames(i), ".") - 1)


strSQL = "select distinct(date) from combined_table"

objDateRS = objConnection.Execute(strSQL)

objDateRS.MoveFirst

Do until objDateRS.EOF

strSQL = "select name, value from combined_table where date = '" & objDateRS("date") & "' and name = '" & strTableName & "'"
objDataRS = objConnection.Execute(strSQL)
If objDataRS.BOF or objDataRS.EOF then
intValue = 0
else
If objDataRS.RecordCount > 1 then
'error because a single name should not have 2 values on the same date, right?
else
intValue = objDataRS("value")
end if

end if
'Here you can write the info where ever you want
'Date = objDateRS("date") | Column Name = strTableName | Value = intValue
objDateRS.MoveNext
loop

next

But i cannot make it to work because of the objconnection