asked on

Solution for Filter formula in Excel or Dynamic query in Access

Hi experts,

We have a Query in Access that brings information from multiple tables & Queries together.
But this merge now needs to dynamically narrowed down based on 2 fields/criteria's. The last is then needed in Excel to create reports (pivot tables stuff).

Since Excel doesn't have the easy filter formula like Google Docs. I thought of creating the 2 dynamic filters in Access based on the following YouTube video.
They called it a "Form-Based Parameter Query - Dynamic Queries".
It shows the results just like I want. https://www.youtube.com/watch?v=mnWidUABYdQ

But then I face the following challenge. The results from this "Form based Parameter Query" I automatically want in Excel through a Power Query refresh.
Without the "Form-Based Parameter Query - Dynamic Queries" I was able to bring the data from that query in Excel. But then without the 2 filters.

But since I made the "Form-Based Parameter Query - Dynamic Queries" in Access, Excel Power Query gives error" [Expression.Error] The key didn't match any rows in the table." when refreshing the query. When I want to recreate the Power Query in Excel, it now doesn't show the Query from Access that is attached to the "Form based Parameter Query".

What solutions are there in such situation?

Ryan Chong

But then I face the following challenge. The results from this "Form based Parameter Query" I automatically want in Excel through a Power Query refresh.
Without the "Form-Based Parameter Query - Dynamic Queries" I was able to bring the data from that query in Excel. But then without the 2 filters.

not too sure if I understand fully about your requirement, is that mean once you filtered the records in Access Form, you want the Excel Power Query to be automatically filtered as well?

if yes, in your Access, try to put your filtered data into a static table. And then in your Excel Power Query just refer to that static table instead.

Gilberto Sanches

ASKER

You have it right Ryan.
I have a Excel Power Query connected with the Query in Access.
So based on the results of the query in Access, I want it automatically reflected in Excel.

How can I put the dynamic data in a static table (automatically)?

Ryan Chong

How can I put the dynamic data in a static table (automatically)?

you can't really do it "automatically", as it must be triggered by some "action". In this case, the action is probably the Click event of button "List Operator's Production", but you need to take care of scenarios such as concurrent access, etc since you save the result into a static table (different users could have different selections).

Gilberto Sanches

ASKER

Ryan, I configured the query to make a table. According to this article https://support.office.com/en-us/article/create-a-make-table-query-96424f9e-82fd-411e-aca4-e21ad0a94f1b

When I now run the query, through the FORM parameters, it gives the following error "Calculated columns are not allowed in SELECT INFO statements". I have 2 calculated columns. If I remove these 2 calculated columns from the query, it still gives the same error.

Ryan Chong

Table query doesn't consider as "static table" in this case. The "static table" I'm referring to is a table with data that didn't taking any parameters. You can treat the "static table" as the normal table.

When I now run the query, through the FORM parameters, it gives the following error "Calculated columns are not allowed in SELECT INFO statements". I have 2 calculated columns. If I remove these 2 calculated columns from the query, it still gives the same error.

I probably need to have your db (simplified) with sample data for debugging. can you upload it here?

ste5an

Well, is you question about the entire process or just about Excel automation to refresh the data?

To refresh a certain PowerQuery connection use:

Option Compare Database
Option Explicit

Public Sub UpdatePowerQuery(ByVal CWorkbook As Excel.Workbook, ByVal CConnectionName As String)

  On Local Error GoTo LocalError

  Dim Connection As Excel.WorkbookConnection
  
  Dim RefreshRequired As Boolean
  
  For Each Connection In CWorkbook.Connections
    RefreshRequired = (Connection.Name = CConnectionName)
    If RefreshRequired Then
      Connection.Refresh
      Debug.Print Connection.Name & " refreshed."
      Exit For
    End If
  Next Connection

  Set Connection = Nothing
  Exit Sub
  
LocalError:
  Debug.Print "UpdatePowerQuery()"; Err.Number; Err.Description
  
End Sub

Open in new window

CConnectionName is the friendly connection name.

Gilberto Sanches

ASKER

@Ste5an, it's about having the data automatically prepared in Access, to then also automatically make a report of it in Excel.
The prepared data in Access is currently for all users & production period.
But the Report in Excel need to be per user & production period. That is where I want to use the filter, in Excel or in Access.
In Access I got something like a Filter working, but Excel Power Query can't use that filtered data.
So Ryan is helping with having the filtered data in a way that Excel Power Query can use it.

If the filtering can be done in Excel, it would be better. But then I'd have to create the complicated filter formula for 23 fields.

Gilberto Sanches

ASKER

Ryan, here is a copy of the Access database https://drive.google.com/file/d/1Y3UrjZKBKJErtKxZmNEiK4GiJaYaO2bU/view?usp=sharing

Query - Operators Felling All Details Report 20200112 ; this is the prepared data without filters

Form - List Felling Operator's Production ; this is for filtering
Make Table Query - Operators Felling Details Report 20200112 ; this is the result of the Filters

ste5an

But the Report in Excel need to be per user & production period [..]

Thus you need a Excel template with an active connection to the local user database. Then you disconnect the Excel after the data is pushed into PowerQuery.

Gilberto Sanches

ASKER

Thus you need a Excel template with an active connection to the local user database

,
exactly Ste5an.

I'll create the Excel template based on the way we'll get the data presented.

Ryan Chong

@Gilberto Sanches

I saw your query : Operators Felling Details Report 20200112 is actually trying to create a table called as : Operators Felling Details 20200113

...

INTO [Operators Felling Details 20200113]

FROM Users ...

so, are you trying to make table: [Operators Felling Details 20200113] as "static table" and the one will be shown in Excel PowerQuery as well?

Gilberto Sanches

ASKER

Yes Ryan, am using (Make Table) "Operators Felling Details 20200113" as static table.
And that static table is then being used in Excel Power Query.

Gilberto Sanches

ASKER

Maybe the way I am approaching the result is too complicated.
If there are other simple ways of achieving the same results,

Have a automatically generated list of data, from 3 tables, with all ProductionPeriod & Usernames.
Have that list be filtered based on ProductionPeriod & Username.

I am open to those solutions.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gilberto Sanches

ASKER

Ryan! Your the man!
Where I gave up hope, you brought hope with your solution.
It works like a charm, eventhough I don't understand all the codes yet.

test.dqy works and even a manual made Power Query connection.

When I want to create a new list through the Form filter I get the error that it can't find DeleteTable.
I created the public function: DeleteTable in the same "Click Event Code Builder" of the button "List operator's production".

Where should I create the public function?

Ryan Chong

I created the public function: DeleteTable in the same "Click Event Code Builder" of the button "List operator's production".

you should create the public function in a new Module, not the same place with "Click Event Code Builder" of the button "List operator's production".

Gilberto Sanches

ASKER

Okay, can you show me the steps or a reference online to creating that public function? & how do I delete the previous one?
I did create the public function as a new module by the way,

Gilberto Sanches

ASKER

to create the Public function I went to:
Ribbin menu -> Database -> Visual Basic -> Deleted the previous module without exporting it. Now the code is gone, but the name still exists.
I created a new module, pasted the code.
When I close Access, Access asks if I want to save changes to Module1. If yes, it asks for a name. That is how that module received the name previously.

Gilberto Sanches

ASKER

I created the Public Function as you suggested in a new module. I receive the same error
"Compile Error : Expected variable or procedure, not module"

Gilberto Sanches

ASKER

I think that this is the step where I went wrong

3. Open your form : "List Felling Operator's Production" in Design Mode

change the button: "List operator's production" 's OnClick event from Macro Builder to Code Builder, and insert the following codes:

When I open the button properties in Design view, s OnClick event shows me the following options: Embedded Macro or Event Procedure.

When I press ... on the Embedded Macro I get the following window

Earlier I had choosen for "Event Procedure" and then on ... of the "Event Procedure" and then got the window "Visual Basic window."
There I inserted the code. After that created a new module for the public function & gave it the name "DeleteTable"

When I click on the "List Operator's Production" button, I then get the following error

Gilberto Sanches

ASKER

Ahaha, you won't believe what the solution was. I deleted the line

Dim SQL As String
    'Drop tmp table
    Call DeleteTable("tbl_tmp_ExcelPowerQuery")

Open in new window

So the only code on the button is

Private Sub List_operator_s_production___Command10_Click()

    'Reinsert tmp data
    DoCmd.SetWarnings False
    SQL = "Select * into tbl_tmp_ExcelPowerQuery from [Operators Felling Details Report 20200112]"
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    
    'Open result
    DoCmd.OpenQuery "Operators Felling Details Report 20200112", acViewNormal, acEdit

End Sub

Open in new window

It now works awesome. Thanks again Ryan

Ryan Chong

Ok, glad and it seems that you have resolved the issue while I was away

= )

Gilberto Sanches

ASKER

Yeah, cheers