Solved

Query Help help

Posted on 2014-02-24
5
265 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
  • 3
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
PatHartman

Thank you
0
 

Author Closing Comment

by:lulu50
Comment Utility
Thanks,  me no English and no access knowledge GREAT!
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Join & Write a Comment

Local Printing Using Remote Desktop Windows 7 sometimes has issues with printing to a local printer using a Remote Desktop Connection (RDC). The 1st step is to verify that printers are checked on the Local Resources tab of the Remote Desktop C…
Like many organizations, your foray into cloud computing may have started with an ancillary or security service, like email spam and virus protection. For some, the first or second step into the cloud was moving email off-premise. For others, a clou…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now