Query multiple tables in access

Question

We have 50 tables we need to query a column that exists in all. This column is a checkbox. We need to count per table how many are checked and how many are unchecked. Cant seem to get 1 query to count results and display per table as opposed to multiplying or combining results. We need 1 column per table to display count of checked and unchecked. Thanks

SELECT "Table1" , Count('qcpass') AS column 
    FROM 5000028 
    GROUP BY [5000028].qcpass
union 
SELECT "Table2",count('qcpass')
    FROM 5000029 
    Group By [5000029].qcpass;

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Edit

Based on your feedback, try this (sorry, didn't realize you wanted 1 column per table):

Make a union query that combines all 50 tables. The result should be 1 row per table:

SELECT "5000028" as QCPASS, Count () FROM 5000028 group by QCPASS
UNION
SELECT "5000029" as QCPASS, Count () FROM 5000029 group by QCPASS
UNION...
Now make a "Crosstab" query which is pretty easy in Access. First, make a new query and select the Crosstab option at the top. This query will use the union query as its source.
This will have 3 columns. The first will be a constant value (you can use "Totals" if you like, it's just a placeholder). Set this as your "Row Heading".
The 2nd column will be QCPass. Set this as your "Column Heading".
The 3rd column will be Expr1. Set this as your "Value".

When you run this, you should see a 1-row table with 1 column per each of your source tables.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 3, 2013 at 16:18

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user2543597 Over a year ago

SELECT QCPASS AS 5000028, Count () FROM 5000028 group by QCPASS UNION SELECT QCPASS AS 5000029, Count () FROM 5000029 group by QCPASS This counts but puts everything in 1 row - I need 1 column per table.. Thanks Greatly for the help

PowerUser Over a year ago

Hi User, I edited my post, but it's actually incomplete. As is, it only gives you the total number of checkboxes in each table. What is the name of your checkbox?

user2543597 Over a year ago

Thanks for the response - The below seems to output the result separately per table in a row - which is ok - but my next challenge is to get only records in a date range from a DATETIME column in each of the 50 tables: SELECT "okok" AS Expr1, Count('qcpass') AS w FROM 5000028 GROUP BY [5000028].qcpass union SELECT "op",count('qcpass') as i FROM 5000029 Group By qcpass;

PowerUser Over a year ago

Can you edit your post and add your code there? It's unreadable in the comments like that. Thanks.

user2543597 Over a year ago

SELECT "Table1" , Count('qcpass') AS column FROM 5000028 GROUP BY [5000028].qcpass union SELECT "Table2",count('qcpass') FROM 5000029 Group By [5000029].qcpass;

|

logixologist · Accepted Answer · 2013-07-03 16:48:00Z

0

SELECT columna, 'tablename1' from tablename1 where ..
UNION
SELECT columna, 'tablename2' from tablename2 where ..
UNION
SELECT columna, 'tablename3' from tablename3 where ..
...
SELECT columna, 'tablename4' from tablename50 where ..

edited Jul 3, 2013 at 16:48

answered Jul 3, 2013 at 16:37

logixologist

3,8644 gold badges34 silver badges47 bronze badges

1 Comment

user2543597 Over a year ago

I need to count the checked and unchecked rows.

Collectives™ on Stack Overflow

Query multiple tables in access

2 Answers 2

Edit

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related