Update query?

[Guest]

I have added a new date field to an existing table.
I am able to calcualte this date for about 50% of the records from another
table in the database, the rest I will need to find out from other sources.
I have created a query which gives me the dates I know, however these are
different for different records.
What is the best way to get these dates into my table. I could just copy
and paste the data ensuring both table and query are sorted by ID ascending,
but am interested to find out if there is a better way.
Access 2003.

Thanks for any help.

 

[John Vinson]

What is the best way to get these dates into my table. I could just copy
and paste the data ensuring both table and query are sorted by ID ascending,
but am interested to find out if there is a better way.

Create an Update query joining the two tables by ID; update your
table's datefield to

[othertablename].[fieldname]

The brackets are essential so that it knows to update to the value in
the field, rather than trying (and failing) to update to the field's
name.

John W. Vinson[MVP]

 

[Guest]

Thanks John, but I'm still not sure.
The date I have got is extracted from a table on the many side of a one to
many relationship using a totals query and the minimum function.
The target table is the one side of the relationship.
If I create my grouped query and convert it to an update query I appear to
lose the grouping. Therefore I created a select query to give me the minimum
date and joined this in an update query. This doesn't work.
I haven't used update queries before am I missing something?

What is the best way to get these dates into my table. I could just copy
and paste the data ensuring both table and query are sorted by ID ascending,
but am interested to find out if there is a better way.

Create an Update query joining the two tables by ID; update your
table's datefield to

[othertablename].[fieldname]

The brackets are essential so that it knows to update to the value in
the field, rather than trying (and failing) to update to the field's
name.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John, but I'm still not sure.
The date I have got is extracted from a table on the many side of a one to
many relationship using a totals query and the minimum function.
The target table is the one side of the relationship.
If I create my grouped query and convert it to an update query I appear to
lose the grouping. Therefore I created a select query to give me the minimum
date and joined this in an update query. This doesn't work.
I haven't used update queries before am I missing something?

Ah. Sorry!

An Update query won't work if it includes any Totals operations, even
if (as in this case) it logically should work.

Instead of the Totals query calculating the minimum, you may be able
to use the DMin() function. Since I don't know anything about your
table structure it's impossible to be specific, but you'll need
something like

DMin("[fieldname]", "[tablename]", "<optional criteria>")

See the VBA editor online Help for DMin for further details.

John W. Vinson[MVP]

 

[Guest]

My copy and paste looks like an attractive solution then. I just didn't want
to get into bad habits using a method I knew rather than exploring the
options and other capabilities.

Thank you very much for your help.

Thanks John, but I'm still not sure.
The date I have got is extracted from a table on the many side of a one to
many relationship using a totals query and the minimum function.
The target table is the one side of the relationship.
If I create my grouped query and convert it to an update query I appear to
lose the grouping. Therefore I created a select query to give me the minimum
date and joined this in an update query. This doesn't work.
I haven't used update queries before am I missing something?

Ah. Sorry!

An Update query won't work if it includes any Totals operations, even
if (as in this case) it logically should work.

Instead of the Totals query calculating the minimum, you may be able
to use the DMin() function. Since I don't know anything about your
table structure it's impossible to be specific, but you'll need
something like

DMin("[fieldname]", "[tablename]", "<optional criteria>")

See the VBA editor online Help for DMin for further details.

John W. Vinson[MVP]