Power Query missing Access Queries

[tony]

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

 

[alansidman]

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.

 

[tony]

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?

 

[alansidman]

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.

 

[tony]

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?

 

[alansidman]

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.

 

[tony]

Removing the IN didn't work. I guess I'll just use a table as the data source. Thanks for your help

 

[Bob Phillips]

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

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.

 

[Bob Phillips]

I even added a Group By, just in case, but that was no problem either.

 

[cyborgski]

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.