create one query with totals from seperate tables

My database contains a lot of information and I would like to make one report
containing statistics from 7 different tables. It is mainly just a count of
rows from each table, so I can see in one report how far I am through the
project eg *150* out of 200 analysed. I want to know how many elements there
are, how many interfaces there are, a count of links- all on one report. I
appreciate your help.

Hi, Ben.

I'm afraid I don't understand based on the information you've given. If
these are related tables, create a query that joins them together in a single
recordset. Then you can do grouping and sorting on it. If you're looking
for summary data, make it a Totals query (View, Totals from query design
view).

For example, if you had a Sales database, and created a query consisting of
a Salesperson, Month, and Amount, and you wanted a report showing the sales
of each person by month, you would list the fields in that order in the
query, select Group By for the first two columns, and Sum for the Amount
column. Access will then calculate a total for each unique combination of
Salesperson and Month.

Hope that helps. If this doesn't get you where you need to be, please post
additional detail regarding your tables and what you'd like your report to
look like, and I'll try again.

Sprinks

"Ben" wrote:

My database contains a lot of information and I would like to make one report
containing statistics from 7 different tables. It is mainly just a count of
rows from each table, so I can see in one report how far I am through the
project eg *150* out of 200 analysed. I want to know how many elements there
are, how many interfaces there are, a count of links- all on one report. I
appreciate your help.

I'm sorry I wasn't clear enough. I am a new starter to Access, so I am a
little in the dark. What I would like to do is create a summary report. I
want this report to list different fields and a count of the occupied rows in
that field. For example, in the report I would like:

Number of interfaces: XXXX (this would come from the interface table)
Number of linked interfaces: XXX (this would come from the link table
between Interfaces and data elements)
Number of Data Elements: XXXX (this would come from data elements table)
Number of Source/Target: XXX (this would come from Source/Target table)

So, in short, the total number of records from various tables is what I
would like to show on one report. How can I do this?

"Sprinks" wrote:

Hi, Ben.

I'm afraid I don't understand based on the information you've given. If
these are related tables, create a query that joins them together in a single
recordset. Then you can do grouping and sorting on it. If you're looking
for summary data, make it a Totals query (View, Totals from query design
view).

For example, if you had a Sales database, and created a query consisting of
a Salesperson, Month, and Amount, and you wanted a report showing the sales
of each person by month, you would list the fields in that order in the
query, select Group By for the first two columns, and Sum for the Amount
column. Access will then calculate a total for each unique combination of
Salesperson and Month.

Hope that helps. If this doesn't get you where you need to be, please post
additional detail regarding your tables and what you'd like your report to
look like, and I'll try again.

Sprinks

"Ben" wrote:

My database contains a lot of information and I would like to make one report
containing statistics from 7 different tables. It is mainly just a count of
rows from each table, so I can see in one report how far I am through the
project eg *150* out of 200 analysed. I want to know how many elements there
are, how many interfaces there are, a count of links- all on one report. I
appreciate your help.

Hi, Ben.

Since the information is in different, presumably unrelated tables, the
easiest way is probably to use subreports, which are nothing more than a
report dragged and dropped from the database window onto another report in
design view.

For each table, create a subreport. Show the Report Header/Footer, and
place a textbox in the footer with the Control Source set to:

=Count([YourPrimaryKey])

Then begin a blank report, resize the window, and drag and drop the other
reports into it.

Hope that helps.
Sprinks

"Ben" wrote:

I'm sorry I wasn't clear enough. I am a new starter to Access, so I am a
little in the dark. What I would like to do is create a summary report. I
want this report to list different fields and a count of the occupied rows in
that field. For example, in the report I would like:

Number of interfaces: XXXX (this would come from the interface table)
Number of linked interfaces: XXX (this would come from the link table
between Interfaces and data elements)
Number of Data Elements: XXXX (this would come from data elements table)
Number of Source/Target: XXX (this would come from Source/Target table)

So, in short, the total number of records from various tables is what I
would like to show on one report. How can I do this?

"Sprinks" wrote:

Hi, Ben.

I'm afraid I don't understand based on the information you've given. If
these are related tables, create a query that joins them together in a single
recordset. Then you can do grouping and sorting on it. If you're looking
for summary data, make it a Totals query (View, Totals from query design
view).

For example, if you had a Sales database, and created a query consisting of
a Salesperson, Month, and Amount, and you wanted a report showing the sales
of each person by month, you would list the fields in that order in the
query, select Group By for the first two columns, and Sum for the Amount
column. Access will then calculate a total for each unique combination of
Salesperson and Month.

Hope that helps. If this doesn't get you where you need to be, please post
additional detail regarding your tables and what you'd like your report to
look like, and I'll try again.

Sprinks

"Ben" wrote:

My database contains a lot of information and I would like to make one report
containing statistics from 7 different tables. It is mainly just a count of
rows from each table, so I can see in one report how far I am through the
project eg *150* out of 200 analysed. I want to know how many elements there
are, how many interfaces there are, a count of links- all on one report. I
appreciate your help.