Solved

How to create a query that gives a percent of fields matching data in another table.

Posted on 2015-01-06
3
128 Views
Last Modified: 2015-03-19
I am trying to create a query that will show give me all records where >50% or the child records match the comparison criteria. Specific example: I want to see all loans in which >50% of the collateral properties are in our assessment area. The parent record contains the loan number, which has a one-to-many relationship with the collateral properties. The census tracts of the collateral properties are compared to the census tracts in our assessment area. I want a query that doesn't just give me the First collateral property that matches. I want to see only those where >50% of the total collateral properties of a loan are in the assessment area (have matching census tracts).
0
Comment
Question by:amcpike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40534496
sample data will help.. how about uploading a copy of the db
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40534632
...and post the expected result, based on this data...
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40534745
Interesting.
You'd be looking to do a Count(*) of all properties where a CensusTractID matched a criteria AND a Count(*) of all properties where a CensusTractID  did not match a criteria, and then display all the records where the match count equaled or exceeded the unmatched count.

You could do it in three queries or perhaps a Union query, or subqueries
I'm no SQL guru but something along the lines of:
Select LoanID from
(Select LoanID, CensusTractID as MatchedCollateral, 0 as UnMatchedCollateral from tblLoans where CensusTractID = something
Union
Select LoanID, 0 as  MatchedCollateral, CensusTractID as UnMatchedCollateral from tblLoans where CensusTractID <> something) as Contrasted where MatchedCollateral >= UnMatchedCollateral

is near to the logic, if not really workable SQL syntax.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question