Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    43

    Unanswered: Query Multiple fields

    I am querying Table A and wish to query 18 fields within that table - But at the moment when i try and do that i dont get any results, i believe this is because access is looking for "Fletchers" in all 18 fields(which there isnt) i just want it to return the fields which do have Fletchers in them (For example fields 1,2,4,9,14)

    How do i do this?(Please post an idiot proof reply)

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from table
    where (column1 = A) OR (column2 = B) OR ...

    Alternatively,

    TableA

    columnA | columnB
    ------------------------

    Select columnA
    from tableA
    where columnB = value;
    Last edited by r123456; 02-27-04 at 06:42.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by r123456
    Select *
    from table
    where (column1 = A) OR (column2 = B) OR ...

    Alternatively,

    TableA

    columnA | columnB
    ------------------------

    Select columnA
    from tableA
    where columnB = value;

    Just a follow up note on this because I'm not sure if the poster is really clear on how SQL works - when you are looking for 'Fletcher', you must specify the column which would contain that value.

    You cannot just pull back rows if ANY column contains 'Fletcher' - you must explicitly name the columns as in r123456's first example.

    Though this is definitely the correct answer by r123456 - didnt want to steal his thunder

  4. #4
    Join Date
    Feb 2004
    Posts
    43
    Thanks Guys-Will try this later, really do appreciate the help.

    May need a little help late with the sql implementation- if you dont mind......

  5. #5
    Join Date
    Feb 2004
    Posts
    43

    Have a look(please)

    This is the string..

    WHERE (((lates.Date)=[Enter Date]) AND (lates.Customer)="Fletchers" OR (lates.Customer (2))="Fletchers" OR (lates.Customer (3))="Fletchers" OR (lates.Customer (4))="Fletchers"

    what have i done wrong(i presume the structure isnt quite right)

  6. #6
    Join Date
    Feb 2004
    Posts
    43

    Different Issue

    I now have this.......

    WHERE (((lates.Date)=[Enter Date]) AND (lates.Customer)="Fletchers" OR (lates.Customer (2))="Fletchers" OR (lates.Customer (3))="Fletchers" OR (lates.Customer (4))="Fletchers");


    Which saves ok(Which i presume means i have sorted the structure)

    but when i try and run it i get an error message which says

    "Undefined Function "lates.Customer" in expression"

    How do i rectify this-and what does it mean?

  7. #7
    Join Date
    Jan 2004
    Posts
    492

    Re: Different Issue

    Originally posted by Oliver_No1
    I now have this.......

    WHERE (((lates.Date)=[Enter Date]) AND (lates.Customer)="Fletchers" OR (lates.Customer (2))="Fletchers" OR (lates.Customer (3))="Fletchers" OR (lates.Customer (4))="Fletchers");


    Which saves ok(Which i presume means i have sorted the structure)

    but when i try and run it i get an error message which says

    "Undefined Function "lates.Customer" in expression"

    How do i rectify this-and what does it mean?

    Not sure if by fields you means rows or columns? You cannot use SQL to simply search an entire row to see if your value is present. You must name explicity name the COLUMNS-

    Say you have 5 columns in TABLE_A -
    Col1
    col2
    col3
    col4
    col5

    Lets say you think 'FLETCHER' could be in col1, 3 or 5

    Code:
    SELECT *
    from TABLE_A
    WHERE col1 = 'FLETCHER' or col3 = 'FLETCHER' or col5 = 'FLETCHER'
    So this will pull back every column if any of those columns contain that value. SQL is a little more structured than you are probably thinking - but it allows for powerful queries.

    Hope this helps.

  8. #8
    Join Date
    Feb 2004
    Posts
    43

    Unhappy

    Hi-thanks for taking the time to answer my silly queries......

    Just one last thing

    If my column 1 is called Date -wouild i still refer to it in the expression as Col1 or would i refer to it as "Date"

    Sorry -and i know these are daft questions.

  9. #9
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Oliver_No1
    Hi-thanks for taking the time to answer my silly queries......

    Just one last thing

    If my column 1 is called Date -wouild i still refer to it in the expression as Col1 or would i refer to it as "Date"

    Sorry -and i know these are daft questions.
    Not a problem - you would stick your actual column names in place of col1,3,5 ...etc. in my example

    So if your column2 is really called "start_date"
    then you can say "where start_date = '01-JAN-2003' " (or something like that)

    Just a couple notes:

    I advise you to put underscores in your tables instead of spaces - Access allows you to put tables with spaces in [ ] . Personally this gets really confusing. Instead of [your table] make it "your_table".

    Also, I believe DATE may be a reserved keyword - Try to give it a little more descriptive word like - end_date, start_date...etc.

    This will help save you headaches in the future. If you still have trouble, PM me again and you can send me your db. Dont be bashful about asking questions b/c thats what this forum is for - everyone started at the beginning just like you

Posting Permissions

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