[R] Query an Access database based on a date attribute

[Mark Wardle]

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