I have a database which has numeric table names like 25, 85, 100, -10, -40; I use excel to get the data from the database, here's the query that i m using

SELECT [25].DUT, [25].BB_AMP1,[25].AMP_LO, [25].F_LO_SIGGEN/2, [25].OUT_AMP, [25].LO_AMP,[25].USB_AMP, [25].USB_AMP-[25].OUT_AMP, [25].F3BB_U_AMP, [25].F3BB_U_AMP-[25].OUT_AMP
FROM [25]
ORDER BY [25].DUT, [25].AMP_LO, [25].BB_AMP1, [25].F_LO_SIGGEN;

The problem that I am facing is I am able to retrieve the data from the table 25 and 85 but if I change the table names to either 100 or -10 (and probably -40, i havent tried that yet), the query stops working and I get a type mismatch error.

SELECT [100].DUT, [100].BB_AMP1,[100].AMP_LO, [100].F_LO_SIGGEN/2, [100].OUT_AMP, [100].LO_AMP,[100].USB_AMP, [100].USB_AMP-[100].OUT_AMP, [100].F3BB_U_AMP, [100].F3BB_U_AMP-[100].OUT_AMP
FROM [100]
ORDER BY [100].DUT, [100].AMP_LO, [100].BB_AMP1, [100].F_LO_SIGGEN;

Another thing that is happening is if i remove one of the expression from [100] query (either [100].USB_AMP-[100].OUT_AMP OR [100].F3BB_U_AMP-[100].OUT_AMP) it works again.

All tables have exactly same structure. Can someone understand what the problem could be.

Why do you have multiple tables with the same structure?

Coz each have different data.

Not a good reason. Add a field to the table that identifies the record and put them all into one table.

Yeah that is what I too recommended, but this thing has been working this way for long time and I just stepped in and cant start making radical changes.

The major reason for putting data in different table was coz if everything is in one table the no.of records grow to a large number and people who aren't familiar with access queries had a tough time getting what they need.

Scott is right. All this data belongs in the same table, with a way if necessary to tell what kind of record it is. You will then need to make it easy for them to fetch what they need. What I would do is make a copy of the stuff as it is now, put all the data in the same table as Scott described, and build a demonstration system to show the users how to do it. And for that matter the powers that be.

Frankly that makes NO sense. What difference does it make if they filter on a field that identifies the data group or they choose a data table? If they can build a query now, adding one field and filtering shouldn't make any difference.

This is not a radical change, this is a logical change that may solve a lot of other problems.

This is easy to explain to someone who understands SQL and its power but no one wants to change the way the things are done and It would take me some time before i can explain this thing. The problem in front of me if why the same query doesn't run with different table names ?

I disagree. Are these queries canned now or do users run their own adhoc queries? If the queries are canned then all you need to do is present them with a form to enter criteria. Why should they need to know SQL. If they can select a query name they can select a query and choose the data filter from a combo on a form. If they are running their own adhoc queries they they already know enough about SQL.

Also if they are running canned queries they how do they know how many tables there are? Users should have NO say in table design.

The problem here is the design. Fix the design and your other problems disappear.