Solved

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

752 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