MS Access - Creating Query w/ multiple criteria values

[JTS25]

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

 

[Joe4]

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.

 

[JTS25]

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.

 

[Joe4]

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.

 

[JTS25]

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?

 

[Joe4]

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).

 

[JTS25]

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.

 

[Joe4]

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.