query criteria from a table?

November 6, 2006 5:15 AM

Access SQL: How to use a short list of numbers stored as text in a table cell as the criteria for a query?

I'm trying to insert a list of values from a table into the where clause of a query. I have a several sets of values stored as text that I'd like to use as criteria in a query, for example,

Table:
ID CoumnA
1 | "3", "5","17", "34"
2 | "1", "12","15"

Where clauses:

WHERE ColumnA IN( "3", "5","17", "34")
or,
WHERE ColumnA IN( "1", "12","15")



Is there an easy way to get those values from a single cell in a table and into the query? I've tried a few things, DLookUp, for example, but get no values returned.
I think there should be a quick way of doing this without resorting to VBA, but I can't think what it is.

posted by Zetetics to Computers & Internet (9 answers total)

WHERE LIKE ColumnA='%"34"%'
WHERE LIKE ColumnA='*"34"*'

does one of those 2 work?
posted by derbs at 6:13 AM on November 6, 2006

I don't think you can do this using a single query with the structure you're using.

You could use an inner join if you turned the data on end.

If your table had one column, named QueryID, four rows, and the rows looked like this...

3
5
17
34

...you could join QueryID to the column in your other table that contains the values you wish to match.

Is there a particular reason you need to reference one "cell"? If so, you'll likely need to write VBA, but it isn't difficult.
posted by SteveInMaine at 6:48 AM on November 6, 2006

Response by poster: Ah - sorry, no.

To clarify, I'm looking to get the values listed above from one table to be used as the criteria for a query to be applied to a different table.

The table I'm querying has a column that contains only one number, stored as a text value. So I want to get the list of values from tableA to to use as criteria for a query on tableB. The query should return only the rows with 3, or 5, or 17, or 34 in one case,
and only the rows with 1, or 12, or 15 in another case. ( There are about 12 such cases)

TableB:
ID Field1 Field 2
1 text 3
2 text 12
3 text 34
4 text 1


The criteria WHERE ColumnA IN( "3", "5","17", "34") works, I just want to be able to change the set of numbers searched for.
posted by Zetetics at 6:51 AM on November 6, 2006

Response by poster: SteveInMaine,

I'm considering using the approach you suggest but, basically, I'm looking for a shortcut. I'm adding a step into the middle of a series of queries and calculations and I'm looking make as few changes as possible to existing tables and queries. I thought that there should be quick way to do this, but if not, I'll do it with VBA.
posted by Zetetics at 7:00 AM on November 6, 2006

If access will do sub-selects then something like this should work:

select some_column from TableB
where some_other_column in (select columnA from TableA where tableA.id='1')

If you don't want the id='1' condition, just remove it, or replace it as appropos.
posted by jaded at 7:47 AM on November 6, 2006

Response by poster: jaded,

I think that approach should work as well - as should a DLookUp in the same position. However, when I run that query, it returns no rows.
I suspect access does something odd to the value returned by the subquery before executing the main query.
posted by Zetetics at 8:59 AM on November 6, 2006

Off the top of my head, the only way I can think of to do this in a single query would be to write a convoluted formula that would analyze the string and return the nth item (and null if the nth item didn't exist). You could then drop versions of this formulas into the criteria rows at the bottom of the query builder window to simulate the "in" function. Probably would be easier to encapsulate this formula in a small VBA function, which could be called from the query. E.g., if the function took 2 arguments, the text string of numbers and the lookup position, the criteria might look like:

somefunction([ColumnA],1)
somefunction([ColumnA],2)
somefunction([ColumnA],3)
etc . . .
posted by treepour at 12:16 PM on November 6, 2006

Apologies for this, but my answer isn't going to be entirely helpful this time, so much as next time.

This is exactly how you must not store values in a database. Whoever made this table is using Access but pretending it's Excel. That collection of strings belongs in a referenced table, not a column -- and all relational databases and SQL itself expect you to do so.

To rephrase that so it sounds a little more helpful and a little less like chiding... if this database is anything other than temporary you ought to "normalise" the data in it as quickly as possible. Thankfully that can be scripted in about three minutes by anyone who can write basic ASP or any similar scripting language.

Once it's done, your database can be made to stand up to bad data input in the future, and it becomes *much* simpler to write the query you're asking for.
posted by genghis at 2:17 PM on November 6, 2006

Response by poster: It looks like this is not as simple to do as I think it should be.

I guess I'll just use VBA to build the SQL then.

Thanks all.

And genghis, yes, I'm trying to retrofit something into an already complicated database for people who would be much happier using excel - if only they could fit all their data into excel.
I am well aware that I am not asking for the ideal way to handle this problem, but given the various constraints of the situation, this would have been, by far, the fastest solution.
posted by Zetetics at 4:12 PM on November 6, 2006