Query for records that have certain numbers in them

5 pts.
Tags:
Microsoft Access
Microsoft Access queries
I have a table with 4 fields and 100 records(rows), with numbers in each field. Example would be: In Record One Fieldone has 87 fieldtwo has 94 fieldthree has 32 fieldfour has 99. than Record two has numbers in each field: Fieldone-94, Fieldtwo-33, Fieldthree-102, Fieldfour-44. I need to create a query that will show records wih the following guidelines: show records that have all four of this numbers; 94, 32, 102 , 125 or show records that have three of the four numbers listed or records that have two of the numbers listed. Can MS Access do this? If so, how?  or do i need to use a different program?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Under assumption that your table has a unique column named id, you could use the following query
SELECT  id, count(*) as “quantity of numbers” 
FROM table, search
where fieldone=f1 or fieldtwo=f1 or fieldthree=f1 or fieldfore=f1
group by id;
where the “table” is your table and “search” is a table with (different) numbers to be search:
f1
94
32
102
125 

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • tlsanders1
    This is not real elegant, but I think it does what you asked for. In a query, add the table and each of the four fields. Add a new column to the query with an immediate IF such as "=IIF(field1=94,1,0)". That column will now have a 1 if field 1 matches your criteria or a 0 if not. Make three more columns in the query for the other three criteria. Now add a column that totals those four, "=expr1+expr2+expr3+expr4" This column now lists for each record, how many of the criteria are matched. I couldn't get that column to filter or sort, so I created a separate query that used the first query for data, and I could set in the criteria line of expr5, ">1" and I could sort it. So, that query lists the records from the original data that meet 2 or more of the criteria and sorts the records that meet 4 criteria, 3 criteria, etc.
    1,340 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following