?
Solved

Help with Update statement

Posted on 2013-11-14
13
Medium Priority
?
358 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
[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
  • 8
  • 3
  • 2
13 Comments
 
LVL 4

Expert Comment

by:rshq
ID: 39649075
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
ID: 39649088
No relation between the tables. I deleted the row, added it and still have the same issue.
0
 

Author Comment

by:gogetsome
ID: 39649151
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 4

Expert Comment

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

Author Comment

by:gogetsome
ID: 39649265
Yup, same issue. Why is that?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39649315
<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
 

Author Comment

by:gogetsome
ID: 39649352
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
ID: 39649416
From what I have been reading perhaps access can do this sort of query. Such a limitation if that is true.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39649551
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
ID: 39651438
Very cool! Thank you so much for your assistance!
0
 

Author Comment

by:gogetsome
ID: 39651508
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39651579
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
ID: 39651591
Thanks for coming back!
That is giving the following error. See imageerror message
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

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