Update with top values

  • Thread starter Thread starter Tom Symonds
  • Start date Start date
T

Tom Symonds

Can anyone help with this.

I have a table of 'Stories' with a field 'NextDiaryDate'. This is linked
One to Many to a second table "Diary Entries"
I want to have an update query work through the second table - "Diary
Entries", looking for the earliest date, and then update Stories -
NextDiaryDate with that date. There'll be some other criteria as well but
that's basically it.

I presume its an Update query, using TopValues in some way. Would I need VB
code to step through the Stories records and then perform the query for each
record?

I want this to happen invisibly to the user, so it needs to be done without
a form.

Thanks
 
I want to have an update query work through the second table - "Diary
Entries", looking for the earliest date, and then update Stories -
NextDiaryDate with that date. There'll be some other criteria as well but
that's basically it.
Click to expand...

You'll need a criterion which finds the maximum date - ones first
guess would be a Subquery using the Max() function but that will keep
the whole query from being updateable.

Instead, use the DMin() VBA function in the Query to look up the
earliest date: use a criterion of

=DMin("[datefield]", "[Diary Entries]", <optional criteria>)

Open the VBA editor by typing Ctrl-G (to get to the right Help file)
and press F1, and search for help on DMin for more details.
 
Thought that would be it, thanks. So to find the correct Top Diary Entry
for each Story, am I right in thinking the only way is to write some code to
loop through the Stories table, and perform the Top Values query for each
story?
Its not possible to write an update query to do the lot in one go?


John Vinson said:
I want to have an update query work through the second table - "Diary
Entries", looking for the earliest date, and then update Stories -
NextDiaryDate with that date. There'll be some other criteria as well but
that's basically it.
Click to expand...

You'll need a criterion which finds the maximum date - ones first
guess would be a Subquery using the Max() function but that will keep
the whole query from being updateable.

Instead, use the DMin() VBA function in the Query to look up the
earliest date: use a criterion of

=DMin("[datefield]", "[Diary Entries]", <optional criteria>)

Open the VBA editor by typing Ctrl-G (to get to the right Help file)
and press F1, and search for help on DMin for more details.
Click to expand...
 
Thought that would be it, thanks. So to find the correct Top Diary Entry
for each Story, am I right in thinking the only way is to write some code to
loop through the Stories table, and perform the Top Values query for each
story?
Its not possible to write an update query to do the lot in one go?
Click to expand...

Sorry! I was unnecessarily cryptic there.

No, you do not need code; no, you do not need to run a query multiple
times; yes, you can write an Update Query to do the lot in one go.

To do so, you need to use the DMax() function as a parameter in your
query to select the top value. You don't want or need the Top Values
operation in your query; just use DMax() to select the highest value,
as a criterion in your query. The query will then be updateable.
 
I'll give it a go. As always, many thanks

John Vinson said:
Sorry! I was unnecessarily cryptic there.

No, you do not need code; no, you do not need to run a query multiple
times; yes, you can write an Update Query to do the lot in one go.

To do so, you need to use the DMax() function as a parameter in your
query to select the top value. You don't want or need the Top Values
operation in your query; just use DMax() to select the highest value,
as a criterion in your query. The query will then be updateable.
Click to expand...
 
Still can't get this to work.
I've created a query from the two tables -- Stories and Diary Entries.
Stories are linked to diary dates, one-to-many, via a StoryID field.
I have the Dmin criteria you suggested under the Diary Entries, Date field.
This works very well at finding the next date in the whole table of Diary
Entries. What I want it to do is find the next diary date for EACH story,
all in one go, each time updating a NextDate field in the Stories table.
Presumably I need an extra criteria to make the query only return the first
Diary Entry for each story, but how can this be specified?
Help gratefully received.
 
Still can't get this to work.
I've created a query from the two tables -- Stories and Diary Entries.
Stories are linked to diary dates, one-to-many, via a StoryID field.
I have the Dmin criteria you suggested under the Diary Entries, Date field.
This works very well at finding the next date in the whole table of Diary
Entries. What I want it to do is find the next diary date for EACH story,
all in one go, each time updating a NextDate field in the Stories table.
Presumably I need an extra criteria to make the query only return the first
Diary Entry for each story, but how can this be specified?
Help gratefully received.
Click to expand...

Air code, bear in mind I can't see your tables:

UPDATE [Stories]
SET [NextDate] = DMin("[Date]", "[Diary Entries]", "[StoryID] = " &
[StoryID]);

This will use the storyID from each record in Stories to look up the
earliest date for that storyID in the Diary Entries table, and use
that retrieved date for the update.
 
Worked perfectly. I think I had the syntax wrong when I tried the same
thing.
Thanks very much for all your help on this.


John Vinson said:
Still can't get this to work.
I've created a query from the two tables -- Stories and Diary Entries.
Stories are linked to diary dates, one-to-many, via a StoryID field.
I have the Dmin criteria you suggested under the Diary Entries, Date field.
This works very well at finding the next date in the whole table of Diary
Entries. What I want it to do is find the next diary date for EACH story,
all in one go, each time updating a NextDate field in the Stories table.
Presumably I need an extra criteria to make the query only return the first
Diary Entry for each story, but how can this be specified?
Help gratefully received.
Click to expand...

Air code, bear in mind I can't see your tables:

UPDATE [Stories]
SET [NextDate] = DMin("[Date]", "[Diary Entries]", "[StoryID] = " &
[StoryID]);

This will use the storyID from each record in Stories to look up the
earliest date for that storyID in the Diary Entries table, and use
that retrieved date for the update.
Click to expand...
 
Back
Top