MS Access - Creating Query w/ multiple criteria values

JTS25

New Member
Joined
Oct 10, 2019
Messages
31
Hi all,

I'm trying to build a query, to have the criteria find multiple values I have on a spreadsheet (See Below). I have around 100 of these values. Is there an easier way to format the criteria rather than typing in "1801 INIT 0025" or "1801 INIT 0026" and so on, for each one? My query is based off of one table and two specific fields (Initiative ID, Current Status). Thanks in advance.
Initiative
1801 INIT 0025
1801 INIT 0026
1801 INIT 0029
1801 INIT 0030
1801 INIT 0045
1801 INIT 0052
1801 INIT 0171
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Just create a one field/column table containing all the values you want to include, then do a matching query between your main data table and this table, matching on this field.
 
Upvote 0
Thanks.

Once I have a query with all the values I'm trying to sort through and find, I need to complete an update query to update the current status field from one value to another.

"Just create a one field/column table containing all the values you want to include, then do a matching query between your main data table and this table, matching on this field."
The problem is that it is taking me forever to setup the criteria for the one field/column table to find all of the values.
 
Upvote 0
Once I have a query with all the values I'm trying to sort through and find, I need to complete an update query to update the current status field from one value to another.
Shouldn't be an issue. Just change it from a "Select" query to an "Update" query and make the appropriate change.
 
Upvote 0
Joe,

When I go to Query Design -> Select Table -> select column (Initiative ID) -> Make Table -> then name new table. Do I want to add this to the current DB in Access or a new one?

Also, by creating this new table will in interfere with the tables that are already created in access that we store important data on?
 
Upvote 0
OK, there are a few going on here, and I want to make sure that we are talking "apples-to-apples" to here.

You are not creating a "Make Table Query" here (at least not to do the comparison part that we were talking about). If you want to update a field on an existing table, that would be an "Update Query".

If you are talking about creating a new Table for one column Table of criteria, I guess it depends on where this list of criteria is coming from. Do you have the list already, maybe internal or external to Access? If so, you have be able to link or import it, depending on where it exists, and what format it is in. Whether or not you want to store it in the same database is up to you. It really should not cause any interference with your existing database tables, as it only interacts with the data in manners which you set (we won't define any pre-existing relationsips between this table and your other tables in the database).
 
Upvote 0
Let me try to provide some more details.

The current database table 'Initiatives' has many columns of data already in it, including 'Initiatives ID, & Current Status'.

I created an excel spreadsheet report from the Database, and filtered it to specific 'Initiative ID' values that I will need to conduct a mass update to their 'Current Status' values. (144 Values that need to be updated).

I was trying to build a 'Select Query' based off of the 'Initiatives' Table and add 'Initiatives ID, and Current Status'. Then use the criteria function to search for the 144 'Initiative ID's' in that Database that I need to conduct updates to their 'Current Status' values.

I'm looking for an easier way to add those 144 'Initiative ID' values to the criteria, so I do not have to do --"1801 INIT 0025" or "1801 INIT 0026" for each of the 144 values.

Once I have the select query built with the output of those 144 values, I was going to complete the update query to update their current status values.
 
Upvote 0
OK, so you have an Excel file with the 144 Initiative ID values that you need. All you need to do is to link that Excel file in to an Access table, and then created a Query, linking your Access table to the Excel table (linking this field to the field in your table that has this value). By "matching" on this field, you are limiting your query to just return the records meeting those values.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top