Solved

Query Help help

Posted on 2014-02-24
5
281 Views
Last Modified: 2014-02-24
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
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39883419
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
 

Author Comment

by:lulu50
ID: 39883477
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 39883540
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
 

Author Comment

by:lulu50
ID: 39883699
PatHartman

Thank you
0
 

Author Closing Comment

by:lulu50
ID: 39883703
Thanks,  me no English and no access knowledge GREAT!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

At the beginning of the year, the IT world was taken hostage by the shareholders of LogMeIn. Their free product, which had been free for ten years, all of the sudden became a "pay" product. Now, I am the first person who will say that software maker…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question