MS Access - Creating Query w/ multiple criteria values

JTS25

New Member
Joined
Oct 10, 2019
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 10, 2019
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 10, 2019
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 10, 2019
Messages
30
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,081,566
Messages
5,359,635
Members
400,542
Latest member
Fahkeet

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top