Solved

Update Db only when information has changed

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

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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