Creating a report of Metrics

jason.teen

Hi,

I am trying to create a table of Query "Metrics"
ie. I have many tables in a database which most of the time just counts
the number of rows in a certain table, or the number of distinct rows
in a table with some condition.

I'm trying to use the MS Access built in report feature to create one
master "metrics report"
where it will just have sample output such as:

#Rows in Table: 25034
#Distinct Rows: 135
#rows where condition is true: 55

....and so forth.

I can create the seperate queries with no problems. ie. I can create
one query which counts the number of rows in a table, and another for
the distinct rows, and another where the condition is true. but what I
cannot do is put them all on the ONE FINAL REPORT.

If I use the built in wizard, it will be able to create only 1 query in
each report. As the report 'Record Source' is to bound to the Query
Name. whereas I need my report to place the results of all the queries
into just the one table.

Is this possible to do?.. if so, how?

Thanks in advance.

Jun 21 '06 #1

Keith Wilby

<ja********@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

Hi,

I am trying to create a table of Query "Metrics"
ie. I have many tables in a database which most of the time just counts
the number of rows in a certain table, or the number of distinct rows
in a table with some condition.

I'm trying to use the MS Access built in report feature to create one
master "metrics report"
where it will just have sample output such as:

#Rows in Table: 25034
#Distinct Rows: 135
#rows where condition is true: 55

...and so forth.

I can create the seperate queries with no problems. ie. I can create
one query which counts the number of rows in a table, and another for
the distinct rows, and another where the condition is true. but what I
cannot do is put them all on the ONE FINAL REPORT.

If I use the built in wizard, it will be able to create only 1 query in
each report. As the report 'Record Source' is to bound to the Query
Name. whereas I need my report to place the results of all the queries
into just the one table.

Is this possible to do?.. if so, how?

Thanks in advance.

You need to include your three separate queries in another query and base
your report on that.

HTH - Keith.
www.keithwilby.com

Jun 21 '06 #2

jason.teen

Can you please elaborate?

I'm not sure what you mean by including them in a separate query?

perhaps an example. is the SQL then just appended all together?
Keith Wilby wrote:

You need to include your three separate queries in another query and base
your report on that.

HTH - Keith.
www.keithwilby.com

Jun 21 '06 #3

Keith Wilby

<ja********@gmail.com> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Can you please elaborate?

I'm not sure what you mean by including them in a separate query?

perhaps an example. is the SQL then just appended all together?

Save your three queries. Open a new query and click the add table button.
Click the queries tab and select your three saved queries. Drag the fields
from your queries onto the grid. I tried it on a simple example and it
seemed to work OK. No need for any joins.

Regards,
Keith.
www.keithwilby.com

Jun 21 '06 #4

jason.teen

Thank you. Worked like a charm.

but now I want to add in a "Notes/Explanation" Column which I'm finding
hard becuase I know that this report is made up from about 20+ queries,
but I dont want to have to put in 20+ labels when the report is
created.

Right now I have my reporting looking like:

Query1 Result1
Query2 Result2
Query3 Result3

but I need soemthing like:

Query1 Result1 Notes1
Query2 Result2 Notes2
Query3 Result3 Notes3

1) Is it possible to create perhaps a TABLE of my results now rather
than a REPORT of course in an automated process? Either throught VBA or
throught MS Access interfaces
like so:

QueryName | Result | Notes
------------------------------------------------------

so that I can just type it in to the table and then print out the full
results in the end to a report?


Keith Wilby wrote:

<ja********@gmail.com> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Can you please elaborate?

I'm not sure what you mean by including them in a separate query?

perhaps an example. is the SQL then just appended all together?

Save your three queries. Open a new query and click the add table button.
Click the queries tab and select your three saved queries. Drag the fields
from your queries onto the grid. I tried it on a simple example and it
seemed to work OK. No need for any joins.

Regards,
Keith.
www.keithwilby.com

Jun 23 '06 #5