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

Kamis, 15 Agustus 2013

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
>
>
>