Using GROUP BY to group the count of multiple tables

[jazzin]

I have figured out how to get the total # of rows for each of my tables, however it creates an infinite # of rows that list the totals of each table. How can I group all these rows together so I have just one row with the total for each table in it?

Here's what I have so far

SELECT (SELECT Count([3_0_addr].ADDRESS) AS 3_0_NUMOFRECS
FROM 3_0_addr) AS 3_0_NUMOFRECS, (SELECT Count([3_2_addr].ADDRESS) AS 3_2_NUMOFRECS
FROM 3_2_addr) AS 3_2_NUMOFRECS, (SELECT Count([3_4_addr].ADDRESS) AS 3_4_NUMOFRECS
FROM 3_4_addr) AS 3_4_NUMOFRECS, (SELECT Count([3_6_addr].ADDRESS) AS 3_6_NUMOFRECS
FROM 3_6_addr) AS 3_6_NUMOFRECS, (SELECT Count([3_8_addr].ADDRESS) AS 3_8_NUMOFRECS
FROM 3_8_addr) AS 3_8_NUMOFRECS, (SELECT Count([4_0_addr].ADDRESS) AS 4_0_NUMOFRECS
FROM 4_0_addr) AS 4_0_NUMOFRECS, (SELECT Count([4_2_addr].ADDRESS) AS 4_2_NUMOFRECS
FROM 4_2_addr) AS 4_2_NUMOFRECS, (SELECT Count([4_4_addr].ADDRESS) AS 4_4_NUMOFRECS
FROM 4_4_addr) AS 4_4_NUMOFRECS, (SELECT Count([4_6_addr].ADDRESS) AS 4_6_NUMOFRECS
FROM 4_6_addr) AS 4_6_NUMOFRECS, (SELECT Count([4_8_addr].ADDRESS) AS 4_8_NUMOFRECS
FROM 4_8_addr) AS 4_8_NUMOFRECS, (SELECT Count([5_0_addr].ADDRESS) AS 5_0_NUMOFRECS
FROM 5_0_addr) AS 5_0_NUMOFRECS, (SELECT Count([5_2_addr].ADDRESS) AS 5_2_NUMOFRECS
FROM 5_2_addr) AS 5_2_NUMOFRECS, (SELECT Count([5_4_addr].ADDRESS) AS 5_4_NUMOFRECS
FROM 5_4_addr) AS 5_4_NUMOFRECS, (SELECT Count([5_6_addr].ADDRESS) AS 5_6_NUMOFRECS
FROM 5_6_addr) AS 5_6_NUMOFRECS
FROM 3_0_addr, 3_2_addr, 3_4_addr, 3_6_addr, 3_8_addr, 4_0_addr, 4_2_addr, 4_4_addr, 4_8_addr, 5_0_addr, 5_2_addr, 5_4_addr, 5_6_addr, 4_6_addr;


Any help on how to group this would be appreciated

[June7]

How many tables are you doing this with (28)? Is there a common ID value that they can be joined on in query? If not, do a UNION query then use that query in a CROSSTAB.

There is no wizard or designer for UNION query, must type (or copy/paste) into SQL View window of query builder, limit of 50 lines.

SELECT Count(ADDRESS) AS NumOfRecs, "3_0" AS Source FROM 3_0_addr
UNION SELECT Count(ADDRESS), "3_2" FROM 3_2_addr
...
UNION SELECT Count(ADDRESS), "4_6" FROM 4_6_addr;


Alternative to UNION then CROSSTAB is DCount() domain aggregate function.

SELECT DCount("Address", "3_0_addr") AS 3_0_NumOfRecs, ..., DCount("Address", "4_6_addr") AS 4_6_NumOfRecs FROM [use name of any table in db];

[jazzin]

I have 14 tables. I thought about creating a relationship btw tables but I really don't want to take attributes from each table and combine them. All I want to do is get a total count of the number of records in wach table, which I have in the above code but it list an infinite list of numbers in each column. What I need is to group the totals together so they are all on one row and not infinite rows.

[June7]

One of the two suggested methods should get you there.

[jazzin]

Hey it worked! Thanks a bunch!