Solved

Help with Update statement

Posted on 2013-11-14
13
313 Views
Last Modified: 2013-11-15
Hello, I have a table called tblMDRStatusReports  where I first insert a row of zeros like this:

 Insert into tblMDRStatusReports (QTYMDR, OpenMDR, ClosedMDR, MDRwRMA, MDRwShipperID, MDRSentToAccounting, MDRScrap) Select 0, 0, 0, 0, 0, 0, 0


I now want to come back and update the row with several update statements to populate the row with the correct data

For example I want to use the following statement to update the QTYMDR of the row previously inserted:

Update tblMDRStatusReports Set QTRMDR = ( SELECT COUNT(*) FROM [DMR Tracking] WHERE  [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM')

The first thing that occurs is that I'm asked to provide QTRMDR
Then I get the following error:
operation must use an updateable query.

What is wrong with my update statement. I just want to update the existing row with a count of rows that fall within where parameters.
0
Comment
Question by:gogetsome
  • 8
  • 3
  • 2
13 Comments
 
LVL 4

Expert Comment

by:rshq
Comment Utility
Hi
  Maybe you have a relation between  tblMDRStatusReports  and  [DMR Tracking] .
  If any relation please delete it and test again.
0
 

Author Comment

by:gogetsome
Comment Utility
No relation between the tables. I deleted the row, added it and still have the same issue.
0
 

Author Comment

by:gogetsome
Comment Utility
I thought that perhaps the  table needed a primary key so I added one and changed my query to this:

Update tblMDRStatusReports Set QTRMDR = (SELECT COUNT(*) FROM [DMR Tracking] WHERE  [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM') Where ID=1

Same issue as before.

This part of the statement returns the desired result set.

SELECT COUNT(*)
FROM [DMR Tracking]
WHERE [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM';

This appears to be a simple update statement. What gives?
0
 
LVL 4

Expert Comment

by:rshq
Comment Utility
Please use simple update like this
 Update tblMDRStatusReports Set QTRMDR =5
0
 

Author Comment

by:gogetsome
Comment Utility
Yup, same issue. Why is that?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<The first thing that occurs is that I'm asked to provide QTRMDR>

you don't have a field named "QTRMDR", the name is "QTYMDR"


Update tblMDRStatusReports Set QTYMDR = ( SELECT COUNT(*) FROM [DMR Tracking] WHERE  [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM')
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:gogetsome
Comment Utility
This works:
Update tblMDRStatusReports  Set QTRMDR =5

but this does not

Update tblMDRStatusReports Set QTYMDR = (SELECT COUNT(*) FROM [DMR Tracking] WHERE  [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM')

This works:

SELECT COUNT(*) FROM [DMR Tracking] WHERE  [Date Issued] between #07/22/2013# And #07/22/2013# And Supplier = 'LEPLM'

So why when I combine to I get
operation must use an updateable query.
0
 

Author Comment

by:gogetsome
Comment Utility
From what I have been reading perhaps access can do this sort of query. Such a limitation if that is true.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try this

Update tblMDRStatusReports Set QTYMDR = dcount("*","DMR Tracking","[Date Issued]>=#07/22/2013# and [Date Issued]<=#07/22/2013# And Supplier = 'LEPLM'")
0
 

Author Closing Comment

by:gogetsome
Comment Utility
Very cool! Thank you so much for your assistance!
0
 

Author Comment

by:gogetsome
Comment Utility
Capricorn1, I hate to come back once closed but I need a little help converting the SQL to run from VBA.

My VBA likes the following syntax but I'm getting the following error when I execute the DoCmd.RunSQL

Error:

Error number=3075, syntax error(missing operator) in query expression



DoCmd.RunSQL "Update [tblMDRStatusReports] Set QTYMDR = dcount('*' ,'DMR Tracking','[Date Issued]>=#" & Me.Start_Date & "# and [Date Issued]<=#" & Me.End_Date & "# And Supplier = " & Me.Supplier & ")"

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
DoCmd.RunSQL "Update [tblMDRStatusReports] Set QTYMDR = dcount('*' ,'DMR Tracking','[Date Issued]>=#" & Me.Start_Date & "# and [Date Issued]<=#" & Me.End_Date & "# And Supplier = '" & Me.Supplier & "')"
0
 

Author Comment

by:gogetsome
Comment Utility
Thanks for coming back!
That is giving the following error. See imageerror message
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 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

7 Experts available now in Live!

Get 1:1 Help Now