I'm having a small problem converting a select query I wrote into an update query. Below is my original select statement:
SELECT Mid([address3],1,InStrRev([address3]," ")-4) AS CITYx, Mid([address3],InStrRev([address3]," ")-2,2) AS STATEx, Mid([address3],InStrRev([address3]," ")+1,10) AS ZIPx, [address3] AS Expr1
FROM Exercise2
Where right([address3],1) <> "E" and address3 is not null;
The above statement basically parses the address field. Now what I need is an update statement that will use the above code. I'm needing to update the empty fields for city, state, and zip from the field address3 which contains all 3 combined.
i have searched /researched and looked at hundreds of posts, and no luck.
Im litlle bit out of my depth, i have posted this before but just got confused.
i have two update querys, the first one identifys if a persons age (by date of birth) is under 16 a check box identifys if this person is "ticked" as junior.
the second update query identifys by (date of birth) if the person is over 16 a check box identifys if this person is "ticked" as senior.
The results are returned into my main query in two columns, these columns return -1 or 0 for junior or senior.
I need to produce a report that returns J or S (Junior or Senior) is there a way of converting these results to one column and converting the -1, 0 to J or S.
Please see attached file.
very lost, still searching for the answer. been on this foe three weeks now.
Could someone tell me is it possible to convert a particular column from 0/-1 to show y/n instead? Its just a simple select statement I'm doing and I need a Y or N instead or numeric!
i have two tables in a one to many relationship, TBL_submissions is a table containing global information about claims submitted by contractors for work done (invoice number, total claim value, contractor etc..), each submission being a batch of claims for specific jobs (measures)
TBL_Submissions is in a one to many relationship with TBL_Measures because one submmsion contains information about many measures. Each record in the Measures table contains only one field IDSubmission relating to this global information, that is IDSubmission which is an autonumber primary key of the submissions table, i.e it is foreign key in TBL_Measures
Each submission comes in to us as an excel file and there is a form where users fill in the global information into text boxes and combo boxes then click an import button browse to the excel file and it gets pulled into a temporary table TBL_TMPSubmission
a query adds an IDSubmission field to this temporary table and then what i want to do is fill it with the autonumber of the record i have just added to TBL_Submissions by pulling all the global information from the form - i can then use a simple append query to load all the new data into TBL_Measures
The buit to add the IDSubmission filed to the temp table works fine and the append query is easy enough i had that going without the ID filed before i added relationships to my database i am trying to fill in the last entered ID with the following statement
UPDATE [TBL_TmpSubmission] SET IDSubmission = (SELECT MAX(IDSubmission) FROM TBL_Submissions)
but i get the error "operation must use an updatable query"
is this a simple syntax error or am i going about this the wrong way?
The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.
When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.
However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.
How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.
Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.
The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.
This is my code in the "on close" event:
DoCmd.OpenQuery "qryUpdateEmailMinimal_True
and here's the SQL:
UPDATE tblInventory SET tblInventory.emailSentMinimal = True;
I assume what is missing is a reference to an inventoryID number. How do I do that?
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
right i have been searching this forum looking for answers to my problem with no luck - i have been searching other forums and i now know how to do this using oracle database which isn't much help because i am using access!
what i want to do is a simple update setting the value of one field but i want to do it on only the records which are returned by a select statement
apparantly oracle has a construct
SELECT <select query here> FOR UPDATE <update query here>
can you do this in access? or is there a workaround?
specifically what i want to do is to read in an excel file which contains all the fields for some records which are already in the database
the table they are in has a valid field (boolean) which when false effectivly means they are deleted from db (we use this instead of actually deleteing so we can duplicate search against previously held records)
i want to find all the records which are in said excel file and set valid to false
so the two parts of this are pretty simple the update is simple, the select is simple but i need to put them together
Hey All!! This is what I wanna get done. I have a big table I am querying into and extracting data and populating it into the new tables that I have constructed.
For eg: SELECT dbo_analyst.anls_id, [fst_name] & [lst_name] AS Analyst FROM dbo_analyst;
This query selects the name and the ID of the Analyst out of the big table. I want to store these values in the Analyst table that I have made. Do i need to combine an Insert or an update query with the select query to simultaneously put the values in the Analyst table. Please help me on how to go about with this.
This database is for a livestock show that I have been working on for quite some time now. This specific 'section' is for the Supreme Competitor award, in which points are given for the showman's placing in showmanship, ONE highest placing animal in market classes, ONE highest placing animal in breeding classes, and the showman's score on a quiz. I'm having a problem assigning 'points' for a single highest placing animal in market and breeding classes.
I would like to create a query/s that selects all animals shown by an exhibitor in a market class, then take the highest placing animal that the exhibitor had and award (update the livestock table-points field) points for a single animal. This is fine until one exhibitor has MORE than one animal that received 1st place. How do I go about telling the query to just pick one, lol... it doesn't matter which 1st place animal it selects to award the points... just as long as only ONE animal is updated and not all of the exhibitor's animals that received 1st... Then do this again to select one highest placing animal from the breeding classes.
Here's a little outline just for visual sake:
Market Classes
Name Tag No Class Placing <points field update> Sally Johnson 100 1 1st 10 Sally Johnson 101 2 1st Sally Johnson 102 3 1st Kim Smith 200 1 2nd 5 Kim Smith 201 4 2nd Kim Smith 202 5 3rd
See where Sally received 3 1st places, but only one of them is selected to be updated, and Kim received 2 2nd places (her highest placing) and only one is updated.
Thanks SO much in advance... this has really got me stumped.
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work: SELECT [tlkp.Language].[LangID], [tblRawData].[LangName], [tblApplication].[AppID] FROM (tlkpLanguage INNER JOIN tblRawData ON [tlkpLanguage].[LangName]=[tblRawData].[LangName]) INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE? I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication. I have tried everything and I keep getting syntax errors.
The coding below works fine. It presents a form with a list box of counties. Allows the user to select ALL, one or several counties and returns a query containing the clients from those counties.
The fields showing in the query are First, Last, Add1, FLAGToMap, City, Prov and Sector_Name.
I want to add in there a choice to select only the records that have are TRUE (-1) in the FLAGToMap field - just like the ALL button, this would be an ALL Selected Button let's say.
I would not know where to begin as I copied and adapted the coding below from a sample database and don't understand - at all - how the query is generated. The only coding in the form is the one below.
Private Sub cmdOpenQuery_Click() On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String
Can anyone help? I have a query that queries against 11 linked views against an SQL Server backend.
The query is running dog slow, so I want to convert it to a pass-through query so that the processing is done server side rather than Access having to drag thousands of records across the network, but don't know where to start.
The problem is that I need to convert the Access SQL to a version of SQL that SQL Server understands.
Is there a tool I can use that does this automatically (keeping my fingers crossed here)? If not then does anyone know where I can find out how to do this?
I'm sure this must be an easy one, I haven't used access for years and I've forgot how to do everything.
I'm designing a query based on a linked table which belongs to someone else. Unfortunately they appear to have stored a cost value (eg. 12030.30) as text.
I need to group the table records together and sum the cost value but I can't because its a text data type. Can anyone help me convert the data type within the query so that I can sum the costs, I've tried to build the expression using the cdbl() function but got stuck when it asked me for a parameter.....
Hi, I was wondering why the following code would give me an invalid SQL statement message:
Dim Rs As New ADODB.Recordset Rs.Open "Manzanero # 450", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
The error message is:
"Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update" I'm just trying to open up the table "Manzanero # 450" so that I might add to its contents. I have Microsoft Active X Data Objects 2.6 library included as well. I find it strange since this is basically a line for line copy of a sample I found in a MS Access book. Please help. Thanks! =)
I have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
My table has many records for each month. I am creating a report that will display only the records in one month of a year. I have been able to create a form that gives the user the choice of the Month and Year for which to create the report. The code I am using to create the combo box is:
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
What I need to do now is create the query for the report that displays all records for the chosen month and year. If I simply reference the combo box, all it shows is mmmm yyyy and the days are missing so the query doesn't work. What do I need to do to create the query so it displays all days within the month and date selected?
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
