I am asked to display this, "List the Client Numbers, Name, and Balance fields in the design grid. Sort the records in desending order by the Balance Field."
AND THIS: "Display the top 25%"
I need to know how to show the top "25%" when I press run, in the datasheet without manually having to arrange them.
I would love detailed instructions ASAP!
Hello,
I was hoping someone on this forum might be able to help me.
I am trying to update a table with info in another table, using a nested query.
strSQL = "UPDATE tblTemp SET AdminNum = '" & cboAdmin.Text & "', " & _
"Size = (SELECT Size FROM tblEquipList WHERE AdminNum = '" & cboAdmin.Text & "')"
DoCmd.RunSQL strSQL
cboAdmin is a combo box on my form that is populated by tblEquipList.AdminNum.
When run this gives me:
Runtime Error '3073':
Operation must use an updateable query.
The SELECT statement works by itself, and the first part of the UPDATE (without the nested select) works by itself, but not when I combine them. I also need to update other things in the same manner in which Size is being updated, but when I can get this to work it shouldn't be a problem just added the updates for the other fields in there.
Thanks in advance,
Jeremiah
Edit:
Also, I figured I should add that when I remove the SELECT statment and put in a number (IE: Size = '400'), the query runs just fine. So I know the problem lies in the nesting, but am unsure how.
I have tried to create a form page that when filled in by a user updates a database.
However i get his error messge
Code:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./tpvsite/RegisterUser.asp, line 62
In my page it aligns to this line objRS.Update. Which i think means there is a problem with my database as anyone any ideas how to fix this. (all my tables and fields are correct)
Cheers
Can someone tell me why this query is causing this error? I don't understand it:
UPDATE 1_2_06 SET ROLE = (SELECT ROLE FROM UserRoleData WHERE USERNAME=ASSIGNED_TO)
WHERE EXISTS (SELECT ROLE FROM UserRoleData WHERE USERNAME=ASSIGNED_TO);
thank you
I am trying to update rows in an access database by retrieving data from dynamic text boxes created through a loop. I keep getting the error message: System.Data.OleDb.OleDbException: Operation must use an updateable query. My code is as follows:
Dim myInsertQuery AsString
myInsertQuery = "INSERT INTO tblTestQues (Answer) Values ("txt" & loop1);"
Dim myCommand2 AsNew OleDbCommand(myInsertQuery)
myCommand2.Connection = MyConnection
MyConnection.Open()
myCommand2.ExecuteNonQuery()
myCommand2.Connection.Close()
Please help or contact me if you need more information. Thanks in advanced.
asp code:
Code:<%Set Con = Server.CreateObject("ADODB.Connection") Con.Open "db"dim username, password, mailusername = Request.form("username")password = Request.form("password")mail = Request.form("mail")sql= "INSERT INTO users(username) values('" & username & "')"Con.Execute sqlCon.closeset Con = nothing%>
error:
Code:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./site/content/register_act.asp, line 13
It's says it has something to do with permissions...
I set writing & reading permissions for the db and the directory that contains it through
contorl panel->Administrative Tools->Internet Information Services
I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS
in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again...
what should I do?
I have subform where access lists orderitems(the main form contains the order info). The subform uses a query to lookup items thats in the orderlist. Everything works fine execpt when I change something in a dropdowncombo list in the orderlist sheet(like in sample northwind db) the I get "You cancelled the previous operation". The post IS changed and everything seems to work nice but why do I get this error?
The combo uses "SELECT DISTINCT Items.IDItem, Items.Item FROM Items;" as rowsource and also "ListedItems.IDItem" as controlsource.
Hi,
As an Access novice I am trying to do a simple update of a table but keep getting an annoying error:
I have 2 tables - Products and OrderDetails
Products
ProductID
Description
StockQuantity
OrderDetails
OrderID
ProductID
Quantity
Basically I want to update the Products.StockQuantity column to subtract the sum of all orders in the OrderDetails table.
So if Product A started with a StockQuantity of 5, and there are 3 Order_Details record, each with a quantity of 2, the query should update the Products.StockQuantity record to -1.
I have tried a few permutations such as:
UPDATE PRODUCTS
set PRODUCTS.StockQuantity = PRODUCTS.StockQuantity - (
SELECT SUM(OrderDetails.Quantity)
FROM ORDERDETAILS
WHERE ORDERDETAILS.ProductID = PRODUCTS.ProductID);
but I seem to keep getting the following error:
"Operation must use an updateable query"
Help - I'm tearing my hair out here!!
Thanks,
Keith
I tried to update a column in a table (tblReservedPart) from a query (qryF4101) using Update Query. Here is the code:
UPDATE tblReservedPart INNER JOIN qryF4101 ON [tblReservedPart].[PN]=[qryF4101].[PartCode] SET tblReservedPart.strProductLine = [qryF4101].[IMGLPT]
WHERE [tblReservedPart].[PN]=[qryF4101].[PartCode];
I received an error "Operation must use an updatable query." I am not updating the query. What is the error complaining?
Thanks.
DanYeung
Hi,
I was wondering if i could get some help here. I got problem while trying to perform sql query. I got an error message saying
"No value given for one or more required parameters".
I think i know what caused this but i just don't know how to get around this.
The query I'm trying to perform requires 3 tables and 2 queries.
Tables: Projects, Plan_Names, Reason
Queries: PSQuery, ProjectQuery
I think this is due to the fact that those two queries are empty for a start. Hence, it needs more value.
My question is how do i in the code, tell it to execute the two queries before attempting the query i want to perform.
Thank you in advance
I have a query where I need to find out the average score of employees over a date range. I have a field called date where I enter a date range (Between [START DATE (MM/DD/YY):] And [END DATE (MM/DD/YY):]) and another field where I enter the name of the supervisor. Now the table I have created contains the scores on a daily basis. So what I want is that when I enter the date and the name of the supervisor it should give me the average of the scores over that date range of all the employees under that supervisor along with the name of the employee.
View 1 Replies View RelatedI have two tables, a Property table and a System table. They are linked so that there are many Systems linked to one Property.
Each System has a risk score and these vary within each property. My Property table has a Property risk score field and in here i would like the maximum System risk score to go.
I'm just wondering how i would do this using a query? All the system risk scores have been entered already.
Any information would be great!!
Matt
Hello everybody,
I am unsure whether this is best done in SQL or VBA, however, I do believe it is possible in SQL so I will put it here.
I have a field 'value of purchase' in which an items cost is inserted. What I am wanting to do initially is automatically deduct 20% off the value of purchase every year until it reaches zero. Now I don't want to deduct 20% off the new value every year, for example:
The price is $1000, 20% deduction is $800. But then year 2 it will be $600 then the next year $400 and so on. So I suppose the first year 20% is taken off the original price, then for year 2 40% and so on (always off the original price not the newly found values).
I want to display this in a new field 'current price'.
I am just not sure how to create the query so it will automatically deduct these amounts per year. If anybody has any advice/help it is muchly appreciated.
Thanks :)
I am running a report from a form that is to produce an excel output:
Private Sub cmdTransferToExcel_Click()
On Error GoTo Err_CmdTransferToExcel_Click
Refresh
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, "Temps_Margin_Output", acFormatXLS, [Forms]![Revenue_Output]![output], False
Exit_CmdTransferToExcel_Click:
DoCmd.Hourglass False
MsgBox "File Created", vbOKOnly
Exit Sub
Err_CmdTransferToExcel_Click:
MsgBox Err.Description
Resume Exit_CmdTransferToExcel_Click
End Sub
but receive an error "Invalid Operation"
Can anyone suggest anything?
Thanks
hello,
I have a table with "fieldA", "filedB", and "filedC". I want "fieldC" to be the result of multiplying "fieldA" and "filedB". How can I do this?? I don't want to have to type in the result in "fieldC", I want this to be done automatically when I fill up "fieldA" and "filedB."
thank you very much
I have a calculated query field that says this:
1 wk Wins: IIf(([1 week price]-[PRICE])/[PRICE]*100>=[s],1,0)
It prompt me for the variable [s] when I run it, as it should.
However, the results for whatever I type for [s] are not the same as the results if I just put the number in the query instead, ie:
1 wk Wins: IIf(([1 week price]-[PRICE])/[PRICE]*100>=5,1,0)
The above should return the same results as if I input 5 for the parameter prompt for [s], but they do not, and I cannot for the life of tell what Access is actually doing with the number I input for [s]. I cannot see a trend in its results from record to record, it just seems random.
Any thoughts on this appreciated.
thanks,
andrew
I have a database that was designed for our needs (not by me) and I have limited database experience. I am looking for someone who can guide me through simple query modifications to existing queries for a flat fee. Please respond if interested and the amount charged for services.
Chris O'Reilly
I'm missing a bit of experience dealing with this query. It's very simple. I'm selecting log activities from COMLOG. However I only want the most recent DateStamp entry of each Cust_FK entry listed.
Because there are more logactivities, there are more/identical Cust_FK values. Would I first have to group these?? I just don't know how I would go about it.
SELECT COMLOG.LogID, COMLOG.DateStamp, COMLOG.Cust_FK, COMLOG.LogActivity
FROM COMLOG;
thanks
Hi all,
I've searched the forums but can't seem to find anything similar. I work for social services and we have a database that stores information on children who may need services from adults when they reach 18.
What I need to do is create a query (so that a report can be run) that will show those children who will turn 18 during the year Apr 1st - Mar 31st but I'm not sure where to start. Can anyone offer any pointers please?
The fields within the database are:
Client ID, Name, Surname, Date of Birth, Address
Many thanks
I have DB with a Switchboard that has a button for Bin Number (Part Number). It is based on a parameter query that asks you to input your Bin Number. Once you enter the Bin Number it pulls all information for that Bin Number from a table that contains the Bin Number, description, Manufacturer P/N and Manufacturer and shows this in the query.
What I need to do is have this information each time a different Bin Number is entered to be stored in specific table (datasheet style) for printing out just a list of specific Bin Numbers and their information. As the parameter query stands now, each time I input a different Bin Number it clears the previous information. Can I tie in a make table query in some manner that will store the selected Bin Number info?
Can someone make suggestions on the easiest and quickest way to set this up? Keep in mind I am still learning Access so please be detailed in your comments. Thanks in advance for any assistance.
Hello everyone,
I’m looking for some help with a “nested query”
I have four fields that typically have a quantity amount in them.
I also have a field with a date.
I would like to say something like this
If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank
Thanks a lot
Corey
Ok, here's what I've got.
I have an access database that links to some SQL Server tables. These tables hold time tickets which keep track of the time that an employee is logged into a job and records the associated product code and work code. All jobs have a work code, and some have a product code. The product coce is the most accurate way to track time, so if a product code is present I need to use that. If it's not present I use the work code.
I currently have a query called 'main' that gets all records in a specified date range and creates a 'code' column. This is a hybrid of the product code and work code using the method described above.
IIf([ProdCode],[ProdCode],[WorkCode])
Everything looks fine there, but when I go to a new query to get total hours by code ('TotalHoursPerCode') by grouping code and getting the sum of ManHour, it doesn't work.
Instead, it lumps all lines that have a product code together but then combines all lines that have no product code into a single line at the top. Instead of using the 'code' column to group.
Here's the SQL from the 'TotalHoursPerCode' query.
SELECT Main.Code, Sum(Main.ManHrs) AS SumOfManHrs
FROM Main
GROUP BY Main.Code;
Any ideas??
Thanks!
http://i45.photobucket.com/albums/f73/VolcomGrrrl79/access.jpg
Hi,
I have data that I imported into access (Table1). Basically, the file shows two values with Header, Component. I need to group the Component together with a delimiter & (or any symbol) when the header is the same.
For example of what I have:
Table1
Field 1 – Header
Field 2 - Component
FG1 | SA1
FG1 | SA2
FG1 | CO1
FG2 | SA3
FG2 | CO2
What I am trying to get is
Table2
Field 1 – Header
Field 2 - Join Component
FG1 | SA1&SA2&CO1
FG2 | SA3&CO2
Thanks
Andrew
Hi everybody,
Let's say that I've got the following tables:
- tblCustomers(IDCust,NameCust)
- tblEmployees(IDEmpl,NameEmpl)
- tblProjects(IDProj,IDCust,IDEmpl,Duration)
My query:
SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust
GROUP BY tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;
What I'd like get when running my query is:
NameCust ------ IDProj - Duration ----- NameEmpl ------- ?????????
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 7
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 9
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 9
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 17
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 4
CUST2 --------- 10 ------ 5 ------------ EMPL3 --------- 17
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 4
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 27
CUST3 --------- 14 ------ 2 ------------ EMPL1 --------- 14
CUST3 --------- 15 ------ 2 ------------ EMPL4 --------- 27
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 14
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2
where ???????? (manually done) = the sum of the Employee duration for ONE Customer !!!
But what I get instead:
NameCust ------ IDProj - Duration ----- NameEmpl ------- SumOfDuration
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 3
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 8
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 1
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 12
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 3
CUST2 --------- 10 ------ 5 ------------ EMPL3 ---------- 5
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 1
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 25
CUST3 --------- 14 ------ 2 ------------ EMPL1 ---------- 2
CUST3 --------- 15 ------ 2 ------------ EMPL4 ---------- 2
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 12
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2
Any suggestion to build this Query ??
Thanks in advance
Great forum guys! Just had to join it :)
I've been doing Access for just under 2 weeks now, so far I'm doing ok, but I've just run into 2 problems. I hope someone can help me on them.
The first problem is on a query. I have a Column called "Type" in my query and the choices under Type could be RECEIPT, REJECT, BOTH
(BTW: BOTH should show all records RECEIPT and REJECT)
By using case statements as shown below, i can easily return records for RECEIPT and REJECT, however, i cannot get BOTH to work
The criteria i have set for TYPE in my query is,
[forms]![frm_Vendor]![opta] OR [forms]![frm_Vendor]![opta] Is Null
Case 1
opta.Value = "RECEIPT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 2
opta.Value = "REJECT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 3
opta.Value = " "
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 3 does not work. with " " . So what do i do here to show Both records?
My second problem is with the reports opened with the above Case code. The search routine (apart from Both) work fine. It finds the records i ask of it and it opens a report to show them, however, there are instances where no records are found, and the report still opens!!. In my search form, what code can i use to test my query for "null" data before
it even gets a chance to open the report?
Thanks in advance for any help.
i am trying to update a field within a table. But i dont want to update the whole of the data within the field just one part of it.
The field is the Tutor Group for students in the format 7B, 7D, 7F, 7H, 7M, 7S, 8B, 8D, 8F,... etc where the number represents the year group! i want to use an expression that would add(+) 1 to the year group but leave the letter the same as this doesn change. By doing this the above list would change to 8B, 8D, 8F, 8H, 8M, 8S, 9B, 9D, 9F, ...etc
I attempted to use the following expression: ((*)+1)(*) but it didn't work.
I should have had the year groups separate but i am given an Excel flat file which has details of the students and doesnt separate the the two.
any help would be much appreciated! hope someone can help!