Coding an External Query into a Query

[RichKorea]

I have a table of service engineers that I’m matching up against a table of
telephone calls for generating a monthly report. I’m using an outer join so
that I can report all of the engineers, even if they have zero calls (thanks
Piet Linden for the advice)

Table_Enginner Table_Calls
Engineer_ID Call_ID
Name Engineer_ID
CallType
CallDate

In order to capture just the calls associated with a specific month, I have
a couple of date text boxes on the report form, and currently I have a
stand-alone query QRY_Calls that returns all of the calls within a certain
data range, and then I have a query created in the report form that then
joins the Table_Engineer with the QRY_Calls to generate this month’s list of
call data.

I’m trying to avoid creating stand alone queries filling up the database
window, if possible, so I’d like to code the query I’m joining to, rather
then using a separate query.

The first query to get the calls is SELECT the call info FROM Table_Calls
WHERE the date’s within some range:
SELECT Table_Calls.Engineer_ID, Table_Calls.Call_Type, Table_Calls.Call_Date
FROM Table_Calls
WHERE (((Table_Calls.Call_Date)>=[Forms]![ReportForm]![txtStart] And
(Table_Calls.Call_Date)<=[Forms]![ReportForm]![txtEnd]));

The second query to match up the calls is SELECT the call info FROM
Table_Engineer LEFT JOIN QRY_Calls ON the Engineer_ID’s matching:
SELECT Table_Engineer.Name, QRY_Calls.Call_Type
FROM Table_Engineer LEFT JOIN QRY_Calls ON Table_Engineer.Engineer_ID =
QRY_Calls.Engineer_ID;

Question: Rather then LEFT JOIN to a query, can I replace QRY_Calls with
the code for that query? I tried (query code) as T, but kept getting error
messages.

Thanks,
Rich

 

[pietlinden]

I have a table of service engineers that I’m matching up against a tableof
telephone calls for generating a monthly report.  I’m using an outer join so
that I can report all of the engineers, even if they have zero calls (thanks
Piet Linden for the advice)

Table_Enginner          Table_Calls
Engineer_ID                Call_ID
Name                           Engineer_ID
                                     CallType
                                     CallDate

In order to capture just the calls associated with a specific month, I have
a couple of date text boxes on the report form, and currently I have a
stand-alone query QRY_Calls that returns all of the calls within a certain
data range, and then I have a query created in the report form that then
joins the Table_Engineer with the QRY_Calls to generate this month’s list of
call data.

I’m trying to avoid creating stand alone queries filling up the database
window, if possible, so I’d like to code the query I’m joining to, rather
then using a separate query.

The first query to get the calls is SELECT the call info FROM Table_Calls
WHERE the date’s within some range:
SELECT Table_Calls.Engineer_ID, Table_Calls.Call_Type, Table_Calls.Call_Date
FROM Table_Calls
WHERE (((Table_Calls.Call_Date)>=[Forms]![ReportForm]![txtStart] And
(Table_Calls.Call_Date)<=[Forms]![ReportForm]![txtEnd]));

The second query to match up the calls is SELECT the call info FROM
Table_Engineer LEFT JOIN QRY_Calls ON the Engineer_ID’s matching:
SELECT Table_Engineer.Name, QRY_Calls.Call_Type
FROM Table_Engineer LEFT JOIN QRY_Calls ON Table_Engineer.Engineer_ID =
QRY_Calls.Engineer_ID;

Question:  Rather then LEFT JOIN to a query, can I replace QRY_Calls with
the code for that query?  I tried (query code) as T, but kept getting error
messages.

Thanks,
Rich

Rich,

One thing you can do that's really easy (and more flexible) is to
create the filter for your report on the fly (in the code you use to
open the report).

'declare a string variable to hold your filter
Dim strFilter As String

' just to make the filter syntax easier to read...
Const cQUOTE = " ' " ' extra spaces are there to show it's a
single quote... they should be removed


'build your filter
strFilter = "[FieldInReportRecordsource]=" & cQUOTE & Forms!SomeForm!
TextControl & cQUOTE

' open the report passing in your filter
DoCmd.OpenReport "MyReport",,, strFilter

 

[RichKorea]

I have a table of service engineers that I’m matching up against a table of
telephone calls for generating a monthly report. I’m using an outer join so
that I can report all of the engineers, even if they have zero calls (thanks
Piet Linden for the advice)

Table_Enginner Table_Calls
Engineer_ID Call_ID
Name Engineer_ID
CallType
CallDate

In order to capture just the calls associated with a specific month, I have
a couple of date text boxes on the report form, and currently I have a
stand-alone query QRY_Calls that returns all of the calls within a certain
data range, and then I have a query created in the report form that then
joins the Table_Engineer with the QRY_Calls to generate this month’s list of
call data.

I’m trying to avoid creating stand alone queries filling up the database
window, if possible, so I’d like to code the query I’m joining to, rather
then using a separate query.

The first query to get the calls is SELECT the call info FROM Table_Calls
WHERE the date’s within some range:
SELECT Table_Calls.Engineer_ID, Table_Calls.Call_Type, Table_Calls.Call_Date
FROM Table_Calls
WHERE (((Table_Calls.Call_Date)>=[Forms]![ReportForm]![txtStart] And
(Table_Calls.Call_Date)<=[Forms]![ReportForm]![txtEnd]));

The second query to match up the calls is SELECT the call info FROM
Table_Engineer LEFT JOIN QRY_Calls ON the Engineer_ID’s matching:
SELECT Table_Engineer.Name, QRY_Calls.Call_Type
FROM Table_Engineer LEFT JOIN QRY_Calls ON Table_Engineer.Engineer_ID =
QRY_Calls.Engineer_ID;

Question: Rather then LEFT JOIN to a query, can I replace QRY_Calls with
the code for that query? I tried (query code) as T, but kept getting error
messages.

Thanks,
Rich

Rich,

One thing you can do that's really easy (and more flexible) is to
create the filter for your report on the fly (in the code you use to
open the report).

'declare a string variable to hold your filter
Dim strFilter As String

' just to make the filter syntax easier to read...
Const cQUOTE = " ' " ' extra spaces are there to show it's a
single quote... they should be removed


'build your filter
strFilter = "[FieldInReportRecordsource]=" & cQUOTE & Forms!SomeForm!
TextControl & cQUOTE

' open the report passing in your filter
DoCmd.OpenReport "MyReport",,, strFilter

Works - I created a WhereCondition string and passed that in the
DoCmd.OpenReport, and it does exactly a nice job (without having to have more
queries in the database window.

strWhereCondition = "([Call_Date] >= " & Format(Me.txtStart,
"\#mm\/dd\/yyyy\#") & ") AND ([Call_Date] < " & Format(Me.txtEnd + 1,
"\#mm\/dd\/yyyy\#") & ")"

Thanks for help,
Rich