ACCESS FEED: RE: [MS_AccessPros] Pass-Through query

Kamis, 15 Agustus 2013

RE: [MS_AccessPros] Pass-Through query

Jim,

BTW: I'm not sure why you included the ACCOUNTS table in the SELECT. There are no fields, criteria, or grouping based on the ACCOUNTS table.

Duane Hookom MVP
MS Access

----------------------------------------
> To: ms_access_professionals@yahoogroups.com
> From: duanehookom@hotmail.com
> Date: Thu, 15 Aug 2013 17:29:48 -0500
> Subject: RE: [MS_AccessPros] Pass-Through query
>
> A few issues pop out:
>
> 1) dbo_ is the prefix given in Access. I expect the server name is actually TRANSACTION_DETAIL_ALL.
>
> 2) Your server has no idea what [Forms] ! [LOCAL_FORM] ! [CURRENT_FISCAL_YEAR] represents. You can't reference Access tables or objects in a P-T. I use a small function to change the SQL property of the P-T so the value of the control on the form is used.
>
> 3) the string delimiter might be a single quote rather than double (at least that's how SQL Server works)
>
> 4) it doesn't make much difference but using a WHERE clause rather than HAVING would be more efficient.
>
> You might want to test something like:
>
> INSERT INTO ONE (
> ACCOUNT
> ,ACCOUNT_CODE
> ,CURRENT_MONTH_ACTUAL
> )
> SELECT TRANSACTION_DETAIL_ALL.ACCOUNT
> ,TRANSACTION_DETAIL_ALL.APPROPRIATION_UNIT_CODE
> ,Sum(TRANSACTION_DETAIL_ALL.[AMOUNT_$])
> FROM ACCOUNTS
> INNER JOIN TRANSACTION_DETAIL_ALL ON ACCOUNTS.ACCOUNT = TRANSACTION_DETAIL_ALL.ACCOUNT
> WHERE
> TRANSACTION_DETAIL_ALL.FISCAL_YEAR = 2013
> AND TRANSACTION_DETAIL_ALL.FISCAL_MONTH = 7
> AND TRANSACTION_DETAIL_ALL.OBJECT_CODE = '7010'
> AND TRANSACTION_DETAIL_ALL.ACCOUNT_TYPE_CODE = '22'
> GROUP BY TRANSACTION_DETAIL_ALL.ACCOUNT
> ,TRANSACTION_DETAIL_ALL.APPROPRIATION_UNIT_CODE
> ,TRANSACTION_DETAIL_ALL.FISCAL_YEAR
> ,TRANSACTION_DETAIL_ALL.FISCAL_MONTH
> ,TRANSACTION_DETAIL_ALL.OBJECT_CODE
> ,TRANSACTION_DETAIL_ALL.ACCOUNT_TYPE_CODE
>
> Duane Hookom MVP
> MS Access
>
>
> ----------------------------------------
>> To: MS_Access_Professionals@yahoogroups.com
>> From: luvmymelody@yahoo.com
>> Date: Thu, 15 Aug 2013 15:02:23 -0700
>> Subject: Re: [MS_AccessPros] Pass-Through query
>>
>> Duane
>>
>> So I have been playing around and I tried this PT and I get an error with ODBC - call failed
>> sybase odbc driver adaptive servicer enterprise incorrect syntax newr '!', &102
>>
>> Below is my sql statement
>>
>> Jim Wagner
>>
>>
>>
>> INSERT INTO ONE (
>> ACCOUNT
>> ,ACCOUNT_CODE
>> ,CURRENT_MONTH_ACTUAL
>> )
>> SELECT dbo_TRANSACTION_DETAIL_ALL.ACCOUNT
>> ,dbo_TRANSACTION_DETAIL_ALL.APPROPRIATION_UNIT_CODE
>> ,Sum(dbo_TRANSACTION_DETAIL_ALL.[AMOUNT_$]) AS [SumOfAMOUNT_$]
>> FROM ACCOUNTS
>> INNER JOIN dbo_TRANSACTION_DETAIL_ALL ON ACCOUNTS.ACCOUNT = dbo_TRANSACTION_DETAIL_ALL.ACCOUNT
>> GROUP BY dbo_TRANSACTION_DETAIL_ALL.ACCOUNT
>> ,dbo_TRANSACTION_DETAIL_ALL.APPROPRIATION_UNIT_CODE
>> ,dbo_TRANSACTION_DETAIL_ALL.FISCAL_YEAR
>> ,dbo_TRANSACTION_DETAIL_ALL.FISCAL_MONTH
>> ,dbo_TRANSACTION_DETAIL_ALL.OBJECT_CODE
>> ,dbo_TRANSACTION_DETAIL_ALL.ACCOUNT_TYPE_CODE
>> HAVING (
>> ((dbo_TRANSACTION_DETAIL_ALL.FISCAL_YEAR) = [Forms] ! [LOCAL_FORM] ! [CURRENT_FISCAL_YEAR])
>> AND ((dbo_TRANSACTION_DETAIL_ALL.FISCAL_MONTH) = [Forms] ! [LOCAL_FORM] ! [CURRENT_FISCAL_MONTH])
>> AND ((dbo_TRANSACTION_DETAIL_ALL.OBJECT_CODE) = "7010")
>> AND ((dbo_TRANSACTION_DETAIL_ALL.ACCOUNT_TYPE_CODE) = "22")
>> );
>>
>> Jim Wagner
>> ________________________________
>>
>>
>>
>> ________________________________
>> From: Duane Hookom <duanehookom@hotmail.com>
>> To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
>> Sent: Wednesday, August 14, 2013 4:41 PM
>> Subject: RE: [MS_AccessPros] Pass-Through query
>>
>>
>>
>>
>> BTW: this is a pass-through I wrote today to determine which factories were missing some required monthly data for reporting purposes. It would have taken hours to create and run this in Access. I get the results in a P-T in a second. The largest table (KPI_Data) has over 200,000 records. This query takes advantage of CTEs which aren't in Access/JET SQL. They are similar to subqueries but much easier to build.
>>
>> WITH
>> cteLR as
>> (
>> -- these are the left KPI from your request
>> SELECT klrLIndCode as KPI
>> FROM KPI_LEFTRIGHT
>> WHERE klrKPI IN ('OSH','NCC','STC','SCF','MSF','CSF','ZPL','FVD')
>> UNION
>> -- these are the right KPI from your request
>> SELECT klrRIndCode
>> FROM KPI_LEFTRIGHT
>> WHERE klrKPI IN ('OSH','NCC','STC','SCF','MSF','CSF','ZPL','FVD')
>> ),
>> cteFac as
>> (
>> -- these are all the factories in the reporting group
>> SELECT fagFacCode FacCode, facFactory
>> FROM KPI_FactoryGroups JOIN KPI_RptFactoryGroups on fagRFGID = rfgRFGID JOIN KPI_Factories on fagFacCode = facCode
>> WHERE fagRFGID = 85 and fagFacCode <>'G85'
>> ),
>> cteMonths AS
>> (
>> -- these are the years and months
>> SELECT DISTINCT kdaYear yr, kdaMonth Mth
>> FROM KPI_Data
>> WHERE kdaYear = 2013 and kdaMonth BETWEEN 1 and 7
>> )
>> , cteCartesian As (
>> -- this is all possible combinations of KPI, Factories, and months
>> SELECT KPI, FacCode, facFactory, Yr, Mth
>> FROM cteLR, cteFac, cteMonths)
>> -- the selects the missing KPI values
>> SELECT FacCode, FacFactory,KPI, indTitle, Yr, Mth, kdaValue
>> FROM cteCartesian
>> JOIN kpi_AllIndicators on KPI = indCode
>> LEFT JOIN kpi_Data on kdaIndCode = KPI AND kdaFacCode = FacCode AND kdaYear = Yr and kdaMonth = Mth
>> WHERE kdaValue is null
>> ORDER BY facFactory, KPI, Yr, Mth
>>
>> Duane
>>
>> ----------------------------------------
>>> To: ms_access_professionals@yahoogroups.com
>>> From: duanehookom@hotmail.com
>>> Date: Wed, 14 Aug 2013 17:03:17 -0500
>>> Subject: RE: [MS_AccessPros] Pass-Through query
>>>
>>> Jim,
>>>
>>> A pass-through is typically much much much more efficient than an Access query.
>>>
>>> P-T queries are read-only which might be a valuable feature.
>>>
>>> A P-T uses the SQL syntax of the database server so functionality like calling stored procedures, using CTEs, recursion, and other cool stuff that isn't available in Access queries.
>>>
>>> P-T queries aren't editable and can't take advantage of data not on your server (without a lot of work). There typically isn't a crosstab equivalent in most databases (again without a lot of work).
>>>
>>> Duane Hookom MVP
>>> MS Access
>>>
>>>
>>> ----------------------------------------
>>>> From: luvmymelody@yahoo.com
>>>>
>>>> Hello,
>>>>
>>>> So I have been researching pass-through queries and I cannot find anything that says why you should create a pass-through query. The only things I find is how to create the query. What is the difference between a pass-through query and a regular linked table?
>>>>
>>>> Thank You
>>>>
>>>> Jim Wagner
>>>
>>> ------------------------------------
>>>
>>> Yahoo! Groups Links
>>>
>>>
>>>
>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>> ------------------------------------
>>
>> Yahoo! Groups Links
>>
>>
>>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>