Update Query

[Paul W Smith]

I have two tables, one contains the highest score each team has ever
achieved - this has been manually built loaded from the leagues historical
records.

I written a query that produces, for each team, it's highest score of the
current season.

I am to know how I would go about writing an update query that would update
my first table where the value in the second table is greater than that in
the first.

 

[Rick B]

Generally, you would not need that high score table. When you run a query,
it is easy to pull the highest score for a given team. It would also be
up-to-the-minute. If a team breaks their record during the current season,
your high score table is inaccurate until the end of the season when you run
this update you are talking about.

 

[Duane Hookom]

You might need to use DMax() in your update query. If you had some table and
field names to share, someone would probably be able to provide the exact
sql statement.

 

[Paul W Smith]

Granted what you say is true.

My problem is getting the historic information into the database. I do not
have all the necessary data to create a record for the previous historic
high score, so what I need is a way to have a position so far, which can
then be updated.

If anyone has an alternative idea please let me know.

 

[Rick B]

I'd probably just add one record for the previous high score into my single
table. Then you would have that record. If any are higher in the future,
then they will be selected and that original record will never be looked at
again.

Another post here explains how to build a query to pull the highest record.

 

[Paul W Smith]

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore

 

[Paul W Smith]

I do not think I am making myself clear to you. I cannot just add a record
into my current table for a historic performance - I do not have the
required additional data.

I therefore have to add the old and compare this against the new.

 

[Rick B]

If you do not have the required add'l data, just enter X's. It is one
record for each team.

 

[Duane Hookom]

Try something like this. I would try it on copies of your data before using
it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")

 

[Paul W Smith]

Thanks for you help, but you are not understanding.

The table which gets updated whenever a match is played contains more than
just the score. I do not have this information for historic games.
Therefore I cannot just add historic records.

I am hopeful that Duane's advice might produce a answer for me, so again
many thanks.

 

[Paul W Smith]

No good......

UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID =" &
ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID =" &
ClubID);

Gives the error:

Compile error. in query expression 'DLookup("MaxScore","qry_TeamHighScores",
"ClubID=" & ClubID)'.

I apprecaite you said try something like this, but there is some syntax
problem that I cannot solve.

BTW ClubID is a number.

Try something like this. I would try it on copies of your data before
using it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore

 

[Rick B]

No problem. We must be miscommunicating. I am not saying to add in all the
historical data. Just add one record for their previous high score. Let's
say "The Bears" got a high score of 26 in September of 2004. They played
many games before that and many after that, but none of them matter. Just
add in that one particular game and the score. Then, from this point
forward, log all your games just like you normally would.

When you run your query to find the "high score" it will search through all
the games in your database that "The Bears" have played. It will find all
the current games, plus one old game out there from September of 2004. If
they have not broken their record since September 2004, then that is the
record that will pull up. You will then know that their high score is 26
and that they did it 09/04. If they have scored higher since then, the new
high score would be the one selected.

This will require only one table. The table would have the TeamName, The
Date of the game, and the score. That's all. You could query this table
and pull the DMAX or TOP to grab the highest score.

All you have to do is look at your current Table1 and make one new record
for each Team.

Current Table1...
The Bears 26
The Whitesox 12
The Jets 24
etc.

New Table...

09/01/04 The Bears 26
01/07/06 The Bears 14
01/14/06 The Bears 12
08/01/04 The Whitesox 12
01/07/06 The Whitesox 16
01/14/06 The Whitesox 8
12/01/04 The Jets 24
01/07/06 The Jets 12
01/14/06 The Jets 16


If you don't know the date of their previous high score, then put in some
bogus date like 01/01/1900.

 

[Paul W Smith]

What you were suggesting I have always understood...you have not tried to
understand what I have been replying.

I CANNOT JUST ENTER HISTORIC DATA IN THE CURRENT DATA TABLE!

The current data is interdependent on other tables.

Each Score line must have a Pair Reference from the Pairing table. Each
pair, and there are two for each fixture must reference a reference in the
fixture table. Each Fixture must reference a season from the season table.
This high score is a minor issue and the database design has been create to
fulfil other criteria.

I appreciate your help, but really you are going down a blind alley on this
one as I have tried to point out.

If I could get the other line of update query working I would have
everything I need alas I cannot.

Funny I cannot seem to get DLookup or DSum to work in any form!

 

[Duane Hookom]

Apparently you didn't provide actual object names...
What is the SQL view of qry_TeamHighScores?

--
Duane Hookom
MS Access MVP
--

No good......

UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID ="
& ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID ="
& ClubID);

Gives the error:

Compile error. in query expression
'DLookup("MaxScore","qry_TeamHighScores", "ClubID=" & ClubID)'.

I apprecaite you said try something like this, but there is some syntax
problem that I cannot solve.

BTW ClubID is a number.

Try something like this. I would try it on copies of your data before
using it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore


You might need to use DMax() in your update query. If you had some
table and field names to share, someone would probably be able to
provide the exact sql statement.

--
Duane Hookom
MS Access MVP
--

I have two tables, one contains the highest score each team has ever
achieved - this has been manually built loaded from the leagues
historical records.

I written a query that produces, for each team, it's highest score of
the current season.

I am to know how I would go about writing an update query that would
update my first table where the value in the second table is greater
than that in the first.

 

[Rick B]

AHHHHH. Nowhere in any of your previous posts could I find where you
indicated that this is all related to other tables. That information might
have helped


Sorry.

 

[Paul W Smith]

tClubs = Name of table to be updated.

HighScore_Score = field in tClubs which needs to be updated.

qry_TeamHighScores = is the query that delivers the high score which needs
to update tClubs if it is greater than the score already held there.

MaxScore = field name in qryTeamHighScores which contains the high score.

ClubID = numeric value that maps tClubs to qry_TeamHighScores.

Apparently you didn't provide actual object names...
What is the SQL view of qry_TeamHighScores?

--
Duane Hookom
MS Access MVP
--

No good......

UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID ="
& ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID ="
& ClubID);

Gives the error:

Compile error. in query expression
'DLookup("MaxScore","qry_TeamHighScores", "ClubID=" & ClubID)'.

I apprecaite you said try something like this, but there is some syntax
problem that I cannot solve.

BTW ClubID is a number.

Try something like this. I would try it on copies of your data before
using it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore


You might need to use DMax() in your update query. If you had some
table and field names to share, someone would probably be able to
provide the exact sql statement.

--
Duane Hookom
MS Access MVP
--

I have two tables, one contains the highest score each team has ever
achieved - this has been manually built loaded from the leagues
historical records.

I written a query that produces, for each team, it's highest score of
the current season.

I am to know how I would go about writing an update query that would
update my first table where the value in the second table is greater
than that in the first.

 

[Paul W Smith]

"I do not have all the necessary data to create a record for the previous
historic
high score"

I never mentioned I had other tables, but if you read the above and believe
it you realise your solution, while valid for others will not work for me.

Thank you for giving my issue your attention.

 

[Paul W Smith]

I cannot get DLookup or DMax to work in any shape or form - I must have some
problem with my Access set-up.

This test does not work.

I have a table (called 'Test') with two fields, 'ID' and 'Score'.

On the first field of a new query I entered - Expr1:
DMax("[Score]","[Test]"), I got the error:

Compile error, in query expression 'DMax("[Score]","[Test]")'

Next I removed the square brackets - Expr1: DMax("Score","Test"), I got the
error

Compile error, in query expression 'DMax("Score","Test")'

Do I need some add-in installed to get DLookup and DMax to work???????

Apparently you didn't provide actual object names...
What is the SQL view of qry_TeamHighScores?

--
Duane Hookom
MS Access MVP
--

No good......

UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID ="
& ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID ="
& ClubID);

Gives the error:

Compile error. in query expression
'DLookup("MaxScore","qry_TeamHighScores", "ClubID=" & ClubID)'.

I apprecaite you said try something like this, but there is some syntax
problem that I cannot solve.

BTW ClubID is a number.

Try something like this. I would try it on copies of your data before
using it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore


You might need to use DMax() in your update query. If you had some
table and field names to share, someone would probably be able to
provide the exact sql statement.

--
Duane Hookom
MS Access MVP
--

I have two tables, one contains the highest score each team has ever
achieved - this has been manually built loaded from the leagues
historical records.

I written a query that produces, for each team, it's highest score of
the current season.

I am to know how I would go about writing an update query that would
update my first table where the value in the second table is greater
than that in the first.

 

[Duane Hookom]

Have you tried to compile your application? You may need to check your
references.

--
Duane Hookom
MS Access MVP
--

I cannot get DLookup or DMax to work in any shape or form - I must have
some problem with my Access set-up.

This test does not work.

I have a table (called 'Test') with two fields, 'ID' and 'Score'.

On the first field of a new query I entered - Expr1:
DMax("[Score]","[Test]"), I got the error:

Compile error, in query expression 'DMax("[Score]","[Test]")'

Next I removed the square brackets - Expr1: DMax("Score","Test"), I got
the error

Compile error, in query expression 'DMax("Score","Test")'

Do I need some add-in installed to get DLookup and DMax to work???????

Apparently you didn't provide actual object names...
What is the SQL view of qry_TeamHighScores?

--
Duane Hookom
MS Access MVP
--

No good......

UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID
=" & ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID
=" & ClubID);

Gives the error:

Compile error. in query expression
'DLookup("MaxScore","qry_TeamHighScores", "ClubID=" & ClubID)'.

I apprecaite you said try something like this, but there is some syntax
problem that I cannot solve.

BTW ClubID is a number.


Try something like this. I would try it on copies of your data before
using it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore


You might need to use DMax() in your update query. If you had some
table and field names to share, someone would probably be able to
provide the exact sql statement.

--
Duane Hookom
MS Access MVP
--

I have two tables, one contains the highest score each team has ever
achieved - this has been manually built loaded from the leagues
historical records.

I written a query that produces, for each team, it's highest score
of the current season.

I am to know how I would go about writing an update query that would
update my first table where the value in the second table is greater
than that in the first.

 

[Paul W Smith]

Thanks Duane, everything works fine now.

However I now need more information about the high score performance. If
you have time have a look at my other post "Grouping Problem". Any advice
(or better a solution) will be very well received.

Have you tried to compile your application? You may need to check your
references.

--
Duane Hookom
MS Access MVP
--

I cannot get DLookup or DMax to work in any shape or form - I must have
some problem with my Access set-up.

This test does not work.

I have a table (called 'Test') with two fields, 'ID' and 'Score'.

On the first field of a new query I entered - Expr1:
DMax("[Score]","[Test]"), I got the error:

Compile error, in query expression 'DMax("[Score]","[Test]")'

Next I removed the square brackets - Expr1: DMax("Score","Test"), I got
the error

Compile error, in query expression 'DMax("Score","Test")'

Do I need some add-in installed to get DLookup and DMax to work???????

Apparently you didn't provide actual object names...
What is the SQL view of qry_TeamHighScores?

--
Duane Hookom
MS Access MVP
--

No good......

UPDATE tClubs
SET HighScore_Score = DLookup("MaxScore","qry_TeamHighScores", "ClubID
=" & ClubID)
WHERE HighScore_Score<DLookup("MaxScore","qry_TeamHighScores", "ClubID
=" & ClubID);

Gives the error:

Compile error. in query expression
'DLookup("MaxScore","qry_TeamHighScores", "ClubID=" & ClubID)'.

I apprecaite you said try something like this, but there is some syntax
problem that I cannot solve.

BTW ClubID is a number.


Try something like this. I would try it on copies of your data before
using it on production data.

UPDATE [Table 1]
SET HighScore = DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)
WHERE HighScore<DLookup("MaxScore","[Query 2]","ClubID=" & ClubID)

This assumes ClubID is numeric. If it is text use
DLookup("MaxScore","[Query 2]","ClubID=""" & ClubID & """")
--
Duane Hookom
MS Access MVP
--

Table 1
ClubID
HighScore

Query 2
ClubID
MaxScore


You might need to use DMax() in your update query. If you had some
table and field names to share, someone would probably be able to
provide the exact sql statement.

--
Duane Hookom
MS Access MVP
--

I have two tables, one contains the highest score each team has ever
achieved - this has been manually built loaded from the leagues
historical records.

I written a query that produces, for each team, it's highest score
of the current season.

I am to know how I would go about writing an update query that
would update my first table where the value in the second table is
greater than that in the first.