Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Contains' (Access 97)

    Hi

    I have to find all instances where a course of treatment includes 2004. This could be period 2002 - 2004, or 2001 & 2004, or just 2004. I have produced a crosstab query which tells me what year a treatment occured in, but can't seem to find the correct syntax for to restrict the year field to "where the row contains 2004".

    Many thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Contains' (Access 97)

    Can you provide some more info ?
    Are you searching in a range of dates ?
    Or have you a text field where you have to find if it contains 2004 ?
    Francois

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Contains' (Access 97)

    Hi

    The two fields are Treatment and Year
    Year ranges from 1995 to 2005
    Treatment could take place in any of these years (or not at all) e.g.

    Treatment Year
    123 2000
    123 2002
    123 2004
    124 2001
    124 2005
    123 2005

    So some treatments will not have taken place in 2004 at all.

    I need to find all those treatments which did take place in 2004. At the moment I have a cross tab query:

    Treatment .... 2000 2001 2002 2003 2004 2005
    123 .... 1 1 1 1
    124 ...... 1 1

    I need to be able to pull back the complete row for Treatment 123
    I am not interested in any Treatments where there is nothing for 2004

    Thanks very much

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Contains' (Access 97)

    Add a column to your crosstab query.
    Enter as field Year.
    Enter as Total : Where
    Enter 2004 as Criteria
    Francois

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Contains' (Access 97)

    Maybe I misunderstood you.
    Do you want all the treatments over all the years if there was a treatment in 2004 ?
    Add the table a second time to the crosstab query.
    Link the two tables by treatment number.
    Add a column to the query and enter the year of the second table as field, Where as Total and 2004 as Criteria.
    See Attachment
    Francois

  6. #6
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Contains' (Access 97)

    Hi

    Second solution worked fine.

    Thanks very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •