Table organization help requested

[TylerTand]

I have at least 200 excel spreadsheets that get updated every day with closing prices of commodities. I want to run a breakout query against every spreadsheet to see if today's close is a new 20 day breakout. What is the best way to organize the access table or tables? Put all the data in one table or have separate tables for each contract and run one query against the group? I need to be able to update the tables everyday so that today's close info is included. I have a working query that works with a single dynamically linked spreadsheet to table, but I don't want to set this up for all 200 spreadsheets. Can this process be duplicated? I have tried to modify the query to run against two separate tables, but was unsuccessful. Since there are so many potential tables, if that is the best way to do it, how can you say select all tables and then run the query?

Here is the query I have written:

SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));

The WZ07 is the December Wheat contract for 2007. This is what the excel spreadsheets are named. I had to do a dateserial command to turn a number into a date(couldn't make access recognize that 20070831 was August 31,2007) All the column headings on all the spreadsheets are the same: Symbol, AlphaDeliveryMonth, DDate(transdate),Close

I know that this is both about tables and queries so I wasn't sure where to post this. Thanks for your help.

 

[The_Doc_Man]

This is do-able but might require some significant VBA programming. I'll give you the overview. You can search this forum for some keywords I'll capitalize. Also search Access Help for them.

In general, if you can import your spreadsheets to a single folder before processing, you can use the Access FILE SYSTEM OBJECT to identify the names of each workbook.

If the workbooks are always in the same format, just different names and numbers, you can do a TRANSFERSPREADSHEET to import the sheets one at a time to a temporary table. This assumes your individual spreadsheets have enough data that you do not need to know the spreadsheet names after the import is complete.

If the workbooks are not uniform, but there are markers in them such that a program with some smarts could identify what is needed, the alternative is to open the workbooks individually as an EXCEL APPLICATION OBJECT. When you do, you have to traverse the COLLECTION of WORKSHEETS that comprise a workbook. In each worksheet, you have sub-collections of ROWS or COLUMNS. If you traverse by Rows, the columns are seen as a collection of CELLS. If you traverse by Columns, it is the ROWS that are seen as a collection of CELLS. Anyway, using syntax to select the text portion of a cell is possible so that a VBA routine could iteratively pull out data. I'm going to assume that you use rows. If the first sheet contains rows of contract info, you might use syntax something like

XLOBJ.Workbook(1).WorkSheet(1).Row(3).Cell(20).Selection.Text

to read Sheet 1, cell C20.

OK, from there, you can create a RECORDSET to your table that you will create. Inside your look, for each new row that you are importing, you would use .ADDNEW to create a new record, then direct-access the fields by name via the RS.fieldname syntax, then when you are done, use .UPDATE to write out the record.

If you use the VBA method, then some simple rules apply. Close what you open. Put away what you take out. Destroy what you create (in terms of application objects).

 

[TylerTand]

O.K I don't have an experience using VBA. Can you explain how I use VBA code inside Access? You said I could use a FILE SYSTEM OBJECT command but I don't know how to use the VBA code once I create it in a module. Can you help me understand how this works? I was hoping that I could create a button on a report that would update all the tables everyday when I opened the form or pushed a button, and then I could run my query on the monster table and see all the breakouts. If you could guide me along in this I would appreciate it. Thanks.

 

[The_Doc_Man]

This is not easy to explain. This forum has many posts about finding files in a folder. If you are not comfortable with Access, start with a book on the subject and study up on VBA. Your goal (push one button, stand back, and wait for the report to be ready) is very possible with VBA. But the starting point is VBA. If you are not familiar with object oriented programming, some Google searchs and a couple of visits to a book store might be in order.

The overview looks like this:

On a form you can have a button that is clickable. The Button has an associated entry point called the {button-name}_OnClick event. (e.g. MyButton_OnClick). If you have a VBA subroutine defined and the _OnClick property of the button (control) points to that event routine, your VBA code is called when someone clicks the button.

OK, to reach your goal, EVERYTHING I mentioned early has to go inside the routine. It will be a nested set of loops.

Outside the outer loop: Create a FileSystemObject so you can interact with the Windows File system. Prepare a recordset so you can store what you wanted.

Outermost loop: Look for another workbook file matching some template. If you find one, open the workbook file. Find the worksheet.

Possible middl loop: If more than one worksheet per workbook, select the worksheet.

Next inner loop: Step through the collection of Rows (I'm guessing it will be row oriented) to extract one record. If the record is blank, don't store it, jut continue the innter loop. Otherwise, add a new record to the recordset using the .AddNew method of recordsets. Copy data to the fields from the spreadsheet row data. Store this record in the recordset via the .Update method of recordsets.

When you reach some marker in the Spreadsheet (perhaps a sequence of 10 rows where you found no identifying data, but that's just a guess), you are done with the spreadsheet.

If you had the middle loop, step to the next worksheet if you can.

When you reach the last worksheet, close your workbook. Try to find another.

Somewhere along the way, you'll find no more workbook files. Close the recordset. Close the FileSystemObject. You are done.

 

[neileg]

As the Irishman said, 'If I was going to Dublin, I wouldn't be starting from here'.

Using hundreds of Excel spreadsheets to hold your data is creating a massive headache. Even if you get your head around The Doc Man's advice ( and I see this is a steep learning curve), you simply can't control the data input in a spreadsheet the way you can in Access. Excel doesn't support the datatype concept except in a very loose way, Access absolutely requires tight datatype discipline.

So I'd turn this around, and look at how I could hold all my data in Access and provide a friendly way of giving users access to that data, perhaps via an interface in Excel.

 

[TylerTand]

I'm the first one to agree with you conclusion. The problem is that I have to get the data from a data service. Unless you know of a way that I can have this data go directly into Access, I have to use excel as a stop over point. I am well aware of the flexibility in excel's formatting compared to access. Do you know of a data format that I might be able to use so that the data could go directly into access? Otherwise, this is the way I have to do it. Thanks for your help.

 

[neileg]

This has more to do with how you receive the data. Explain what that is and we'll see if we can help.

 

[TylerTand]

I subscribe to a data service that sends me ascii format files everyday for commodities. Each contract month is in a separate workbook. The new data each day is just tacked onto the last row of each spreadsheet. All the spreadsheets are in a folder. Ideally I want to take all the data from each workbook and put it in a table so that I can run a query against it to find the breakouts. I will try and learn some more about VBA. I appreciate any help that you guys can render.

 

[neileg]

I think you need to look at uploading all your data into one table. Access works much better that way.

 

[TylerTand]

I have been successful at getting some code to create an Access Table with all of the contract data in it. I have a button that runs some very nice VBA code to do this. Now for the next step. I want to run a query against the entire table that will tell me if today's close in each of the different contracts is a new 20 day High. Basically if Today's Close> Max(Close where DDate between date()-20 and date()-1). BUT I need for the query to look at contracts individually. By that I mean I need to evaluate the December Contract of Corn for 2007 separately from the March Contract of Corn for 2008. Is it possible to use a group by criteria to separate the contracts? I currently don't have a primary key either. I was told that I should use the Symbol, DeliveryMonth, and Date together as this will produce a unique identifier for each contract. Can someone explain why I need a primary key when I only have one table and won't be creating any relationships? Or will I? I could use some help with sorting this out. I appreciate all of your comments, you have been a great help so far. Thanks. Tyler