R help - Nov 2007 - Query an Access database based on a date attribute

Dear useRs,

I would like to query an Access database through R based on a date attribute
but, unfortunately, I fail to do so. For example, the table test_table of the
test.mdb looks like:

   ID         cd  market competitor         dd price
1   1 2007-11-20 atl-bos      delta 2007-11-20   210
2   2 2007-11-20 atl-bos      delta 2007-11-21   190
3   3 2007-11-20 atl-bos      delta 2007-11-22   180
4   4 2007-11-20 atl-bos     united 2007-11-20   205
5   5 2007-11-20 atl-bos     united 2007-11-21   195
6   6 2007-11-20 atl-bos     united 2007-11-22   175
7   7 2007-11-20 atl-sfa      delta 2007-11-20   350
8   8 2007-11-20 atl-sfa      delta 2007-11-21   320
9   9 2007-11-20 atl-sfa      delta 2007-11-22   300
10 10 2007-11-20 atl-sfa         aa 2007-11-20   340
11 11 2007-11-20 atl-sfa         aa 2007-11-21   320
12 12 2007-11-20 atl-sfa         aa 2007-11-22   300

In this context, I try to get all the records for which market is atl-bos,
competitor is delta and dd is 2007-11-20 (first record above). To do this I used
># channel <- odbcConnectAccess("test.mdb")
>res <- sqlQuery(channel, "select * from test_table where market =
'atl-bos'
and competitor = 'delta' and dd = 2007-11-20")

but the result seems to be incorrect.
> res
[1] ID         cd         market     competitor dd         price
<0 rows> (or 0-length row.names)

Does any of you know what it is going on? I suspect that the last expression in
the select statement is not executed/interpreted correctly and, as a
consequence, the query leads to the above empty set.

Thank you.

Tudor

--
Tudor Dan Bodea
Georgia Institute of Technology
School of Civil and Environmental Engineering
Web: http://www.prism.gatech.edu/~gtg757i

This is a question about SQL, or more precisely, Microsoft's peculiar 
dialect of SQL.  You haven't even mentioned (let alone credited) package 
RODBC which you appear to be using.

In SQL queries you need to quote numeric values if you want them to be 
treated as character.  Why did you quote 'alt-bos' and not
'2007-11-20' ?
As I recall, some DBMSs need date values quoted and some do not, so try 
quoting.

On Mon, 12 Nov 2007, Tudor Bodea wrote:
> Dear useRs,
>
> I would like to query an Access database through R based on a date 
> attribute but, unfortunately, I fail to do so. For example, the table 
> test_table of the test.mdb looks like:
>
>   ID         cd  market competitor         dd price
> 1   1 2007-11-20 atl-bos      delta 2007-11-20   210
> 2   2 2007-11-20 atl-bos      delta 2007-11-21   190
> 3   3 2007-11-20 atl-bos      delta 2007-11-22   180
> 4   4 2007-11-20 atl-bos     united 2007-11-20   205
> 5   5 2007-11-20 atl-bos     united 2007-11-21   195
> 6   6 2007-11-20 atl-bos     united 2007-11-22   175
> 7   7 2007-11-20 atl-sfa      delta 2007-11-20   350
> 8   8 2007-11-20 atl-sfa      delta 2007-11-21   320
> 9   9 2007-11-20 atl-sfa      delta 2007-11-22   300
> 10 10 2007-11-20 atl-sfa         aa 2007-11-20   340
> 11 11 2007-11-20 atl-sfa         aa 2007-11-21   320
> 12 12 2007-11-20 atl-sfa         aa 2007-11-22   300
>
> In this context, I try to get all the records for which market is 
> atl-bos, competitor is delta and dd is 2007-11-20 (first record above). 
> To do this I used
>
>> # channel <- odbcConnectAccess("test.mdb")
>> res <- sqlQuery(channel, "select * from test_table where market
= 'atl-bos'
> and competitor = 'delta' and dd = 2007-11-20")
>
> but the result seems to be incorrect.
>
>> res
> [1] ID         cd         market     competitor dd         price
> <0 rows> (or 0-length row.names)
>
> Does any of you know what it is going on? I suspect that the last 
> expression in the select statement is not executed/interpreted correctly 
> and, as a consequence, the query leads to the above empty set.
>
> Thank you.
>
> Tudor
>
> --
> Tudor Dan Bodea
> Georgia Institute of Technology
> School of Civil and Environmental Engineering
> Web: http://www.prism.gatech.edu/~gtg757i
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

On 12/11/2007, Tudor Bodea <gtg757i at mail.gatech.edu>
wrote:
> Dear useRs,
>
> I would like to query an Access database through R based on a date
attribute
> but, unfortunately, I fail to do so. For example, the table test_table of
the
> test.mdb looks like:
>
>    ID         cd  market competitor         dd price
> 1   1 2007-11-20 atl-bos      delta 2007-11-20   210
> 2   2 2007-11-20 atl-bos      delta 2007-11-21   190
> 3   3 2007-11-20 atl-bos      delta 2007-11-22   180
> 4   4 2007-11-20 atl-bos     united 2007-11-20   205
> 5   5 2007-11-20 atl-bos     united 2007-11-21   195
> 6   6 2007-11-20 atl-bos     united 2007-11-22   175
> 7   7 2007-11-20 atl-sfa      delta 2007-11-20   350
> 8   8 2007-11-20 atl-sfa      delta 2007-11-21   320
> 9   9 2007-11-20 atl-sfa      delta 2007-11-22   300
> 10 10 2007-11-20 atl-sfa         aa 2007-11-20   340
> 11 11 2007-11-20 atl-sfa         aa 2007-11-21   320
> 12 12 2007-11-20 atl-sfa         aa 2007-11-22   300
>
> In this context, I try to get all the records for which market is atl-bos,
> competitor is delta and dd is 2007-11-20 (first record above). To do this I
used
>
> ># channel <- odbcConnectAccess("test.mdb")
> >res <- sqlQuery(channel, "select * from test_table where market
= 'atl-bos'
> and competitor = 'delta' and dd = 2007-11-20")
>
> but the result seems to be incorrect.

Dear Tudor,

Try running your query in Microsoft Access/Query first. Once it works,
then move to R/RODBC...

I don't use MS Access routinely in this way, but presumably there is a
problem with your SQL.

Best wishes,

Mark
-- 
Dr. Mark Wardle
Specialist registrar, Neurology
Cardiff, UK