Update with top values

[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

 

[John Vinson]

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.

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.

 

[Tom Symonds]

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?

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.

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.

 

[John Vinson]

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?

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.

 

[Tom Symonds]

I'll give it a go. As always, many thanks

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.

 

[Tom Symonds]

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.

 

[John Vinson]

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.

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.

 

[Tom Symonds]

Worked perfectly. I think I had the syntax wrong when I tried the same
thing.
Thanks very much for all your help on this.

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.

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.