• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Update Db only when information has changed

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
DColin
Asked:
DColin
  • 2
  • 2
1 Solution
 
jgoedersCommented:
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
 
DColinAuthor Commented:
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
 
jgoedersCommented:
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
 
DColinAuthor Commented:
jgoeders:

Thank you your solution works great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now