Results 1 to 10 of 10

Thread: Power Query missing Access Queries

  1. #1
    Neophyte tony's Avatar
    Join Date
    Aug 2019
    Posts
    4
    Articles
    0
    Excel Version
    365

    Power Query missing Access Queries



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, new to the forum and new to power query. I am trying to connect to our access database at work and it connects fine but there are lots of queries missing when I try to connect in Excel. Anyone know why this happens. Tried refreshing and still doesn't work.

    TIA

    Using Office 365

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    Are there any queries that have the NZ function in them? Excel and probably PQ do not recognize these functions. If this is the case, then you will have to export the queries from Access to Excel and then upload to PQ.

  3. #3
    Neophyte tony's Avatar
    Join Date
    Aug 2019
    Posts
    4
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by alansidman View Post
    Are there any queries that have the NZ function in them? Excel and probably PQ do not recognize these functions. If this is the case, then you will have to export the queries from Access to Excel and then upload to PQ.
    I don't think so. I'm looking at the SQL code for one of the missing queries and it doesn't have an NZ functions?

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    Additionally, I cannot think of all the Access functions that do not exist in Excel, but that (similar to the NZ function) causes in Excel to not see or be able to access the Access Queries. How many queries are you talking about. Does it make sense to post the SQL statements here for review to see if there are those types of functions that may cause this type of action.

    Also, when replying it is not necessary to "Reply with Quote" unless there are more than one participant responding. I know what I typed. Adding the quotes is only clutter. Just click on Reply.

  5. #5
    Neophyte tony's Avatar
    Join Date
    Aug 2019
    Posts
    4
    Articles
    0
    Excel Version
    365
    here it is.
    SELECT [CALENDAR_YEAR]+IIf([PAY_MONTH]>=4,1,0) AS FISCAL_YR, FASTHR_A_PAYROLLDETAIL.CLASS_CODE, FASTHR_A_PAYROLLDETAIL.DEDUCTION_CODE, Sum(FASTHR_A_PAYROLLDETAIL.naturalamt) AS SumOfnaturalamt
    FROM PAY_NO INNER JOIN FASTHR_A_PAYROLLDETAIL ON PAY_NO.PAYNO = FASTHR_A_PAYROLLDETAIL.PAY_NO
    GROUP BY [CALENDAR_YEAR]+IIf([PAY_MONTH]>=4,1,0), FASTHR_A_PAYROLLDETAIL.CLASS_CODE, FASTHR_A_PAYROLLDETAIL.DEDUCTION_CODE, FASTHR_A_PAYROLLDETAIL.CALENDAR_YEAR, DateSerial([CALENDAR_YEAR],[PAY_MONTH],1), FASTHR_A_PAYROLLDETAIL.CF04_CODE
    HAVING ((([CALENDAR_YEAR]+IIf([PAY_MONTH]>=4,1,0))=[Fiscal Year]) AND ((FASTHR_A_PAYROLLDETAIL.DEDUCTION_CODE) In ("ADD","ADX","CP1","CP2","CP3","CP4","CPP","DES","DEX","EHS","EHX","EI1","EI2","LIF","LIX","MP1","MP2","MSS")) AND ((FASTHR_A_PAYROLLDETAIL.CF04_CODE)="6780"));

    thanks.I'm missing quite a few queries, maybe 20..
    The database is password protected, does that matter?

  6. #6
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    Having done some research on this a while ago, as I indicated earlier, if there is a function in the Access Query that is not compatible with Excel (ie. NZ, etc.) then the query does not appear. A work around that has been suggested is to create a make table query and use that as your data source.

    In the above SQL statement, I think the villain may be the "In" statement. "In" is not a function within Excel. For testing purposes, remove the In statement and see if the query then shows up.

  7. #7
    Neophyte tony's Avatar
    Join Date
    Aug 2019
    Posts
    4
    Articles
    0
    Excel Version
    365
    Removing the IN didn't work. I guess I'll just use a table as the data source. Thanks for your help

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by alansidman View Post
    In the above SQL statement, I think the villain may be the "In" statement. "In" is not a function within Excel.
    I don't think it is the In statement, I have just created an Access query with an In statement and that showed up fine. Remember also that Power Query is not using that query code itself, it lets the database engine handle that. so the In doesn't come near PQ. To illustrate what I mean, my query was a simple AdventureWorks sales query, customers, sales details, product details, and the SQL generated in PQ is as follows

    Code:
    select [$Table].[Title] as [Title],
        [$Table].[FirstName] as [FirstName],
        [$Table].[MiddleName] as [MiddleName],
        [$Table].[LastName] as [LastName],
        [$Table].[SalesOrderLineNumber] as [SalesOrderLineNumber],
        [$Table].[OrderQuantity] as [OrderQuantity],
        [$Table].[UnitPrice] as [UnitPrice],
        [$Table].[SalesAmount] as [SalesAmount],
        [$Table].[EnglishProductName] as [EnglishProductName],
        [$Table].[EnglishProductSubcategoryName] as [EnglishProductSubcategoryName],
        [$Table].[EnglishProductCategoryName] as [EnglishProductCategoryName]
    from [Sales Per Customer] as [$Table]
    so PQ did a simple select (in this case) from the query.

    Odd the query does not show, if there were a problem I would have expected the query to show, but generate an error in the display.

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,663
    Articles
    0
    Excel Version
    O365
    I even added a Group By, just in case, but that was no problem either.

  10. #10
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by tony View Post
    here it is.
    SELECT [CALENDAR_YEAR]+IIf([PAY_MONTH]>=4,1,0) AS FISCAL_YR, FASTHR_A_PAYROLLDETAIL.CLASS_CODE, FASTHR_A_PAYROLLDETAIL.DEDUCTION_CODE, Sum(FASTHR_A_PAYROLLDETAIL.naturalamt) AS SumOfnaturalamt
    FROM PAY_NO INNER JOIN FASTHR_A_PAYROLLDETAIL ON PAY_NO.PAYNO = FASTHR_A_PAYROLLDETAIL.PAY_NO
    GROUP BY [CALENDAR_YEAR]+IIf([PAY_MONTH]>=4,1,0), FASTHR_A_PAYROLLDETAIL.CLASS_CODE, FASTHR_A_PAYROLLDETAIL.DEDUCTION_CODE, FASTHR_A_PAYROLLDETAIL.CALENDAR_YEAR, DateSerial([CALENDAR_YEAR],[PAY_MONTH],1), FASTHR_A_PAYROLLDETAIL.CF04_CODE
    HAVING ((([CALENDAR_YEAR]+IIf([PAY_MONTH]>=4,1,0))=[Fiscal Year]) AND ((FASTHR_A_PAYROLLDETAIL.DEDUCTION_CODE) In ("ADD","ADX","CP1","CP2","CP3","CP4","CPP","DES","DEX","EHS","EHX","EI1","EI2","LIF","LIX","MP1","MP2","MSS")) AND ((FASTHR_A_PAYROLLDETAIL.CF04_CODE)="6780"));

    thanks.I'm missing quite a few queries, maybe 20..
    The database is password protected, does that matter?
    Is [Fiscal Year] an Access parameter? If so, I don't think PQ would support it (how could it). You probably need to put a table in with Fiscal year and change your queries to join on it to filter on Fiscal Year. Alternatively, you could probably pass the fiscal year as a parameter from PQ and remove the [Fiscal Year] parameter from your queries.
    Last edited by cyborgski; 2019-08-22 at 12:59 PM.

Posting Permissions

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