Solved

Query Help help

Posted on 2014-02-24
5
274 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 35

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 35

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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