Query for records that have certain numbers in them
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
March 3, 2012 4:00 PM
UPDATED:
March 5, 2012 4:54 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.