count rows of union query

[pavlos]

Hi,

I use a union query as follows

SELECT ALL [A],
FROM [Εμβόλια παιδιών] WHERE >"0"

UNION ALL SELECT [C], [D]
FROM [Εμβόλια παιδιών] WHERE [D]>"0"

UNION ALL SELECT [E], [F]
FROM [Εμβόλια παιδιών] WHERE [F]>"0";

When the query runs I need to have the numbers of resulted rows (if possible only of the second column) in a text box on my form. How can I implement it by avoiding the use of another select query?

Thanks in advance!

 

[pbaldy]

You could use a DCount() function.

 

[pavlos]

Thanks pbably,

You mean to use the Dcount in the union query? If yes what's the syntax?

 

[Atomic Shrimp]

Alternatively, you could normalize your database so that you don't have similar data in multiple different columns A, C, E and b, D, F

 

[pavlos]

Alternatively, you could normalize your database so that you don't have similar data in multiple different columns A, C, E and b, D, F

Still I would need to count the number of rows...

 

[pavlos]

Ok, I did it guys...

When the mind is not clear easy things seems difficult.

Thanks a lot for helping!

 

[pbaldy]

Not in the query, as the control source of the textbox. The syntax should be covered in Help. If not, let me know.

 

[pavlos]

Not in the query, as the control source of the textbox. The syntax should be covered in Help. If not, let me know.

Thank you very much pbably! I appreciate your help!!

 

[Atomic Shrimp]

Still I would need to count the number of rows...

That's true. I didn't mean to come across as unhelpful, but on re-reading, I'm sure I didn't really help - sorry about that.

What I meant to say was that normalizing the table so as to be narrow and long rather than short and wide, where possible and appropriate, will make it easier to count things, because it can be done with a single query containing multiple AND/OR-type criteria - and it will be easier to carry off whether it's done with a DCount, or a query.

 

[pbaldy]

For the record, I agree that the question implies a normalization issue. If you have fields like "Product1", "Product2" etc then you should address the table design. UNION queries are great tools, but often they are used to put incorrectly stored data into proper format, and in such cases the table design itself should be changed if possible.

 

[pavlos]

Atomic and Paul you are absolutely correct! Indeed, at some point I must look again the table design in order to achieve better normalization of the db.

Many thanks guys!