Query and Count tables

[mralmackay]

Would appreciate any help with the following:

I need to able to count, by month, the amount of records for each table
within my database.

The database is made up of the following:
- Approx 60 linked tables (the data is actually linked to folders
within Outlook).
- Field called 'Date Received' is the field to count on.

For each table within the database I would prefer not to have to define
these all and for the query to work on every table that is present?

I would like the output, if possible, to be like below:

Jan Feb Mar
Data1 3 45 54
Data2 43 4 34
Data3 32 41 21
etc....

Is this possible?

Appreciate your help as always.

Thanks, Al.

 

[John Vinson]

Would appreciate any help with the following:

I need to able to count, by month, the amount of records for each table
within my database.

The database is made up of the following:
- Approx 60 linked tables (the data is actually linked to folders
within Outlook).
- Field called 'Date Received' is the field to count on.

For each table within the database I would prefer not to have to define
these all and for the query to work on every table that is present?

I would like the output, if possible, to be like below:

Jan Feb Mar
Data1 3 45 54
Data2 43 4 34
Data3 32 41 21
etc....

Is this possible?

What is the actual structure of your table (or tables)? Are they all
the same, or different? Do you want to get this crosstab (that's what
you'll need) on the concatenation of all sixty linked tables? If so...
it may be difficult; you're very likely to get the dreaded "Query Too
Complex" error.

What I'd try though is to create query based on a query. You'll need
to go to the SQL window and type something resembling (bearing in mind
I don't know your table or fieldnames):

SELECT [Data], Format([Date Received], "mmm") AS Mon
FROM Table1
UNION ALL
SELECT [Data], Format([Date Received], "mmm")
FROM Table2
UNION ALL

<and so on for all 60 tables>

Save this query as uniAllMail; then create a Crosstab query using
[Data] as the row header, [Mon] as the column header, and counting the
records as the value.

You cannot possibly get away from specifying the tables in your query.
The tables are where the data is to be found, and the query must
reference the tables.

John W. Vinson[MVP]