Solved

Update Db only when information has changed

Posted on 2008-06-21
4
230 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 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