Solved

Query Help help

Posted on 2014-02-24
5
287 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 38

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 38

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

632 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