Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    55

    Unanswered: Is there a way to compare two fields from two different tables?

    I am working on a database that will help users to reserve rooms..I have a table DATA with ROOMNAME, START_DATE,END_DATE,START_TIME,END_TIME, USER_NAME as the fields.

    I have another table HISTORY that has a history of all the reservations. I would like to compare the data between these two tables. For example, if a user enters a date and a time that's been taken, I would like to display an appropriate message. Is there a way to compare two fields from two different tables? Or is there an easier way to do this?

    Thanks,

    DH

  2. #2
    Join Date
    Apr 2004
    Posts
    130

    Arrow

    In simplified terms, you need to create a query which links the two tables together. This is done by finding a common field in the two tables and linking them together in the query. Not as complex as it might sound. Any very basic Access book will help you in this area.
    Winston

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select dateColumn
    from tableA ta
    where EXISTS
    (Select null
    from tableB tb
    where ta.dateColumn = tb.dateColumn);

    If NOT (dateColumn is NULL) Then
    Message('Date Taken')
    End If
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by r123456
    select dateColumn
    from tableA ta
    where EXISTS
    (Select null
    from tableB tb
    where ta.dateColumn = tb.dateColumn);

    If NOT (dateColumn is NULL) Then
    Message('Date Taken')
    End If
    I tried the above code but it gave me a syntax error..I am not sure if the above code would work in a query..

    I tried this in my query :

    SELECT HISTORY.ID, HISTORY.START_DATE
    FROM HISTORY
    WHERE
    EXISTS(Select NULL FROM SCHEDULE WHERE HISTORY.START_DATE=SCHEDULE.START_DATE)
    Iff NOT(HISTORY.START_DATE Is null), "DATE TAKEN" ;

    What am I doing wrong here?

  5. #5
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55
    If your doing this all in VB then create a qurey based on the paramaters you mentioned. Then run a simple test for EOF. If you get an EOF then you know that the date and time is avalible. If EOF is FALSE then you know that date and time is taken.

    If not then I would suggets to do it VB.

    HTH

Posting Permissions

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