Im havin a heck of a problem. I have a database with 800 tables. All the tables are the same with the same fields. Each table is a survey form. My table structure is as follows:

FPC_Code | FPC_Product | Price | oos | osd

the above sturucture is the same for all my tables. Now what i have to do is as follows:

I have one master table Which conatins a range of prices which each product can fall between. FPC_Code being my primary key:

FPC_Code | LowerPrice |upperPrice.

Now i have to compare each product in my master table with all the 800 tables and check whether the price in each table falls with in the 'UpperPrice' and 'Lowerprice' price bracket of the master table. Pls note that each table contains 200 products.


I have an idea how to start it but need a lil jump start.

Any help will be much appreciated.

What is your idea of how to start?

I guess I would make a loop that loops through the list of each table. At each loop, create a sql statement that joins the current table to the master table on the FPC_Code. Within the query, put in a WHERE statement to compare the Price of the records to the LowerPrice and UpperPrice in the master table.

Let me know if that's enough of a jump start.

so your suggesting to use SQl to validate it? My idea was load each table into a dataset->datatable and compare each row with the row object. I dunno whether its the optimum way to do it coz im not too great at SQL.

I was suggesting using vba to loop through each table and then creating a record set based on the table and master table.