Query linked tables with same field name

[tcarter963]

I have a database (access 2000) for each project that I'm working on, which has a table named RawData. Data is dumped into this table which has a field FileName that I'm trying to query. In each project database I can query this field and return a result for the number of filenames in a given month by using the criteria *1010* for the month of October. The format of the records in the Field FileName (primary key) is YYMMDD_Instrument#_Increment# e.g. 101023_LC04_001

I can't create one database for all the results with one table RawData because the number of records would eventually be very large, and each project is different and requires customization to the number and type of entries in the RawData table.

What I would like to be able to do is link all the RawData tables in a single database and query the field FileName (rather than having to do this within each individual database) to determine the number of samples run in a month for each project and I would like to query to find out the number of samples run on each instrument. I've tried to create a select query with the wizard but I can't put the FileName field in the query multiple times. I'm thinking it's because you can't put the same field name in multiple times. Is there a way to create a master table from selected fields in the RawData table that resides in multiple databases, or is there a way to query multiple linked tables with the same field name?

I want to query the FileName because it contains the following information: number of samples run, instrument, and dates.

I hope this makes sense, thanks for any help.

 

[xenou]

If you use query design (the "QBE" grid - query by example) instead of query wizard you'll be able to add the fields. You'll probably still want to change the fields names or Access will prefix them with the table names for you to keep them apart. In the QBE you do this by adding an alternate name (alias) and a colon to the column selection. Below is a "before" and "after". In this case the query would give the result of LastName in the query instead of the original underlying column name of LName.

[LName]
LastName:[LName]

On the other hand, it may still be better to create a single table, even if its only a temporary table for your work on a given day. You can do this by appending records from the multiple tables into one table, and working on that table (including a field to tell the original tables apart if needed). It may make the work easier and the queries faster - the staging would be a simple 1) clear the temporary table, 2) append records from tables a,b,c etc to the temp table with any appropriate filters if you don't need all the records from all the tables.

 

[tcarter963]

Thanks Xenou,

I tried the alias and it took a while to process the query, I think because of how many linked tables there were. I'm going to attempt to put everything in one table as you recommended, but I'm not very good with Access so it might take me a little while to figure out. One thing that comes to mind is how to create a field for the project number. The project number isn't currently a field because there is a database for each project.

 

[xenou]

In your append query (lets say your appending data from database 1 table 1) you would include another field using the same alias strategy as above.

If the project number is a text ID value:
ProjectNumber: "XYZ"

If the project number is a numeric value:
ProjectNumber: 123

Then you will get this ID in every row for the data - it will serve as an identifier of the original table or database.