Hi all,
I have been trying to make 2 tables join (the end goal is to overwrite some data in one table, with data in another). The table layouts are as follows
All data types are matching, so that shouldnt be the problem. I want to grab the value fware from the firmware table and put it into the dbo_device 'dev_memory' field, where the dev_name & name are the same. Here is my query...
UPDATE dbo_device
SET dev_memory = f.fware
FROM firmware f
INNER JOIN dbo_device d ON d.dev_name = f.name
------------------/SQL---------------------------------
I am getting the error 'Syntax error (missing operator) in query expression 'f.fware FROM firmware f
INNER join dbo_device d ON d.dev_name = f.name'
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
SELECT (Int([age]/10)*10) AS MYG, Count([MYG]) AS Frequency FROM Data WHERE ((([Data].[Date of referal]) Between #1/1/2006# And #1/1/2007#) And (([Data].[Age])>10)) GROUP BY (Int([age]/10)*10);
the [MYG] field is basically an integer 10,20,30,40,50 etc I wish to change this field to 10 - 20, 20 - 30, 30 - 40 etc.
I have 2 fields in a form called 'Employee No's' which use a combo box (this information for these comes from a query). However, I have another form called 'Employee Details' (this form comes from the same table as the query is taken from).
The 'Employee No's' form opens by a command button in the 'Employee Details' Form. However, what I need to be able to do is for the query (used for the combo box) to update with information I have just put in the 'Employee Details' form while I have not yet closed the 'Employee Details' Form.
Would I use an SQL statement for this? If so what would I put?
In the attached databse, there is an Available Days function in qryMain. I'd like it so that when a day of the week tickbox is checked, to update the query. However I can't seem to get the Available Days function to access information from the query. I've had this working using a form previously, but can't seem to get it working with a query. I know my code is messy, still figuring out access, and VBA stuff.
I appreciate I also posted about this a while back, and that was a big help. Since then though, I've had to rebuild the database, and this is the best version so far. Would love to get it working like I imagine in my head! :confused:
I'd also like to get the frmUserList page working so that when Open User button is selected, it will open the users page, but give access to all user so you can switch through them. There doesn't seem to be a wizard way of doing this, do I need to do more VBA code?
I have a list of sites (approx 15,000) that I need to update. Basically, against each site we store a region code (HR1 - HR7) and we have decided to increase the number of regions we operate with. I have created a spreadsheet that states the post codes included in each new region (NR) and now I want to create a query that looks at the post code of the site, then according to my spreadsheet, changes it to the new region. There are no new sites to be added, simply old ones to be updated.
How can I do this easily? I have imported my spreadhseet into access, and I have all the data there - just unsure of the query to update.....
I have two table Client_details and Release_details in a database,were client field is the primary keyin both the tabels.I want to update records of Airtel-India in Release_details,for that i have wrriten a query
UPDATE Client_detail, Release_detail SET Client_detail.Client = "Airtel-India", Release_detail.Client = "Airtel-India" WHERE (((Client_detail.Client)="Bharati Chennai") AND ((Release_detail.Client)="Bharati Chennai"));
but after running an error message is shown like
Microsoft access didnt update 0 fileds due to a type conversion failure,16 records due to key violations,0 records due to validation rule violations :confused:
I have 2 fields in a form called 'Employee No's' which use a combo box (this information for these comes from a query). However, I have another form called 'Employee Details' (this form comes from the same table as the query is taken from).
The 'Employee No's' form opens by a command button in the 'Employee Details' Form. However, what I need to be able to do is for the query (used for the combo box) to update with information I have just put in the 'Employee Details' form while I have not yet closed the 'Employee Details' Form.
Would I use an SQL statement for this? If so what would I put?
This is my first post on this forum so I was wondering whether you guys could help me.
I import data from a server daliy. My DB has three tables containing print files;
Print: shows leaflets that have been printed Package: shows leaflets that have been packaged Dispatch: shows leaflets that have been dispatched
Since data can only be imported at the end of the business day, I needed to create a query which will show the files that have printed but havent been packaged. To do this I created an unmatched query and tested it, it works fine.
This unmatched query has now been placed in a sub form, I would like the users to add comments next to the rows in the queries. However since the unmatched query cannot be updated I was wondering if there is any way of around it
Good Afternoon--- I'd like to use a query to update a table, however the query is based on several tables. I'm pretty sure this is possible, but I've only used a pre-existing query where this applied. Never had to build one like this from scratch.
Say for example you want to update the contact information for one record that occurs in several different tables without openning every table. I'm not sure that I should even be using a query to solve this problem, but any suggestions would save me a lot of time. Thanks!
Hello, I'm stuck on what seems to be a somewhat simple problem. I have an access db and have an admin area to allow for edits/deletions for the faq's entries. I was able to add in queries to sort the massive amount of data (3000 entries), but when I try to edit or delete one of them, I get the error message that the Database or object is read-only. I am somewhat new to coding, and if anyone could look over the below code and make any recommendations, it would be greatly appreciated.
FAQ_EDITDELETE.ASP CODE <!--#include file="../globalvariables/backglobalvariablez.asp"--> <% Dim ID, responsewrite ID = Request.Querystring("ID") If ID <> "" Then Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open connectionstring Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "Delete from Dilemmas where ID = " & ID objRS.Open strQuery, objCon responsewrite="<font color = green>FAQ Deleted Successfully</font>" Set objRS = Nothing Set objCon = Nothing end if %>
<% Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open connectionstring Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "SELECT * from Dilemmas order by subject asc" objRS.Open strQuery, objCon IF objRS.EOF Then Response.Write("<tr><td colspan = 3>No Faqs</td></tr>") Else Do While not objRS.EOF x = x + 1 %>
<table border = 0 bgcolor = DDDDDD cellpadding = 7 cellspacing = 0 width = 530> <tr bgcolor = CCCCCC> <td align = left><b><%=objRS("subject")%></b></td> <td align = right><input class = "btn" type = button onclick = "top.location.href='faq_edit.asp?id=<%=objRS("ID")%>'" value = "Edit" class = "btn"> <input class = "btn" type = button onclick = "fndelete('<%=objRS("ID")%>');" value = "Delete" class = "btn"></td> </tr> <tr> <td colspan = 2><b>Question:</b><br><%=replace(objRS("question"),vbcrlf,"<BR>")%></td> </tr> <tr> <td colspan = 2><b>Answer:</b><br><%=replace(objRS("answer"),vbcrlf,"<BR>")%></td> </tr> </table> <br> <% objRS.MoveNext Loop End If objRS.Close Set objRS = Nothing Set objCon = Nothing %>
Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open connectionstring Set objRSAddComments = Server.CreateObject("ADODB.Recordset") strQuery = "SELECT * FROM Dilemmas where ID = " & ID & " " objRSAddComments.CursorType = 2 objRSAddComments.LockType = 3 objRSAddComments.Open strQuery, objCon objRSAddComments.Fields("subject")=strformat(request.form("subject")) objRSAddComments.Fields("question")=strformat(request.form("question")) objRSAddComments.Fields("answer")=strformat(request.form("answer")) objRSAddComments.Update objRSAddComments.Close response.redirect("faq_editdelete.asp") end if
ID = Request.Querystring("ID") Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open connectionstring Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "SELECT * from Dilemmas where ID = " & ID objRS.Open strQuery, objCon IF objRS.EOF Then Else dim subject, question, answer subject = objRS("subject") question = objRS("question") answer = objRS("answer") End If objRS.Close Set objRS = Nothing Set objCon = Nothing %>
Let's say I have a table with employee's info In that table there's a field about their salary. In this table I have different pay scales. I have another table with Just pay scales..for instance
employetbl Auto Record EmployeeName Workaddress PayGrade Salary 1 Joe "B" 2 John "C" 3 Steve "A"
salarytbl Auto Record Grade Salary Comments 1 "A" $25.00 Technician 2 "B" $22.00 Administrative 3 "C" $23.00 Operations Spec.
If I were to run a a query from the employee table and pull all the fields to my QBE under design view. and selected my criteria and UpdatedQuery to change the update to...(what do i click to get to change the new salary.. ???.
A) Select the proper criteria and and change the update query to the new salary and do it manually?.... B) Can I somehow follow option "A" but at the end select the proper "grade" under my update query and let that somehow change it to the new salary?...(with out me having to type the salary..but only the salary grade?) C. Or can I just type the auto record under to the updated query . Under my update query to ??.
In other words I am trying to select a salary from me typing a record number or a salary grade field..
Have an MDE file in our equipment, it's a language data base one of the languages isn't fully translated so I did a query to pull out those records that equaled the English field which brought the 1800 lines down to 218 to go over and edit.
BUT
What I am worried about, well our Software Engineers are nervous about is my trying to udate those records via an update query and then putting the MDE back into the machine's computer.
Is there something that I should be careful about while doing this? I know that I shouldn't try to repair and compact as MDE just goes bye bye when that is done, changes into an MDB file.
I have a form that opens a subform with a list of work orders by who they are assigned to. I would like to be able to close any or all of the work orders. When I close them, I need the timestamp to be updated, but I can't seem to figure out just how to make that happen. Any ideas?
I am trying to make a combo box that is controlled by the value in a previous combo box. The first combo box lists genres for games (e.g. RPG, Sports, Action) and the second, when necessary, will expand on this. For example, if Sports is selected in the first, the second would display a list of sports (Football, Hockey etc.).
The following items from my database are relevant to this question:
tblGames uses a lookup wizard to assign the tblGenres list to the GameSubGenre field. Also, tblSubgenres uses a lookup wizard to get the value for SubGenreGenre from tblGenres.
I built the following select query using the expression builder:
SELECT tblSubgenres.Subgenre, tblSubgenres.SubgenreGenre FROM tblSubgenres WHERE (([SubgenreGenre]=Games!GameGenre));
When I run the query, it asks me to enter a value for Games!GameGenre. If I enter a correct value, it lists just the sub-items I want. However, if I set GameSubGenre in tblGames as a combo box that looks up from qrySubgenres, the combo box will remain blank always.
First, let me point out that I am a new Access user, so apologies ahead of time
I have a form which has a field called 'Initials'. I want this field on the form to default to whatever was last entered (into the table called 'Main Table'). I first tried setting the default value with a DLast command, which seemed to work for a day or so, but then it returned values from mid-table - very strange
I then set up a query which does return the value of the last Initials entered (cunningly called 'Last intials entered'), but I can't get the field in the form to default to this value - it comes up with #Name?
Can anyone help, or have I not explained myself very well
I have a table and a query both of which have the same [Resource ID], [Resource Name] fields.
I am trying to update the [CBL_1_Date], and the [CBL_1_kW] fields in the table with the [CBL_x_Date] and [CBL_x_kW] fields of the query. The problem is that when I try to run the query I keep getting "Operation Must Use and Updateable Query" error.
here is the SQL Code
UPDATE tCBLAggregation INNER JOIN qBestof4CBLs_avg ON (tCBLAggregation.[Event Date] = qBestof4CBLs_avg.[Event Start Date]) AND (tCBLAggregation.[Resource ID] = qBestof4CBLs_avg.[Resource ID]) SET tCBLAggregation.CBL_1_Date = [qBestof4CBLs_avg].[CBL_x_Date], tCBLAggregation.CBL_1_kW = [qBestof4CBLs_avg].[AvgOfCBL_x_kW];
I have a query based on a table that has a column called Calculated Card Expiration:
IIf([Card Type Issued]="PIV",[Issue Date]+1095,IIf([Card Type Issued]="Non-PIV",[Issue Date]+1095,IIf([Card Type Issued]="Flash",[Issue Date]+365,Null)))
It worked fine when I ran the query the first time, but now when a Card Type Issued and Issue Date is added or changed it doesn't work.
I have created a query which is based on 2 tables.I now want to use the query to update content as needed in one of the tablesWhen I try to do this it says, Operation must use an updateable query..I used the update feature but it still says must be created in updateable query.
I'm working on a project management database for my fathers small business. Data Entry goes as follows: Users enter a Work Order, and all the parts that must be created for that work order. The parts have different processes (tasks) done to them to be created. The database runs a simple system like this as we want it to work. I am now attempting to allow handling of more advanced work orders. Specifically, some parts require the completion of multiple other parts before they can be created.
Here is how the database should handle these events:
1) User clicks button named "requires other parts" when entering a new part. 2) On click a subform is opened that allows the user to enter which other parts need to be completed first. 3) Once all of the parts are completed the next part can begin.
This is my current solution:
1) A query calculates if a part is complete 2) Some form of logic looks up to see if all the required parts are completed. If the answer is yes, a field named ready changes to "Yes" and if the answer is no, a field named ready changes to "no"
