LG said:
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?
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
(e-mail address removed)
