Many queries rolled into 1 report

[LG]

I have 4 queries from 4 different tables. QRY_ALL_COM, QRY_ALL_GOV,
QRY_MAIL PREP, QRY_RPH
The following are fields that are queried in common TDate, QCP_ID, Count of
Bath_ID. There are some fields with in the queries that are different since
each table varies on differnt data required.
My main objective for a supervisor report is getting per QCP_ID a count of
how many items they completed for a day(s) in each of the 4 queries.
Let me know if you need more data?

[KenSheridan via AccessMonster.com]

You can tack each query together with a set of UNION ALL operations. For the
columns which are not common to each query you can return Nulls in each
separate part of the operation for those queries which don't have the column.
To distinguish between each you can include a constant in each as another
column. If for this example we assume that in addition to the three columns
in common, each query returns one other column (which I'll call ColumnA,
ColumnB, ColumnC and ColumnD for this example, the query would then be:

SELECT "All Com" AS Category,
TDate, QCP_ID, [Count of Bath_ID],
ColumnA, NULL AS ColumnB,
NULL AS ColumnC, NULL AS ColumnD
FROM QRY_ALL_COM
UNION ALL
SELECT "All Gov",
TDate, QCP_ID, [Count of Bath_ID],
NULL, ColumnB, NULL, NULL
FROM QRY_ALL_GOV
UNION ALL
SELECT "Mail Prep",
TDate, QCP_ID, [Count of Bath_ID],
NULL, NULL, ColumnC, NULL
FROM QRY_MAIL PREP
UNION ALL
SELECT "RPH",
TDate, QCP_ID, [Count of Bath_ID],
NULL, NULL, NULL, ColumnD
FROM QRY_RPH;

You can then base a report on the above query, grouping the report by QCP_ID,
then TDate (or vice versa if you want the query to group first by date and
then by QCP_ID per date). The QCP_ID and TDate values can be included in
each group header and the Count of Bath_ID, the Category column and the other
columns specific to each source query in the detail section.

You could of course also join whatever table the QCP_ID columns reference to
the above query and include columns from that table in the report if you
wished.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200908/1

[James A. Fortune]

Ken has shown a very clever way to get results from multiple queries
into a report. Here's another way:

'---Begin module code
Public Function ReturnSQLResult(strSQL As String) As Variant
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount > 0 Then
MyRS.MoveFirst
ReturnSQLResult = MyRS(0)
Else
ReturnSQLResult = Null
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function
'---End module code

Suppose a report is based on qryShippedOrders from tblOrders with
certain criteria. Say,

qryShippedOrders:
SELECT * FROM tblOrders WHERE HasShipped = -1;

Also, I need to have a total for the detail section that provides
additional oblique information from the same table, but with different
criteria than qryShippedOrders. The public function above can be used
in a report's detail section footer textbox as follows (air code):

txtUnshippedCustomerTotal.ControlSource

=ReturnSQLResult("SELECT Sum(OrderTotal) FROM tblOrders WHERE HasShipped
= 0 AND MyHeaderField = " & Chr(34) & HeaderField.Value & Chr(34) & ";")

(perhaps next to txtShippedCustomerTotal based on '=Sum([OrderTotal])')

Note that the SQL expression uses none of the records that were selected
in the base query to obtain the unshipped customer total (perhaps the
report uses something like "CustID = " & txtCustID.Value & ";" instead
of a text field for the header field). Naturally, for a detail within a
detail, textbox information from the outer header would provide an
additional constraint to the WHERE clause.

That example is a little bit contrived because it would not be hard to
put the unshipped order total information into the report's base query,
but it illustrates that any information in any table based on criteria
from the report field values can be plopped onto a report footer without
having to add that table to the base query mix. Perhaps this Control
Source SQL technique can prevent someone with a hirsute base query from
having to complicate it further. The idea is similar to using domain
aggregate functions, but allows for more general and easier to implement
and test SQL expressions to be used instead.

James A. Fortune
MPAP...@FortuneJames.com