Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: Convert SQL Query to work with Access

    Please help! I'm not a SQL expert neither I'm an Access expert, I have the below SQL query given by DBA and it works very well in SQL but it doesn't seem to work with Access as it returns Syntax error [attached].

    SELECT DISTINCT
    Device,
    [Policy Instance]

    FROM (

    SELECT
    AllServices.Device,
    [group] ,
    [Policy Instance],
    ISNULL(PolicyCount, 0) AS PolicyCount,
    ISNULL(WaveMember, 0) AS WaveMember

    FROM AllServices LEFT JOIN ( SELECT DISTINCT
    device,
    count([group]) AS PolicyCount
    FROM AllServices
    WHERE [group] LIKE 'CN_DSP_CA_%' OR [group] LIKE 'CN_DPT_CA%'
    GROUP BY device ) CentralisedGroupCount ON
    AllServices.Device = CentralisedGroupCount.Device LEFT JOIN ( SELECT DISTINCT Device,1 AS WaveMember
    FROM dbo.AllServices
    WHERE [group] LIKE 'CN_DPT_CA%WAVE_[0-9]') WaveGroup ON
    dbo.AllServices.Device = WaveGroup.Device


    ) A
    WHERE
    A.WaveMember = 0
    AND PolicyCount < 10


    ORDER BY
    device,
    [Policy Instance]


    I would need to make this work against a Access table as most of the work will be done on the Access files.

    Appreciate if someone can help with this.

    Below is the sample date in the table AllServices

    Device Group Policy Domain Policy Instance
    APACABC12345 Grp_DSP_CA_CAAGENTUPGRD_78_01 XXLB001 CAAGENTUPGRD_78_01
    APACABC12346 Grp_DSP_CA_OFFICECOM_2007_01_MP_01 XXLB001 OFFICECOM_2007_01
    APACABC12347 Grp_DSP_CA_OFFICECOM_2007_01_MP_01 XXLB001 HF_OFFICECOM2007_00
    APACABC12348 Grp_DSP_CA_OFFICECOM_2007_01_MP_01 XXLB001 HF_KB2405042_00
    APACABC12349 Grp_DPT_CA_OFFICEWEBCOMPONENT_2003_01 XXLB001 OFFICEWEBCOMPONENT_2003_01
    Attached Thumbnails Attached Thumbnails Capture.JPG  
    Last edited by shameem2k; 02-13-12 at 11:27. Reason: Error Screenshot attached

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it would make things ~so~ much easier if you observed some conventions for indentation, so that the structure of the subqueries became more readily discernible

    here's your query, reformatted --
    Code:
    SELECT DISTINCT
           Device
         , [Policy Instance]
      FROM ( SELECT AllServices.Device
                  , [group] 
                  , [Policy Instance]
                  , ISNULL(PolicyCount, 0) AS PolicyCount
                  , ISNULL(WaveMember, 0) AS WaveMember
               FROM AllServices 
             LEFT 
               JOIN ( SELECT DISTINCT 
                             device
                           , count([group]) AS PolicyCount 
                        FROM AllServices
                       WHERE [group] LIKE 'CN_DSP_CA_%' 
                          OR [group] LIKE 'CN_DPT_CA%'
                      GROUP 
                          BY device ) CentralisedGroupCount 
                 ON AllServices.Device = CentralisedGroupCount.Device 
             LEFT 
               JOIN ( SELECT DISTINCT 
                             Device
                           , 1 AS WaveMember 
                        FROM dbo.AllServices 
                       WHERE [group] LIKE 'CN_DPT_CA%WAVE_[0-9]' ) WaveGroup 
                 ON dbo.AllServices.Device = WaveGroup.Device
           ) A
     WHERE A.WaveMember = 0
       AND PolicyCount < 10
    ORDER 
        BY device
         , [Policy Instance]
    here's your query with my suggested changes --
    Code:
    SELECT DISTINCT
           Device
         , [Policy Instance]
      FROM ( SELECT dbo.AllServices.Device
                  , dbo.AllServices.[group] 
                  , dbo.AllServices.[Policy Instance]
                  , ISNULL(PolicyCount,0) AS PolicyCount
                  , ISNULL(WaveMember,0) AS WaveMember
               FROM (
                    dbo.AllServices 
             LEFT 
               JOIN ( SELECT device
                           , COUNT([group]) AS PolicyCount 
                        FROM dbo.AllServices
                       WHERE [group] LIKE 'CN_DSP_CA_%' 
                          OR [group] LIKE 'CN_DPT_CA%'
                      GROUP 
                          BY device ) CentralisedGroupCount 
                 ON CentralisedGroupCount.Device = dbo.AllServices.Device
                    ) 
             LEFT 
               JOIN ( SELECT DISTINCT 
                             Device
                           , 1 AS WaveMember 
                        FROM dbo.AllServices 
                       WHERE [group] LIKE 'CN_DPT_CA%WAVE_[0-9]' ) WaveGroup 
                 ON WaveGroup.Device = dbo.AllServices.Device
           ) A
     WHERE WaveMember = 0
       AND PolicyCount < 10
    ORDER 
        BY Device
         , [Policy Instance]
    i have marked in red the changes that i believe are necessary to fix your error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. In a SQL Server procedure (T-SQL) the ISNULL function is the equivalent of the Nz function in Access. This in T-SQL:
    Code:
    ISNULL(PolicyCount, 0) AS PolicyCount,
    ISNULL(WaveMember, 0) AS WaveMember
    becomes in Access:
    Code:
    Nz(PolicyCount, 0) AS PolicyCount,
    Nz(WaveMember, 0) AS WaveMember
    2. In T-SQL the percent character (%) is a wildcard. The equivalent is Access is the star character (*) This in T-SQL:
    Code:
    WHERE [group] LIKE 'CN_DSP_CA_%' OR [group] LIKE 'CN_DPT_CA%'
    becomes in Access:
    Code:
    WHERE [group] LIKE 'CN_DSP_CA_*' OR [group] LIKE 'CN_DPT_CA*'
    3. You don't use a schema prefix (here: dbo.) in Access. This in T-SQL:
    Code:
    FROM dbo.AllServices 
    WHERE [group] LIKE 'CN_DPT_CA%WAVE_[0-9]') WaveGroup ON
    dbo.AllServices.Device = WaveGroup.Device
    becomes in Access:
    Code:
    FROM AllServices 
    WHERE [group] LIKE 'CN_DPT_CA*WAVE_[0-9]') WaveGroup ON
    AllServices.Device = WaveGroup.Device
    4. This type of JOIN operation does not seem to be supported in Access (Error message: "Join expression not supported"). You can try combining several queries instead of using subqueries:
    a) Query 'CentralisedGroupCount':
    Code:
    SELECT DISTINCT device, count([group]) AS PolicyCount
    FROM AllServices
    WHERE [group] LIKE 'CN_DSP_CA_%' OR [group] LIKE 'CN_DPT_CA%'
    GROUP BY device;
    b) Query 'WaveGroup':
    Code:
    SELECT DISTINCT Device, 1 AS WaveMember
    FROM AllServices
    WHERE [group] LIKE 'CN_DPT_CA%WAVE_[0-9]';
    c) Query 'A':
    Code:
    SELECT AllServices.Device, 
           AllServices.Group, 
           AllServices.[Policy Instance], 
           Nz(CentralisedGroupCount.PolicyCount,0) AS PolicyCount, 
           Nz(WaveGroup.WaveMember,0) AS WaveMember
    FROM  ( AllServices LEFT JOIN CentralisedGroupCount ON AllServices.Device = CentralisedGroupCount.device
          ) LEFT JOIN WaveGroup ON AllServices.Device = WaveGroup.Device;
    d) The final query:
    Code:
    SELECT A.Device, 
           A.[Policy Instance]
    FROM   A
    WHERE (A.WaveMember = 0) AND 
          (A.PolicyCount < 10)
    ORDER BY A.Device, A.[Policy Instance];
    5. I don't fully understand what data set this query is supposed to yield but I'm almost sure there's a better and easier way to get the expected result.
    Have a nice day!

  4. #4
    Join Date
    Feb 2012
    Posts
    3
    @r937 - Thanks mate, but when I execute your reformatted query, it returns Circular Reference Caused by Alias error. I'm totally blank about how I fix that I cannot understand a thing about this SQL query.

    @Sinndho - I could make corrections to what you have suggested in points 1-3, but point 4 I'm afraid it totally blew over my top. Apologies for that as I have no clue how I can combine the queries you have mentioned.

    The data set should return me the distinct device and the Policy instance and exclude those devices where the Policy Instance mapped to the group name does not have have 'CN_DSP_CA_' or CN_DPT_CA' string..and so on..

    Please can you help me with the query, if you would like I can send you an excel sheet with the complete data from the table and the query result from SQL to your mail id.

    I'm desperately looking for a solution as I have limited or no knowledge about SQL or Access and I would need to get the output sent to a team for processing by today.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply create the different queries and save them under the names I provided. The original query you posted becomes what I named "the final query".
    Have a nice day!

  6. #6
    Join Date
    Feb 2012
    Posts
    3
    Thanks Sinndho,

    The query you provided works perfectly fine, I did exactly what you asked me to and runnig the final query returned me the results.

    Please can you let me know how I can combine all these different queries into one, the reason being that we receive this AllServices table as an Excel sheet and we import this excel sheet into SQL server and execute the original query.

    Part of my team works in Singapore, due to network latency issues they are having difficulty in connecting to the SQL server and execute the query as the server is based in London.

    For this reason we thought of importing this Excel file into Access DB and run the query but my managment want this to be automated as it is daily task and very critical.

    I created a VbScript which will read the excel sheet using Jet DB engine, execute this single query and display the results in a new excel sheet. This is where I'm stuck as the entire query must be passed as single argument and I'm unsure how I can work with multiple saved queries?

    Please advise.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know, you can't. The problem is that although such a query is valid when used on a SQL Server, it uses JOIN operations that Access cannot handle.

    If you intend to import a local copy of the data into an Access database, I don't see why you could not use a combination of several queries to retrieve the result.

    You do not need to pass references to several queries: just issue the last one. It will work provided that the "intermediate" queries are stored into the database. If you recreate a new database every time you want to import a new set of data, you can create an automatic procedure to recreate the queries.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •