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.
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.
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)?
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
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.
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
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs JobID (J000001, J000002, etc) Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc Consultant: (Joe, Terry etc) ObjectID: (RoberI, SmithJ etc) Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001" as [JobNo] FROM J000001 UNION SELECT ObjectID, Consultant, Status, "J000002" as [JobNo] FROM J000002 UNION SELECT ObjectID, Consultant, Status, "J000003" as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual FROM (
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper. Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual FROM [
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable Now when you want to execute the query you get an errormessage. Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?
I have a crosstab query built on a select query.It works perfect. However, when I try to use the "Between [BegDate] And [EndDate]" it tells me access engine can not recognize it.If I use this in the actual select query it works fine. But when I try to use the crosstab query it gives me the above error.
Also, I'm using two undbound textboxes on a form for [BegDate] and [EndDate]. I have the syntax correct in my select query and it works fine. But it won't work with the crosstab query.
