Creating a Query from multiple tables (same key)

[no cigar]

Hey all, this is my first post here. I'm glad to have found a good forum like this where I can pose some Access questions.

I'm working on this database and, right now, cannot figureout how to extract information from various tables.
See the diagram:

Basically, as the illustration says, I'm trying to query these three tables and somehow consolidate all of the records in one table, by name... Thanks!

 

[wilpeter]

The first thing you might want to do is add the Make field to the tables. Depending how many records you have accumulated, each table could be adjusted by adding the Make field values (Ford, Chevy, etc). You can use an Append Query to add the appropriate value to that field's records.
Then when each existing table has matching field names in the same order, you can start a new table, adding a Key Field such as AutoNumber. Then copy/paste the records to it. You'll find sorting and querying so much easier.

 

[no cigar]

Awesome. Thank you very much!

 

[wilpeter]

You're welcome, hope it helps. Season's Greetings!

 

[no cigar]

Ok to expand on this, I want to consolidate these into a single table... Is there any way to do this without having to manually set up the tables' design? Is there a way Access will import from one table to another and create the layout based on the table you are copying records from? I'm just saying because I have 4 tables, each with around 20 fields, some present in all four tables and some unique. I would really like to have these consolidated in a 'master' table without having to manually set them up.

I do know about make table query. But it only works once. If I run it again, it wants to delete the master. I would use append but again, it's not willing to create the non-existant fields for me.

 

[wilpeter]

I don't see a safe way to accomplish it without the first Make Table Query having at least all the fields from the existing table that has the most fields. e.g., take one table, add the Make field, insert the value of that field for the first table's records (e.g., Ford). Then use the Make-Table Query to produce the Ford portion.
If one of the Experts here will intervene here...
Perhaps with code, an Append Query can force new fields in this new table from the other two Tables. Anybody??
Just thought: You might export/analyze each table in Excel...then you could copy and paste them together with the extra fields in new columns. Then they could be imported to Access as a new table. Might be some formatting to adjust after.