Hi,
I've generated the following SQL statement...
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.
Is this possible using SQL?
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
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.
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
Table: dbo_device
dev_name (prim key)
dev_ip_address
dev_memory
Table: firmware
name (prim key)
fware
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...
------------------SQL---------------------------------
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'
Any help would be greatly appreciated!
Thanks
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 an update query (see attached jpg), but it is not updating. I am running Office 2000 on Windows XP.
Any ideas on where the error is?
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?
Is it possible to update a query without updating the underlying table?
View 2 Replies View RelatedHello everyone
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
Thanks guys
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
%>
<font class="headertext">FAQ Edit / Delete</font><br><br><br>
<center><%=responsewrite%><P></center>
<%
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
%>
<!--#include file="../globalvariables/backglobalvariablez2.asp"-->
FAQ_EDIT.ASP CODE
<!--#include file="../globalvariables/backglobalvariablez.asp"-->
<%
Dim ID
ID = Request.Form("ID")
If ID <> "" Then
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
%>
<font class="headertext">Edit an FAQ</font><br><br><br>
<table cellpadding = 10 width = 450 bgcolor = DDDDDD>
<tr valign = top>
<td>
<form name = workform action = "faq_edit.asp" method = "post">
<b>Subject:</b><br>
<input type = text value = "<%=subject%>" name = "subject" size =83 >
</td>
</tr>
<tr>
<td><b>Question:</b><br>
<textarea name = "question" cols = 60 rows = 7><%=question%></textarea>
</td>
</tr>
<tr>
<td><b>Answer:</b><br>
<textarea name = "answer" cols = 60 rows = 7><%=answer%></textarea>
</td>
</tr>
<tr>
<td align = center>
<input type = hidden value = "<%=Request.Querystring("ID")%>" name = "ID">
<input class = "btn" type = button value = "Update" onclick = "if(document.workform.subject.value=='' || document.workform.question.value=='' || document.workform.answer.value==''){alert('Please fill out all fields')}else{document.workform.submit();}">
<input class = "btn" type = reset value = "Clear Form">
</td>
</tr>
</table>
</center>
<!--#include file="../globalvariables/backglobalvariablez2.asp"-->
Thank You!
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.
Appreciate any thoughts on the above,
G2
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?
View 3 Replies View RelatedThis is driving me absolutely insane, can anyone find why this won't work?
UPDATE tbl_PrivateMessages SET tbl_PrivateMessages.RecipientStatus = 1
WHERE tbl_PrivateMessages.MessageID IN ([Enter MessageIDs]);
I've tried updating it through ASP with the following:
Execute UPDATE_INBOX_MSG_STATUS 0,26,25
And also just tried running the query from access itself using 25,26 as IDs.
Neither of those seem to work...please advise. Also, I don't even get an error message as to why it didn't update.
TABLE STRUCTURE
MessageID - AutoNumber
MessageUserID - Number
MessageSubject - Text
MessageContent - Memo
MessageAuthor - Number
MessageDate - Date/Time
AuthorStatus - Number
RecipientStatus - Number
Many thanks
Steve Johnson
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
GameGenre
GameSubGenre
tblGenres
Genre
tblSubgenres
Subgenre
SubgenreGenre
qrySubgenres
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.
What should I do to get this working properly?
Thanks in advance for any assistance!
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
Cheers
I have a query called providerIds
It appends to the a table called providerIDS
Everything works fine however when ever new data is added to a form
The data shows in the append query but not in the table where it appends to.
How do update the table where the append query is attached to.
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 want to update a field of the table from a field of query .
this is the syntax :
UPDATE tbl_PresentStockAll INNER JOIN qry_BroachStock ON tbl_PresentStockAll.ProductId = qry_BroachStock.ProductId SET tbl_PresentStockAll.Broach = [qry_BroachStock].[BalanceQuantity];
but i am getting error of "operation must use an updateable query".
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.
View 4 Replies View RelatedI'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"