Union and group, then count

[Marcus]

I got two three tables, table 2 & 3 only got a field with a text
value.
In table 1 I want to have a field which lookup and count how many
times the value occurs in table 2 & 3, for each row. I added a field
as 'data type' 'Combo box' and the sql below as 'Row Source'.

SELECT count(*) FROM
(select [text] from [Table2] union ALL select [text] from [Table3])
AS tabletmp
WHERE [tabletmp].[text]=[Table1].[text] GROUP BY [tabletmp].
[text];

Access doesn't return any value at all and I don't get any error
message at all. What could be wrong?

 

[John W. Vinson]

I got two three tables, table 2 & 3 only got a field with a text
value.
In table 1 I want to have a field which lookup and count how many
times the value occurs in table 2 & 3, for each row. I added a field
as 'data type' 'Combo box' and the sql below as 'Row Source'.

SELECT count(*) FROM
(select [text] from [Table2] union ALL select [text] from [Table3])
AS tabletmp
WHERE [tabletmp].[text]=[Table1].[text] GROUP BY [tabletmp].
[text];

Access doesn't return any value at all and I don't get any error
message at all. What could be wrong?

First off, you shouldn't store this count at all, in any table; secondly, a
Field should not have a data type of Combo Box. A combo box is a display tool,
not a datatype! Data is Text, or Number, or Date, etc.; it's not a Combo Box!

If you want to display each value of the text field and count the number of
occurances, you can do it dynamically in a Query. There is no need or benefit
to store it in a table (if you did, its value would be WRONG the moment you
changed either of the other tables).

First create a Union query uniAllText:

SELECT [Text] FROM Table1
UNION ALL
SELECT [Text] FROM Table2;

(I hope the field is not actually named Text, since that's a reserved word and
will cause errors).

Then create a Totals query based on uniAllText:

SELECT [Text], Count(*) AS CountOfText
FROM uniAllText;

You can use a Subquery to do it all in one step if you prefer:

SELECT [Text], Count(*) AS CountOfText
FROM (SELECT [Text] FROM Table1
UNION ALL
SELECT [Text] FROM Table2);

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com