Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-06
3
Medium Priority
?
132 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
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 1500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

877 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