Query for records that have certain numbers in them

Dnsnb2

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?

Asked: March 3, 2012 4:00 PM Last updated: March 5, 2012 4:54 PM

Answer Wiki

Last updated: February 1, 2013 6:59 PM GMT

msi771,670 pts.
History
Contributors
- Ordered by most recent
- msi771,670 pts.
- Michael Tidmarsh62,155 pts.
- Wwjiu150 pts.

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:

—

102

125

Discuss This Question: 1 Reply

tlsanders1 Mar 5, 2012 4:54 PM GMT

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