Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24

    Unanswered: Comparing Fields

    Morning All

    I have a sever case of brain fade on the simplest of things this morning

    I have 2 tables containing a numebr fo fields, the importanat ones are
    Table - Yesterday
    Field 1 - TicketNo
    Field 2 - ServiceID
    Field 3 - CurrentState

    Table - Today
    Field 1 - TicketNo
    Field 2 - ServiceID
    Field 3 - CurrentState

    What I am trying to do is to compare the CurrentState field in both tables, and just report the ones which have changed. And I haven't got a clue.

    Can anyone point me in the right direction?

    EEjit
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you really have tables called yesterday and today?
    this smells of bad design.
    Code:
    SELECT *
    FROM   yesterday
     INNER
      JOIN today
        ON yesterday.ticketno = today.ticketno
       AND yesterday.serviceid = today.serviceid
    WHERE  yesterday.currentstate <> today.currentstate
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by georgev
    Do you really have tables called yesterday and today?
    this smells of bad design.
    Agreed there.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2008
    Posts
    3
    Hi, I'm working with Eejit on this personal little project of ours. and no by design each table has got the relative date on it example: 18Nov08 etc. We are looking to be able to set the query to check today's date what ever that maybe and then search the day before to.

    But we thought we would start simple first.

    Many thanks,

    Genesis

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    REDESIGN!!
    One table, with a datestamp field.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    18Nov08
    Is that text? Now it smells like bad design AND bad data!! ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2008
    Posts
    3
    I know but it is all we have to work with really, the data is created by a third party we are just trying to make it easier to read and sort.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it may be created by a third party, but once its in your systems thats no excuse, import the data into your own table (note: NOT TABLES)
    that way round you have control of your data, these wquestions becoem trivial to answer
    the next time some msmart aleck wants to knwo what happens if we compare with last week, month, year, decade you are prepared.

    don't let someone elses idea of data design hold back what the business requires
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    24
    Just because we used the names "yesterday", "today" & "18Nov08" doesn't necessarily make it bad design. Other than the original question, you have no idea what we are trying to achieve or how the database was to be used. The names make perfect sense to us. There will be no need to compare last week, last month etc.
    ----------------------------------------------------

    One day, I'll have learnt so much that I'll be able to answer questions as well!!

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey Das, that is bad design - trust us
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Bad design. Single table.

    You've used data (in this case temporal data) to artificially name & differentiate multiple entities which should in fact be one single entity. You should have a single entity and this temporal data should be part of the composite natural key for that entity.

    This is a classic design error and is seen time and time again - you are not the first, you will not be the last. You have compounded it by using not just temporal but relative temporal terms (today, yesterday) as table names. If for whatever reason your data loading fails one day then your database is rendered logically b0rked.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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