How to create a report based on multiple queries

[davidstevans]

Hi all,

I am trying to create a report based on 30 queries . These 30 queries
are based on one table. When I use the Report wizard and select more
than one query I get the message " You have chosen fields from record
source which the wizard can't connect. You may have chosen fields from
a table and from a query based on that table. If so try choosing
fields from only tha table or only the query".

I do not know what to do.? Any guidance will help

thanks,


Dave

 

[John W. Vinson]

Hi all,

I am trying to create a report based on 30 queries . These 30 queries
are based on one table. When I use the Report wizard and select more
than one query I get the message " You have chosen fields from record
source which the wizard can't connect. You may have chosen fields from
a table and from a query based on that table. If so try choosing
fields from only tha table or only the query".

I do not know what to do.? Any guidance will help

thanks,


Dave

I think you're on the wrong track. Do you want thirty different sections of
the report, one for each query? How do the queries differ?


John W. Vinson [MVP]

 

[davidstevans]

I think you're on the wrong track. Do you want thirty different sections of
the report, one for each query? How do the queries differ?

John W. Vinson [MVP]

John,

Queries are like below:

SELECT Min(MailDate) FROM YourTable

2, What is the oldest maildate with an operator assigned to it.

SELECT Min(MailDate) FROM YourTable WHERE Operator is Not Null

3, What is the oldest maildate without an operator assigned to it

SELECT Max(MailDate) FROM YourTable WHERE Operator is Null

4, What is the oldest maildate for the current year

SELECT Min(MailDate) FROM YourTable WHERE [Year] = "07"

5,What is the oldest maildate for the prior years(anything that is not
07)

6,What is the oldest maildate where data in CURRENT starts with 714.

SELECT Min(MailDate) FROM YourTable WHERE [CURRENT] Like "714*"

7, How many records are between 0 to 30 days with an operator.

SELECT Count(*) FROM YourTable WHERE [Days] Between 0 and 30
and
Operator is not Null

Vary the criteria for 8 to 16

18, How many records are between 150 & older without an operator
assigned to it

SELECT Count(*) FROM YourTable WHERE [Days] >=150 and Operator
is
Null

27. Percentage of data starting with 714 in the CURRENT column
againist all other data in the same column.

SELECT Count(IIF(Current Like "714*",1,Null))/ Count(IIF(Current
Not
Like "714*",1,Null)) as result FROM YourTable

28, Top 3 error codes for data starting with 714 in the CURRENT
column

SELECT Top 3 Error1 FROM YourTable WHERE Current like "714*" GROUP
BY
Error1 ORDER BY Count(Error1)

 

[John W. Vinson]

Queries are like below:

Then you need ONE query or a very few queries, not thirty.

You're hardcoding the criterion in each query. That's useful when the criteria
are static and you don't need to choose them, but in this case, you do!

I don't have time to go through all thirty queries suggesting changes, but
consider having an unbound Form, frmCrit, with combo boxes and/or textboxes
for the various values that you'll be using for your searches. Instead of

6,What is the oldest maildate where data in CURRENT starts with 714.

SELECT Min(MailDate) FROM YourTable WHERE [CURRENT] Like "714*"


you could have a single query

SELECT Min([MailDate]) FROM YourTable WHERE
([Year] <> [Forms]![frmCrit]![txtExcludeYear] OR
[Forms]![frmCrit]![txtExcludeYear] IS NULL
AND [Current] LIKE [Forms]![frmCrit]![txtCurrent] & "*";

Building this query will be some work... but far less than trying to
accommodate thirty different queries on one report!


John W. Vinson [MVP]