Bizarre MS Access thing

Okay, something strange is going on.

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?

I have no idea why this is happening. Any ideas?

Thanks!

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.

Ahh, gotcha. Thanks.

It ended up being easier to export everything to Excel and work with it that way.

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.

Ya know, I thought briefly about whether there might be a web-based solution, and I totally forgot to look at Google. Next time…

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.