Solved

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

Posted on 2015-01-06
3
112 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 119

Expert Comment

by:Rey Obrero
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now