• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Query Help help

Hi,


I have an error:

it says operation must use an updateable query

not sure what that mean or how to fix it.


UPDATE Report_2_MTD_MOS_Containment SET Report_2_MTD_MOS_Containment.Contained_Calls = (SELECT Count(*) AS Counts
FROM AUD_MOS_IVR_LOG
WHERE (((AUD_MOS_IVR_LOG.[unique_key_id]) In (select unique_key_id
    from AUD_MOS_IVR_LOG where
((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#2/20/2014# And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=#2/21/2014#)
 And AUD_MOS_IVR_LOG.record_type = 'CB' And
AUD_MOS_IVR_LOG.data_1 in ('1718','8882881718')
 )) AND ((AUD_MOS_IVR_LOG.record_type)='TE')) AND
AUD_MOS_IVR_LOG.data_3 in ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS') )
WHERE (((Report_2_MTD_MOS_Containment.CreatedDate)=#2/21/2014#) AND ((Report_2_MTD_MOS_Containment.Dialed_Number)='8882881718'));
0
lulu50
Asked:
lulu50
  • 3
  • 2
1 Solution
 
PatHartmanCommented:
In order for a query to be updateable, all parts of the query must be updateable.  Part of your query includes aggregation (Count(*)).  Once data has been aggregated, the original records are no longer identifiable and that makes the query not updateable.

Generally we do not store calculated values.  It is best to calculate them as you need them.  Once you store a calculated value, it immediately becomes obsolete as soon as some data it is reliant upon is changed.  That makes all calculated values suspect.   You always have to ask yourself - when was this last calculated?  Has the underlying data been updated since.

That said, there are two approaches for storing calculated values (there are cases when it is useful and necessary - I don't know if this is one)
1. Create a make table query to save the aggregated data to a temp table.  Then join to the temp table to do the update.  The downside of this approach is database bloat.  You will need to compact frequently to keep the database size in check.  Access doesn't reuse work space so if you replace the contents of the temp table with new values, Access simply allocates new space to hold the recreated temp table even though the old one is deleted in this process.  Using delete and append queries has the same effect so that isn't a solution.
2. Create a procedure that reads through the totals query data and updates the matching main table rows using DAO.  This doesn't cause bloat but is more complicated to program since you have to be sufficiently proficient with VBA to create a two-file match.
0
 
lulu50Author Commented:
I am trying to get the count from this select

it says my syntax is wrong.

I want to save my count into the rsCount


 Set rsCount = db.Execute "SELECT Count(*) AS Counts " _
& " from AUD_MOS_IVR_LOG " _
& " WHERE (((AUD_MOS_IVR_LOG.[unique_key_id]) In (select unique_key_id " _
& " from AUD_MOS_IVR_LOG where " _
& " ((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>= #2/21/2014# And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<= #2/22/2014#)" _
& " And AUD_MOS_IVR_LOG.record_type = 'CB' And " _
& " AUD_MOS_IVR_LOG.data_1 in ('1718','8882881718') " _
& " )) AND ((AUD_MOS_IVR_LOG.record_type)='TE')) AND " _
& " AUD_MOS_IVR_LOG.data_3 in ('EC','HU','TC','TT','TB','TH','TN','TM','TI','TE','VT','TS');"
0
 
PatHartmanCommented:
The .Execute method is used for action queries.  This is a select query.  If you want to read it, you would open a recordset using the .OpenRecordset method.  

Since you only want a single column from the query, you might as well use a DLookup() to run the query since that will be only one line of code.
0
 
lulu50Author Commented:
PatHartman

Thank you
0
 
lulu50Author Commented:
Thanks,  me no English and no access knowledge GREAT!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now