Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Searching Multiple Columns in Acess

    Hi,

    I am trying to workout a way that I can run a query that will search a number of columns and bring me the latest date back for that id number
    example
    AddressID Appt1 Appt2 Appt3 Appt4 Appt5
    101 07/04/2008 14/04/2008 22/04/2008 01/05/2008
    102 06/04/2008 13/04/2008 21/04/2008 23/04/2008 08/05/2008

    So what I am trying to understand is in my query I want to add another column thats say MaxAppt and that would look accross the fileds and pick out the latest Appt dates... (in this case would be 01/05/2008 & 08/05/2008)

    How can this be done?

    Thanks

  2. #2
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Quote Originally Posted by healdem
    are they always in ascending date order?
    can you always rely on the last 10 digits being the last gdfate the prioperty was viewed

    if so you could do somethign similar to

    iif(isnull(<mycolumn>,"",right$(<mycolumn>,10))

    its not as robust As I'd like it but....
    the isnull caters for properties with no appointments the right$ chops off the last 10 characters.

    incidentally, part of the reason why you are struggling with this is that in my books the design is flawed...

    one you are storing dates as text.. never a good idea, what happens if theres a typo, or if say da boss suddenly wants to know what properties haven't had an appointment in n days/weeks/months

    you design is not open ended.. what happens if say there are more than than say 22 appointments if you are suing a text field.

    it would nmake more sense in my books to have appointments as a sub table hanging off a specific property, possibly linked with the agent who took the prospective customers round. so you could identify which agent had done which viewings, which properties a customer has looked at....
    just my tuppeny ha'porth
    The dates are always in ascending order with Appt1 being the first appointment. I can rely on this as its a application instruction from the front end DB that inputs into the field. So it will always be a date that gets entered and in the same format

    Using this iif(isnull(<mycolumn>,"",right$(<mycolumn>,10))

    What happens when there is only 1 appointment made and the Appt2 to 5 are blank?

    Can you explain that formula a bit more I dont understand it, I guess I'm inputting it this way?

    iif(isnull(<Appt1>,"",right$(<Appt1>,10))



    Its not open ended there are only a maximum of 5 appointments that can be made


    I know it would have made more sense to make this a different way by a seperate table and using it as a listing. That would been far easier but I only report from the DB, I didnt build it! unfortuately!!

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Normalise your structure and things like this become all to easy
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    How do you mean normalise my structure?

    I cant amend the Database, I dont own the DB. All I have is an ODBC link tables Access Database to query the tables. If I created a table that would put each appointment on a single row, so instead of having 5 columns of dates how easy would it be to replicate it and have 5 rows?

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way:

    http://support.microsoft.com/default...b;en-us;209857

    Another would be to create a UNION query to normalize the data:

    SELECT AddressID, Appt1
    FROM TableName
    WHERE Appt1 Is Not Null
    UNION ALL
    SELECT AddressID, Appt2
    FROM TableName
    WHERE Appt2 Is Not Null
    UNION ALL
    ...

    And then all of your subsequent tasks may become easier, as George mentioned.
    Paul

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    will become easier
    George
    Home | Blog

Posting Permissions

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