Dynamic search of Many-to-Many Relationship with List that aggregates data (1 Viewer)

jmt90404

Registered User.
Local time
Today, 06:07
Joined
Mar 24, 2018
Messages
29
I'll preface this by saying Im a novice at access.
I have a query with 5 tables:
  1. Deal Table
  2. Property Table
  3. Client Table
  4. Junction Table between Deals and Property
  5. Junction Table between Deals and Clients
I want to present a list of all deals on a form. Some deals will include multiple properties and/or multiple clients, but the list only need to show the first one of each if there are multiples. The list items don't need to be edited or added to on this form. I just want to present the data. I also want to have a dynamic search/filter field that limits the deals shown on the list, but will also filter based on the clients or properties that are not shown on the list (e.g., the 2nd client or property in the deal). How can I accomplish this?

I only want each deal to show up once on the list, not multiple times for each iteration of the deal/property/client relationship. I currently have a datasheet subform with a totals query as the source and a filter field on the main form that is updated on the key up event. The totals query is giving me some issues, but the bigger issue is that I can’t search/filter on the 2nd, 3rd, etc. clients or properties that aren’t shown on the list but are still part of the deal that is shown.
 

plog

Banishment Pending
Local time
Today, 05:07
Joined
May 11, 2011
Messages
11,611
The best way to communicate data needs is via data. So please post 2 sets:

A. STarting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect your query to produce when you feed it the data in A.

Again, 2 sets of data.
 

jmt90404

Registered User.
Local time
Today, 06:07
Joined
Mar 24, 2018
Messages
29
Here is a striped down version of my database. One additional issue I have is that there are situations where I have a deal and a property but not a client attached to the deal yet (or a deal and a client, but no property). Right now the deal would not show up unless all 3 are present, but I would still want the deal to show up.
 

Attachments

  • Test DB.accdb
    792 KB · Views: 124

jmt90404

Registered User.
Local time
Today, 06:07
Joined
Mar 24, 2018
Messages
29
I would expect the following results on the Client List page, but the filter bar to include any deals that have been aggregated below. For example if I type Jason into the search/filter field, then I would want to see Loan No 33333333 show up as Jason is associated with that deal.

NameAddress/DealLoan NoLoan AmountBorrower
Jonathan Smith2 Valley Drive, Sherman Oaks
12345678​
$500,000.00​
Property LLC
Jonathan Smith1 Ocean Drive, Santa Monica
22222222​
$800,000.00​
Assets LLC
Gilbert Wong6 Mountain Drive, Denver
33333333​
$1,500,000.00​
Mom & Pop LLC
1 Ocean Drive, Santa Monica
$3,000,000.00​
 

plog

Banishment Pending
Local time
Today, 05:07
Joined
May 11, 2011
Messages
11,611
First, there is no order in a table. So when you say you want the "first" name on a deal it doesn't mean anything unless you say how to determine "first". I defined first as the one that appears alphabetically first. Which means on Loan No=12345678 I got "Daniel King" for the Name because that is before "Johnathan Smith". Same for the address, so my results don't match yours but produces the correct results.

I have re-attached your database and the query called Main achieves what you want. To make it I had to create 2 sub queries (sub1, sub2) which is where I determined the first name and first address.

Let me know if it works as you need, if not let me know how it doesn't work, preferably with example data.
 

Attachments

  • TestBack.accdb
    792 KB · Views: 104

jmt90404

Registered User.
Local time
Today, 06:07
Joined
Mar 24, 2018
Messages
29
The result of the query is correct but the search/filter field I created on the DealList_frmMain form doesn't work once I connect the query as the source and correct the formatting errors (since the columns in your queries are different then mine. Maybe what I want cant be done, I don't know. I wanted to be able to filter the deals on the form by any info that had been aggregated into the results even if the info I am filtering by isn't visible.
 

plog

Banishment Pending
Local time
Today, 05:07
Joined
May 11, 2011
Messages
11,611
When I open your form it shows 3 items. When I type in data that matches on the form it filters correctly. I don't understand.

Can you give me an example of what data you type in and it doesn't work?
 

jmt90404

Registered User.
Local time
Today, 06:07
Joined
Mar 24, 2018
Messages
29
I apologize if I'm not explaining this clearly. What I am looking to accomplish is a form (currently my DealList_frmMain form) that lists all the deals in the database and can be filtered from a universal dynamic filter field, which I currently have. I also want the form via the query to aggregate all the properties or clients than are part of one deal into a single line (and thereby not showing the additional property or clients), which you have done (thanks for that!). But I still want the filter field to work with those properties or clients that are not visible. In my example expected results above, typing in the name Jason should pull up the deal that shows Gilbert Wong (loan no 33333333), since Jason is also a client on that same deal.

I have an idea in theory but not sure how I can go about accomplishing it or if its even possible. Can I create a sub query that concatenates all of the names of the clients into a single field (and likewise for property addresses), that I don't show on the form, but can filter against?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:07
Joined
Feb 19, 2002
Messages
42,970
You are trying to show too much information on one form. The particular problem is the two 1-m relationships which are causing "duplication" because they create a Cartesian product.

A question and it's critical. Do you want the data on this form to be updateable? Because if you do, you will have to create a more normalized view since in order to resolve your problem, you first need to create queries on the many-side tables that return one and only one row for each instance of the client. Then you can join the client to these queries and the result will be one row per client. HOWEVER, the recordset will not be updateable and that's why I asked the question.
 

jmt90404

Registered User.
Local time
Today, 06:07
Joined
Mar 24, 2018
Messages
29
I actually don't want the data on this form to be updatable.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:07
Joined
Feb 19, 2002
Messages
42,970
Then create the two queries I suggested that pick up the single instance you want and join to them. Once all the relationships are 1-1, there will not be any duplication.
 

Users who are viewing this thread

Top Bottom