2

I wanted to make a query in MS Access 2003 (or just sql in VBA) which would have an IN clause working on tuples, i.e.,

I have to columns with year and month, and I would like to extract for example 2010-10 and 2012-03 using IN clause. For comparison in Postgres it would look like

SELECT * FROM my_table WHERE (year, month) IN (("2010","10"),("2012","03"));

but it does not work for me in Access. Yes, I store both year and month as text, but it really does not matter.

I know I could write it using many AND and OR, or make a LEFT JOIN with a temporary table. But those queries are created prograYmatically, so I wanted to make it as simple as possible.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
MPękalski
  • 6,873
  • 4
  • 26
  • 36
  • 1
    If you replace double quotes with single quotes then your SQL complies with the SQL:2000 standard. Sadly, Access is not compliant with even entry level SQL:2000 and the syntax is not supported ([it isn't supported on SQL Server](http://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors) either). – onedaywhen Feb 21 '12 at 10:07
  • ...however, I think you have a design problem. You seem to be modelling the period of one month using two attributes of type 'text'. Consider using temporal data types instead, perhaps coupled with a [Calendar table](http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html) but hard to give good advice based on a single query ;) – onedaywhen Feb 21 '12 at 10:17
  • There was not much designed. It was a fast task where I needed to create a query which worked like above. Of course I could import everything to Postgres, but it takes longer to create a table etc. than to import the file to Access Hence, I wanted to know if it was possible to make this kind of WHERE clouse. – MPękalski Mar 01 '12 at 23:13

1 Answers1

3

How about:

SELECT * FROM my_table WHERE year & month IN ("201010","201203")
Fionnuala
  • 90,370
  • 7
  • 114
  • 152