Select And Update Another Table.
Jun 27, 2005
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.
Thanks
Mo
View Replies
ADVERTISEMENT
Sep 23, 2014
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)?
View 1 Replies
View Related
Feb 27, 2006
I have 2 tables with the following fields.
OrderInfo
PO Number
Status
CustomerInfo
Purchase Order Number
Customer Name
Customer Phone
Order Status
This is what I am trying to do:
get the Status for a the PO Numbers from the first table and update the Order Status field for those same PO Numbers in the second table.
This is how I am doing it currently:
I created a select query and saved it as ExistingPO. This is what ExistingPO looks like.
SELECT OrderInfo.*
FROM CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.[Purchase Order Number] = OrderInfo.[PO Number];
and then I created another query which makes use of this first select query. The second query looks like this
UPDATE CustomerInfo INNER JOIN ExistingPO ON CustomerInfo.[Purchase Order Number] = ExistingPO.[PO Number] SET CustomerInfo.[Order Status] = ExistingPO.Status;
But I was wondering is there was a way to combine the first query and the second query into one query?
View 1 Replies
View Related
Nov 8, 2007
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
View 3 Replies
View Related
Feb 27, 2006
I have 2 tables with the following fields.
OrderInfo
PO Number
Status
CustomerInfo
Purchase Order Number
Customer Name
Customer Phone
Order Status
This is what I am trying to do:
get the Status for a the PO Numbers from the first table and update the Order Status field for those same PO Numbers in the second table.
This is how I am doing it currently:
I created a select query and saved it as ExistingPO. This is what ExistingPO looks like.
Code:SELECT OrderInfo.*FROM CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.[Purchase Order Number] = OrderInfo.[PO Number];
and then I created another query which makes use of this first select query. The second query looks like this
Code:UPDATE CustomerInfo INNER JOIN ExistingPO ON CustomerInfo.[Purchase Order Number] = ExistingPO.[PO Number] SET CustomerInfo.[Order Status] = ExistingPO.Status;
But I was wondering is there was a way to combine the first query and the second query into one query?
View 2 Replies
View Related
Jun 19, 2007
hello
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?
View 2 Replies
View Related
Jul 30, 2006
Hi,
I have Access 2002 on Windows XP.
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.
Thanks.
View 3 Replies
View Related
May 19, 2007
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.
View 1 Replies
View Related
Dec 28, 2007
Hello,
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.
Thanks,:mad:
View 1 Replies
View Related
Jan 29, 2008
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.
View 1 Replies
View Related
Nov 9, 2005
plz help me how to do it...
plz look at tables
i have "monthyear" & "partno/details and month" table
i cant open all part that have same month at table monthyear...
but i can open n view customer that bought same part at partno/details...
then i cant create form from table Monthyear..i want to create form as form "test"
plz help me...
am i need a coding?
can u show me the coding?
i'm new...plz..
ahh. cant post my DB....my db size to big...700kb after zip
cant i post screenshot? :confused:
View 1 Replies
View Related
Nov 22, 2006
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?
View 3 Replies
View Related
Jun 23, 2013
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
[code]....
View 6 Replies
View Related
Apr 4, 2013
I have a Sales Table with below fields, i might have not set it up in the best way possible.
Consumer, Consumer_ID, SaleDate,Prod_Sl#,Prod_Type,Sale Amount
1. I need to update the sales price for each item sold based on category of Product Type, as we are tracking the product with Serial Number.
For an instance if 2 items of Category1 with Prod_Sl# as Sl1 and Sl2 and
2 items of Category 2 with Prod_Sl# as Sl3 and Sl4 are sold.
I need to update the sales price amount for these.
2. I want to accomplish this using a query.
View 1 Replies
View Related
Nov 8, 2004
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! =)
G
View 2 Replies
View Related
Mar 13, 2013
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.
View 2 Replies
View Related
Mar 22, 2005
Hi
I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”
View 4 Replies
View Related
Jul 13, 2014
I have a basic database design, well I think so anyway. It only has two tables:
1. tblClientInfo
2. tblNotes
Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.
I then have two forms:
1. Claims Loss Form
2. tblNotes_DatasheetSub1
So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).
Example of a print out of what it is doing:
John Doe - 123 Easy St - Notes 1 (Most Recent Note)
John Doe - 123 Easy St - Notes 2 (Previous Note)
John Doe - 123 Easy St - Notes 3 (and so on...)
It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.
How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?
I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.
I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.
The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.
View 2 Replies
View Related
Dec 11, 2006
Hi Guys!
Need help in putting up SQL string.
I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.
What I want to do is to view customer information that does not have customer logins.
Is it possible to do in MS Access 2003?
Regards,
Aga
View 7 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Feb 12, 2014
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
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?
View 2 Replies
View Related
Sep 20, 2006
Hello,
I am trying to familiarise myself with MS Access and have the following problem:
Lets say I have a Table A with two columns as follows:
Name Total
Tom 1000
Mary 200
Brigid 150
Niamh 75
My question is how can I - using SQL only - select the nth, ie 3rd or 4th row?
I have spent some time on this and it irks me becasue it is probably very simple.
Any help or advice will be gratefully received.
Best Regards,
Tony
View 4 Replies
View Related
Oct 3, 2007
I have two tables [Reviews] and [Results].
When a Review has a Status = 'Not Accepted' then there should be one or more records in [Results] that indicate what the result of the review was.
I want to structure a query to perform a data integrity check to select any Review ID that satisfies the following criteria:
- The Review Status = 'Not Accepted'
- There are zero records in [Results] for that ReviewID
I can't figure out how to do it.
Here is an example of the tables where all record are OK...
[Reviews]
ID...Status
1....Accepted
2....Not Accepted
3....Accepted
4....Not Accepted
5....Not Accepted
6....Accepted
[Results]
ReviewID....ResultID
2................5
2................6
4................3
5................3
5................7
Here is how [Results] would look if there is a data integrity problem, where the results for Review 5 are missing....
[Results]
ReviewID....ResultID
2................5
2................6
4................3
So I want to structure a query that would return the following...
ReviewID
5
Because this is a Review with Status = 'Not Accepted' but there is no corresponding record(s) in [Results].
I guess in plain English it would be like this...
SELECT ReviewID
FROM [Reviews]
WHERE (ReviewID is in [Reviews]) AND (ReviewID is NOT in [Results])
Can this be done in a single SQL query?
View 10 Replies
View Related
Dec 5, 2005
I have a table which contains a nine digit number. How do I write a query to limit the restults to just the first two digits of that nine digit number?
Thanks
View 2 Replies
View Related
Aug 17, 2007
Query
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.
for eample if user choose varcurve1
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
if user choose varcurve2
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
is there a way to do that?
Thanks
View 1 Replies
View Related
Mar 17, 2005
Hi in the attached database there is a form called "with table select".
I want to be able to select any one of the three tables i have in the top combo box. When this is done the bottom 2 combo boxes will display the relevant data if needed (look at the other form "myform" to see the fields that are requseted).
Is this possible? :confused:
Thanks
View 1 Replies
View Related
