Multiple Queries into One Report (1 Viewer)

[STEVENCV]

Hi,

We have a database that has around 30 queries that are ran on a monthly basis. They all have unique criteria (but there is a crossover of fields that are used), and are all set up so they show only the previous month's data (the date is taken from one date field for some queries, and another date field for others).

We run the queries, and only need the 'count' of number of records that fit the query each month.

I was wondering if it is possible to create a report that will run the queries simultaneously, and show the 'count' of the query results in predetermined fields.

 

[CJ_London]

In your report you can create unbound controls with the following controlsource

=DCount("*","YourQueryName")

 

[STEVENCV]

Thank you for the reply. I am completely new to this, unfortunately, so could do with some "For Dummies" help with this.

If I was to create a brand new report - how would I ensure it counts all the relevant stuff?

Could I create a blank report, add an unbound text box and just use your controlsource above (with my own query name)? Because I just tried that and it gave me an error.

 

[CJ_London]

You need to be more specific

how would I ensure it counts all the relevant stuff?

I don't know without you telling me what the relevant stuff is

Could I create a blank report, add an unbound text box and just use your controlsource above (with my own query name)?

Yes

Because I just tried that and it gave me an error.

There are any number of errors which might apply - it would be extremely helpful if you could tell me what the error is and ideally also the code you used - the chances are it is a typo

 

[STEVENCV]

Sorry for not providing enough information, I will try to explain clearly now.

For now, I am testing this with one specific query to see if I can get a text box on a report to show the count for that query.

My database has two tables, with a one-to-many relationship. One table is called 'Master Client List" and holds client data, and the other is 'calls', of which clients can have multiple calls for various reasons. (A call is when a member of our care staff goes and visits a client, who are elderly).


The first query I am testing shows the following fields:

"ID" from the 'Calls' table
"Date of Call" from the 'Calls' table
"Area" from the 'Master Client List' table (with a criteria of 'Cherwell', which is one of the multiple drop down list options on that field)
"Tenure" from the 'Master Client List' table (with a criteria of 'Private' which is one of two options on the drop down field)
"Call Reason (Client) from the 'Calls' field (with a criteria of 'Fallen', which is one of multiple drop down options).
"Date of Call' from the 'Calls' field (with a criteria of only showing calls from the previous month)

This then gives us a list of all calls in the previous month that list the client as private, the area as Cherwell, and the reason for the call as 'fallen'.

As we have similar queries for each of the 'reason for call', each 'area', each 'tenure' etc, you can see how we collated a long list of queries.

We don't need the actual client data for these queries (and if we did, we could run the individual queries to access that).

All we need is a list of each query in a report, with a count of how many records match each set of specific criteria for each query. All the queries work with "Year(Date())*12+DatePart("m",Date())-1" to only show the previous month's data. So all queries we run in June will only show figures for May, and when we get to July 1st, we will only see June's figures.

Does this make sense, or have I confused matters even more? :-D

 

[CJ_London]

It helps, but you haven't answered the question!

tell me what the error is and ideally also the code you used

Also it would help if you can give me the name of the query you are using for testing purposes

 

[STEVENCV]

Ah sorry, I missed that bit!

I have just tried to recreate it and it seems to have worked this time - so you may have been right about the typo!

Thank you for your help.