querying from tables without same fields

[elizagilbert]

I am new to this forum and a beginner to intermediate with Access.

I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.

Thanks for any ideas

 

[pbaldy]

Try a UNION query:

SELECT Field1, Field2, Field3
FROM Table1
UNION ALL
SELECT Field1, Field2, "None" AS Field3
FROM Table2

Note that each of the "sections" of the query must have the same number of fields in the same order. If a table doesn't have the relevant field, put in a "placeholder", as in the second one in my example.

 

[elizagilbert]

parameter values

Paul, The union is the perfect tool..if I can get it to work correctly. I tried a simple union in a blank database and got it to work. Then I went to my database and tried. These are the problems I am coming across now

I am given syntax errors if I keep the spaces that are in the field names so then I deleted the spaces and have kept everything in lower cases. That has eliminated the syntax error messages

When I run the query it asks me to enter parameter values. A gentleman I work with said to enter an "*", which I did and it looked like things were working until a message came up saying "data type mismatch in criteria expression". Then it returned 180 records with #NAME? in the cells. If the two tables were actually unioned there would have been over 200 records. If you have any thoughts, thank you

 

[pbaldy]

It would help to see the actual SQL, or a sample db if feasible.

You could have used brackets ([field name]) to get around the spaces problem, but you're better off without spaces in your names anyway.

 

[mresann]

Try removing the "ALL" from the Union statement in pbaldy's answer. In addition, the fields you are trying to match in all queries MUST be the same datatype; that is, the first field listed in each query must identical datatypes, the second field in each query must have identical datatypes, etc. This can be very difficult to catch at times.