Access Fields out of sync

Results 1 to 2 of 2

Thread: Access Fields out of sync

  1. #1
    Join Date
    Dec 1969

    Default Access Fields out of sync

    I have attempted to set up a Trivia Access DB. I have two tables, one entitled Members (which holds all the persons registration information i.e. first name, last name, date of registration, etc.) and Scores (which has columns A1-A10 i.e. answers for questions 1 - 10). I also have two automatic numbered fields in the tables, one called Key in the Members table and one called QuizNum in the Scores table. <BR>Naively, I thought this would be enough to tie the two together. I quickly learned that I was wrong. If the person were to click the Back button on their browser after taking a test, and corrects his/her answers, that would make two records for just one person. Now I have 120 score and only 118 registered members. <BR><BR>I just want to know how to set up a relationship between the two tables, so this doesn&#039;t happen to me again. Could someone please give me some guidance? I am using Access 2002. Thank you very much.

  2. #2
    Join Date
    Dec 1969

    Default Define a relationship

    So far, you don&#039;t seem to have anything that relates the two tables.<BR><BR>So add something.<BR><BR>The most obvious thing would be to put a "Key" field in the Scores table that relates each record to a record in the Members table. That is, you copy the value from the Members table to the Scores table to say which score goes with which person.<BR><BR>And then, to prevent duplicate entries, you simply do:<BR><BR>SELECT * FROM Scores, Members <BR>WHERE Scores.Key = Members.KEY<BR>AND Member.UserName = &#039;jones&#039;<BR><BR>or something similar to that.<BR><BR>If you get any record(s) from that query, then the score entry already exists. (Just use RecordSet.EOF to see if there are any records.)<BR><BR>One of the beauties of this scheme: You could have a different set of trivia questions each day (or week or month or whatever).<BR><BR>Just add *another* field to the Scores table: DateOfQuiz<BR><BR>And now you do:<BR><BR>SELECT * FROM Scores, Members <BR>WHERE Scores.DateOfQuiz = Date()<BR>AND Scores.Key = Members.KEY<BR>AND Member.UserName = &#039;jones&#039;<BR><BR>Now only records from today are checked. The member could have scores from a hundred other days, but they don&#039;t matter.<BR><BR>If you don&#039;t use the date to designate a particular quiz, just use a sequential numbering system or some such:<BR><BR>SELECT * FROM Scores, Members <BR>WHERE Scores.QuizNumber = 7<BR>AND Scores.Key = Members.KEY<BR>AND Member.UserName = &#039;jones&#039;<BR><BR>Get the ideas?<BR><BR>

Posting Permissions

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