Filtering data to match requirements (1 Viewer)

aark

New member
Local time
Today, 05:38
Joined
May 24, 2016
Messages
1
Hello

I have a table with 40,000+ transactions and I wish to filter them using several criteria. The transactions have approx. 30 fields and I wish to use 4 of them to filter the transactions.

- My goal is to end up with a table of transactions that fit all 4 of my criteria.

The 4 fields that I wish to filter them by are:

- Product type (3 acceptable values)
- ISIN (100,000+ acceptable values)
- Counterparty code (30+ acceptable values)
- Trader (1000 acceptable values)

For each I field that I wish to filter by, I have a separate table which lists all the values that I want to pass the filter.

- desired result:

1 list of transactions that have values in the 4 filter fields that match values in the other tables.

Does anyone know the best way to go about this?

Many thanks

Aark
 

plog

Banishment Pending
Local time
Today, 00:38
Joined
May 11, 2011
Messages
11,613
You need to bring your 5 tablesinto your query (Transaction Table and 4 filter tables). Link the filter tables to the Transaction table field they are to filter and then bring down all the fields from Transactions you want to show. Run that query and there's your data.
 

informer

Registered User.
Local time
Today, 06:38
Joined
May 25, 2016
Messages
75
Hi aark,

Spontaneously, I'll create 4 queries, each of them based on transactions table.

qry1 = 'SELECT [Product type] FROM [transactions table]'
qry2 = 'SELECT [ISIN] FROM [transactions table]'
qry3 = 'SELECT [Counterparty code] FROM [transactions table]'
qry4 = 'SELECT [Trader] FROM [transactions table]'

Create 4 listboxes and set up their RowSource as follows
listBox1.RowSource= qry1
listBox2.RowSource= qry2
listBox3.RowSource= qry3
listBox4.RowSource= qry4

Add a button Filter which calls on clickOn event filter VBA command
sCriteria = "1 = 1 " --> ovoid to manage AND
sCriteria = sCriteria & iif(listbox1.value<>"", " AND [Product type] = " & listbox1.value,"") & iif(listbox2.value<>"", " AND [ISIN] = " & listbox1.value,"") ...

Me.filter = sCriteria
Me.filterOn = True

Of course it's possible to create dynamically a SQL query to set to form.recordsource. Proceed with the same method described above to create the dynamic query

;)
 
Last edited:

Simon_MT

Registered User.
Local time
Today, 05:38
Joined
Feb 26, 2007
Messages
2,177
I use a Dialogue Box and then build the Criteria depending if any of the Search Feilds are used. The Last Scheme uses any of the above Criteria to qualify the number of Schemes.

Simon
 

Attachments

  • Schemes_Dialogue.png
    Schemes_Dialogue.png
    37.6 KB · Views: 136

informer

Registered User.
Local time
Today, 06:38
Joined
May 25, 2016
Messages
75
Hi

If there is a reference tables for each listbox, do as follows

qry1 = SELECT [Product type] FROM [Product type Table]
qry2 = SELECT [ISIN] FROM [ISIN table]
qry3 = SELECT [Counterparty code] FROM [Counterparty code table]
qry4 = SELECT [Trader] FROM [Trader table]

Then proceed as described above
 

Users who are viewing this thread

Top Bottom