Solved

Help with Update statement

Posted on 2013-11-14
13
352 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
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!

 
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 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

726 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