Solved

Help with Update statement

Posted on 2013-11-14
13
356 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

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 earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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