|
|
|
date: Tue, 11 Dec 2007 13:40:00 -0800,
group: microsoft.public.access.odbcclientsvr
back
Re: Count Function in an Update Query
Realistically, you shouldn't store computed values in tables. As fellow
Access MVP John Vinson likes to say "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."
Instead, create a query that includes the computed value you want, and use
the query wherever you would otherwise have used the table.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Reinhard" wrote in message
news:5878E06D-82B9-4F94-BA10-F42AC13DDDEF@microsoft.com...
> How do I update a field in 1 table with the count of matching records in a
> 2nd table? I keep getting error 3122 when I attempt this.
>
> My example is
>
> UPDATE Table1 INNER JOIN Table2 ON Table1.Field2 = Table2.Field2
> SET Table1.Field1 = COUNT(Table2.Field2)
> GROUP BY Table2.Field2 ;
date: Tue, 11 Dec 2007 17:14:06 -0500
author: Douglas J. Steele
Re: Count Function in an Update Query
Thanks for the suggestion, but unfortunately I need to store the computed
value. Is there any way to do that via an Update query?
"Douglas J. Steele" wrote:
> Realistically, you shouldn't store computed values in tables. As fellow
> Access MVP John Vinson likes to say "Storing calculated data generally
> accomplishes only three things: it wastes disk space, it wastes time (a disk
> fetch is much slower than almost any reasonable calculation), and it risks
> data validity, since once it's stored in a table either the Total or one of
> the fields that goes into the total may be changed, making the value WRONG."
>
> Instead, create a query that includes the computed value you want, and use
> the query wherever you would otherwise have used the table.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Reinhard" wrote in message
> news:5878E06D-82B9-4F94-BA10-F42AC13DDDEF@microsoft.com...
> > How do I update a field in 1 table with the count of matching records in a
> > 2nd table? I keep getting error 3122 when I attempt this.
> >
> > My example is
> >
> > UPDATE Table1 INNER JOIN Table2 ON Table1.Field2 = Table2.Field2
> > SET Table1.Field1 = COUNT(Table2.Field2)
> > GROUP BY Table2.Field2 ;
>
>
>
date: Tue, 11 Dec 2007 14:25:01 -0800
author: Reinhard
Re: Count Function in an Update Query
No. Queries that include aggregates are not updatable even though logic
says that if the record you want to update is not part of the aggregate they
should be. You could try a subquery that does the aggregation, but I'm not
sure that will work either.
To store the calculated values, run your aggregation inside a make table
query and then use the temp table to update your other table. Delete the
temp table when you are done. Also be aware that creating and deleting temp
tables will bloat the database and cause it to need more frequent
compacting.
And as Doug has already mentioned, storing calculated values is poor
practice. There are almost always other ways.
"Reinhard" wrote in message
news:5584B31A-8277-44D3-B89F-21EAB9AA9CE6@microsoft.com...
> Thanks for the suggestion, but unfortunately I need to store the computed
> value. Is there any way to do that via an Update query?
>
> "Douglas J. Steele" wrote:
>
>> Realistically, you shouldn't store computed values in tables. As fellow
>> Access MVP John Vinson likes to say "Storing calculated data generally
>> accomplishes only three things: it wastes disk space, it wastes time (a
>> disk
>> fetch is much slower than almost any reasonable calculation), and it
>> risks
>> data validity, since once it's stored in a table either the Total or one
>> of
>> the fields that goes into the total may be changed, making the value
>> WRONG."
>>
>> Instead, create a query that includes the computed value you want, and
>> use
>> the query wherever you would otherwise have used the table.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Reinhard" wrote in message
>> news:5878E06D-82B9-4F94-BA10-F42AC13DDDEF@microsoft.com...
>> > How do I update a field in 1 table with the count of matching records
>> > in a
>> > 2nd table? I keep getting error 3122 when I attempt this.
>> >
>> > My example is
>> >
>> > UPDATE Table1 INNER JOIN Table2 ON Table1.Field2 = Table2.Field2
>> > SET Table1.Field1 = COUNT(Table2.Field2)
>> > GROUP BY Table2.Field2 ;
>>
>>
>>
date: Thu, 10 Jan 2008 13:28:07 -0500
author: Pat Hartman please no
|
|
