looking to create table/query from many tables

[Bigschnoz]

Firstly I must admit to being extremely weak in terms of Access ability.

I am looking to build a sales forcast tool (front end may yet be access or excel). The original data comes from 3 sources which are linked comprise the bulk of the data I need with one exception. I need to break down the cost element of each line entry (job number) in the final dataset "SalesForecast".

The query created from the 3 data sources currently comprises around 4000 records and this will increase by about 1500 per year. Each individual record in this expanding dataset however needs linking to cost data which is stored in a rather strange fashion.

Each "job" in the dataset has its own individual database which holds some data not available in the main dataset. It is stored in a \year\month structure:-

\\server\shared\etc\"year"\"Month"\"Job Number.Mdb
(e.g. \\server\shared\etc\2012\Apr\5678.mdb)

I have created a linked table to a spreadsheet where each cell has a path to the relevent database which links to each job number in the main dataset.

\\server\shared\etc\2008\Jun\1033.mdb
\\server\shared\etc\2008\Jun\1034.mdb
etc.

I need to import or link to a table called "CostItem" within each of the databases and sum the values for the field "ItemCost" where the values of the field "Description" = "Fitting" (There can be multiple entries in the field "ItemCost" and therefore this needs to be summed in order to generate a single value of "CostItem" per line in the main data.

My first problem is I do not have a method of coding or creating a query for the list of db file paths that would enable me to link to data from thousands of databases within a single query or table. Any tips/clues would be gratefully received .

Once this issue is resolved I should be able to sum the cost information I am after then link this to the main "Sales Forecast" table.

Many thanks in anticipation
Steve

 

[VilaRestal]

thousands of databases! seriously?

you do not want to be linking a single mdb to thousands of others. importing one at a time is your only hope i think and no wonder you want to do it through code.

I suggest putting this request in the modules & vba section before we go any further.

 

[VilaRestal]

If the naming convention of folders and databases is strictly adhered to and all the databases have the same structure then the code wouldn't be too complicated to design.

Running it might take days and you might want to do it in stages so you can backup the main database from time to time (recording where it had got to in the procedure in another table and resuming from there). If it crashes at 90% and you're left with a corrupt database...

And I concur with Pat. You should be looking to do this as a way to dispose of those thousands of databases. Not as a one off operation and then keep using the existing system until the next time you need to go through this.

And if you're worried Access isn't powerful enough to handle all the records in one place (if each of these thousands of databases has thousands of records) then consider migrating to SQL Server. You could still use Access VBA to do the importing then.

 

[Bigschnoz]

Many thanks for your comments guys.
The data file structure is as shown below. Setting up an initial data extract as a batch process would be useful as a starting point but there will be an ongoing update issue as each new job creates a new database and this will need periodically including in the forecast tool. I will redirect this to the VB section as suggested, thanks

\\server\shared\etc\jobheader.mdb
\\server\shared\etc\2012\Apr\5678.mdb
\\server\shared\etc\2012\Apr\5679.mdb........etc
\\server\shared\etc\2012\May\5789.mdb etc

Steve

 

[VilaRestal]

Thread has been moved to modules forum Pat. It is explained there.

 

[Bigschnoz]

Sorry it is my first posting and after initially posting in tables it was recommended I put in vba, unfortunately I am not aware of a means of moving the initial thread and was unsure if I should delete the initial thread.