Histogram for fields

[Peter3]

HI

I have one more problem to solve and I would ask You for help.

I Have e table:

Name	Age	email
Peter	35	test1@gmail.com
Marc	33	test2@gmail.com
Mike	37	test1@gmail.com
Martin	37	test2@gmail.com
Mike	37	null
Peter	35	test1@gmail.com
Peter	37	test2@gmail.com
Marc	33	test1@gmail.com
Peter	35	null
Martin	35	test2@gmail.com
Mike	37	test1@gmail.com
Peter	35	test2@gmail.com
Mike	37	test1@gmail.com

I have to present a histogram of fields..
Using simple queries i can do i for each fild (field, count(field), group by.... )

Name	N_count
Peter	5
Mike	4
Marc	2
Martin	2

Age	A_count
37	6
35	5
33	2

email	E_count
test1@gmail.com	6
test2@gmail.com	5
null	2

However, the real table has many columns and this method is very inefficient.
I would like the easiest way to get such a result:

Name	N_count		Age	A_count		email	E_count
Peter	5		37	6		test1@gmail.com	6
Mike	4		35	5		test2@gmail.com	5
Marc	2		33	2		null	2
Martin	2

How to achieve it?

[June7]

Why would you try to show this data as a single table? Makes no sense.

What you want cannot be generated with a single query. Sorry, but you will have to do multiple queries. Or build VBA procedure that writes data to a temp table (table is permanent, data is temporary).

[Peter3]

Thank you very much for your answer.

Why would you try to show this data as a single table? Makes no sense.

I am just trying to find the best solution.
As you wrote (I do not know if I understood correctly), i have ways:

1. the simplest - if i have table with 55 columns (example) - to make 55 simple queries, that agregatte data and count it...
It sounds weak.. I thought about the results of all these 55 queries show later on one report, if it can not be done on any one common query.
However, this is a bad solution i think. (you think so too?)

Or build VBA procedure that writes data to a temp table (table is permanent, data is temporary).

THIS is what I thought about, but I'm afraid that I can not help myself because I am a beginner in VBA.
Do you think that this will be a very complicated code?

How should it look like?
One simple query executed in a loop on all columns in the table, returning the result to the temporary table (column by column) + query based on this table temp. table showing the result?
Will VBA be able to do one query on all table columns? Do I have to define in the VBA code as many aggregate queries as there are columns and give their names?
Can you have an example of such a code?

Best Regards
P.

[June7]

Yes, I think the code will be complicated. And no, it won't be one simple query. If one simple query were possible, code would not be needed. There are many examples of code writing data to table but specifics are unique to the requirements. Yours are quite odd. I am not willing to devote time to this effort. You will either have to learn VBA or hire a contractor if you really want to go this route. For the cost involved, might as well do your 55 queries and build a report/subreport arrangement. However, I'm not sure 55 subreports is possible.

[Peter3]

Ok, thanx i will try to do it..

[Peter3]

P.S.

I did it! works fine,

Best Regards!