Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
130 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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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