Solved

Update Db only when information has changed

Posted on 2008-06-21
4
241 Views
Last Modified: 2010-03-20
Hi Experts,

I am recording sports score information in a Db once per minute for the duration of a sports event. This has caused the problem that most of the records are identical apart from the time i.e:

Time     TeamA    TeamB    ScoreA ScoreB
12:00    France    Ireland    0           0
12:01    France    Ireland    0           0
12:02    France    Ireland    0           0
12:03    France    Ireland    0           0

It would be more efficient if only a change to the score is recorded. Is it possible to do this using a SQL query?
0
Comment
Question by:DColin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:jgoeders
ID: 21837533
UPDATE myTable SET ScoreA = " & myScoreAVariable & ", ScoreB = " & myScoreBVariable & " WHERE TeamA = 'France' AND 'TeamB = Ireland'


This is just an example of a SQL statement you would need to run.  In this example I have shown how to build in the scores from variables.  The same could be done with the teams.  If you need to check if the teams are already entered then let me know and i'll post how to do that.
0
 

Author Comment

by:DColin
ID: 21837989
Hi jgoeders:

Thank you for yourSQL query. I can see how this will update the score as it changes but does it record the changes as I would require, for example:

Time     TeamA    TeamB    ScoreA ScoreB
12:00    France    Ireland    0           0
12:10    France    Ireland    1           0
12:23    France    Ireland    2           0
12:43    France    Ireland    2           1

Or would your query  just keep modifying a single record to return a single entry table as below:

Time     TeamA    TeamB    ScoreA ScoreB
12:43    France    Ireland    2           1

Also it would not make the original (12:00, France, Ireland, 0, 0) entry.
0
 
LVL 2

Accepted Solution

by:
jgoeders earned 500 total points
ID: 21839101
I see what you're looking for.

First check if a record exits:

set rs = currentdb.openRecordset ("SELECT * FROM myTable WHERE TEAMA = ... AND TEAMB = ... AND SCOREA = ... AND SCOREB = ...")
IF RS.EOF THEN 'RECORD ISN'T THERE YET
    CURRENTDB.EXECUTE("INSERT INTO MYTABLE (TIME, TEAMA, TEAMB, SCOREA, SCOREB) VALUES (NOW(),...,...,...,...)
ELSE
'PERFORM THE UPDATE CODE I PROVIDED ABOVE
END IF
0
 

Author Comment

by:DColin
ID: 21840046
jgoeders:

Thank you your solution works great.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question