By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,822 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,822 IT Pros & Developers. It's quick & easy.

Comparing Records in a table.

P: 72
HI there a newbie here in i just wanted to ask something, on how can compare records in a particular field in my table... I'am develop a time scheduling system, its like for example i have a table name called RECORDS that has a 4 fields namely, TIME START, TIME END ,DAYS,ROOM. i want to compare all records that has been save in the RECORDS to be able to test if there a conflict on it,,, first hing i wanted to is to test if there equal values of records.

Example, the first record that has been saved has the follwing:
TIME START: 7:00 AM, TIME END 8:00 AM, DAYS: MWF, ROOM: 101.

then the second record that has been save is exactly equal to the first record.
the question how i can prevent this sql statement, what is advisable to use LIKE or COMPARE...

Can somebody tell me or site some ideas on how to solve this problem:

And lastly

am using this code:

Select * from RECORDS where TIME START, TIME END, DAYS, ROOM (this is the part i dont know).. i hope someone here can help me.

Thank you very much.... and God Bless...
May 24 '07 #1
Share this Question
Share on Google+
1 Reply

code green
Expert 100+
P: 1,726
first hing i wanted to is to test if there equal values of records
I think you mean you are testing for duplicate records. If duplicates are undesirable it is better to dissallow them in the first place as they quickly become unmanageable. However your problem. This is a common problem that causes heated discussion on how to solve. The method I prefer is creating a temporary table containing unique entries from the RECORDS table (can you not think of a more imaginative name?)
Expand|Select|Wrap|Line Numbers
  1. CREATE TEMPORARY TABLE temptable (SELECT DISTINCT start_time ,room etc FROM records)
Then a query looking for matching fields but different IDs that occur between the two tables.Then you have your duplicate entries
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM records WHERE temptable.time = record.time AND temptable.room = record.room AND etc AND  records.id <>  temptable.id
This is pseudocode and possibly MySql syntax but should translate to Access.
May 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.