Master Form for all tables

[babj1]

I have been given a project:

We have several spreadsheet that are generated on a monthly basis. The files are placed in a specific location each month however the path remains the same, for eg. a specific file for march will be kept in March folder, another file for April will be kept in April Folder etc. The naming convention for the files are same only the month differs, for eg 'Trader File March' is used for March, 'Trader File April' used for April etc.

I have to build a form (or anything that might get the job done) to link all the files in Access to make a master file which will help me get any information from the spread sheets that are located in the respective folders.

A help would be very much appreciated as i am a bit handicapped when it comes to Access.

 

[Pat Hartman]

Import the data into the same table. Link to each table in turn and use an append query that selects from the linked table and appends to the consolidated table. Given that your workbook names include "data", you'll need to create a column in the consolidated table to identify the workbook the data was imported from.

You would then use queries with selection criteria to control which set of data you select.

What generates the spreadsheets? Why not get your data from the source?

 

[babj1]

Thanks for the reply Pat.

The Reports gets generated by some jobs that run on some server. The spread sheets gets generated via the jobs.

Cant we make a form where we pass the parameters (eg. I give a products name and everything related to that product is shown in the screen.?). The informations would be collected from the respective files from the respective folder.

 

[Pat Hartman]

Are you talking about collecting the data on the fly?

You didn't answer my previous question - Why not get your data from the source?
If the source is a relational database, get permission to link to those tables. It is always better to go to the source for data. Of course, having worked in IT for far too long, I understand the fiefdoms that spring up around data sources. It's like the seagulls circling around Nemo in "Finding Nemo" - Mine! Mine! Mine!

 

[spikepl]

OT:

I understand the fiefdoms that spring up around data sources

The data crapification and subsequent decrapification supports many IT-people