We’re using Access 2003 to enter data from evaluation forms into tables. Because we can’t have a single table open at once, I set up three identical tables (and forms for data entry).
Table 1 has 46 records.
Table 2 has 30 records.
Table 3 has 21 records.
To merge them all into one table, I created a make table query in Access’s query design view that would add all the records from all three tables and create a new table. The query runs fine, but the resulting table has almost 30,000 records. Huh what?
Your query is joining all the tables with a cartesian product instead of concatenating them, so you get 46 * 30 * 21 rows. Don’t know how the query builder in Access works, exactly-- but in SQL you would want to be doing a UNION and not a JOIN of the tables.
It has about 28980 rows, right? That would be Access doing a cross-join instead of stringing them together in a union. In SQL terms, you’d write what you’re looking for as:
CREATE TABLE [tablename] AS
SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3;
If you’re getting 28,980 what it’s actually doing is taking the first row from Table 1, the first row from Table 2, and the first row from Table 3 and making a record. Then the first row from T1, first from T2, second from T3. And so on.
Incidentally, if your goal is to have a multi-user form that enters into a single spreadsheet-like table, look at Google Forms. You can quickly whip up a form that will automatically save every entered row into a spreadsheet, and it’s instantly multi-userable, unlike horrid Access hacks.
You can do that SQL in Access by the way, so that running the query unions those 3 tables together for you as the result set - probably quicker than 3 exports and copy/pastes in excel. I did that exact thing earlier this week.