Topics Search

Look Up Values From One Table and Automatically Insert Them in Another Table

Look Up Values From One Table and Automatically Insert Them in Another Table
Views: 1331
An append query automatically looks up fields that you select in one table and attaches them to another table within the database. Be careful though--once you make the attachment you cannot easily undo it.
Sponsored Links:

More topics

 

Insert Into versus Select Into

Preview
I was using "SELECT INTO" in access 2007 to insert all values from one table into another. This function deletes the table then reinserts everything. If I wanted to use INSERT INTOand insert all values from 1 row of one table into another how would I write this?
 

insert multiple rows in access

Preview
I created a table with field names "id", "name", "score". Now I want to insert values for this table. I don't know how to do insert multiple rows into this table but I can do single as below.

insert into table
values (1,'XYZ',55)
I tried the below but it doesn't work
insert into table
values (1,'XYZ',55),
values (2,'xaz',73)
 

insert multiple rows in access

Preview
I created a table with field names "id", "name", "score". Now I want to insert values for this table. I don't know how to do insert multiple rows into this table but I can do single as below.

insert into table
values (1,'XYZ',55)

I tried the below but it doesn't work
insert into table
values (1,'XYZ',55),
values (2,'xaz',73)
 

update vs. delete / insert

Preview
I have a table with only 1 record (and it will always have only 1 record.)
I'm looking for a method of updating the record's values by replacing them with the new values from a record in a second table (with many records). The record selected from the second table would be based on a option that the user selects on a form.

Currently, I have a delete and insert set of statements; however, a form which has this table as its record source is visible thru the process (of delete and insert) and I am able to get the form to requery to the new values (it seems to be getting lost during that perioid when the table has no record ?)

I had a thought that rather than delete / insert, I could use an update statement instead (?)
Q. Is there a way to use a wild card so that all of the fields in the record are upddated with values in the second table? (both tables have identical fields.)
Something like: UPDATE tbl1stTable SET * = * where <.&gt
 

Assign Values Automatically & Insert Records From Queries

Preview
I have queries to run records which do not match from 2 tables. These 2 tables are not related.

1. The records are considered closed orders so how do I get MS Access to update the status field in my Master Table as "Closed" automatically.

2. There are records that are new orders and I want to insert them into my Master Table for more monitoring. How do I insert these records that are ran from a query then?
 

Combine insert into syntax

Preview
I know how to insert values into a table and I know how to insert the results of a select statement into a table.
But is it possible to combine these two methode in MS Access?

Something like this?
INSERT INTO Group (groupID, memberID )
VALUES("1234",(SELECT memberID FROM members WHERE membergroup
 

Insert Multiple Records into a Table

Preview
Can I insert multiple records into a table using "INSERT INTO"., it seems it can insert only one record at one time.

INSERT INTO Table1 (F1, F2, F3, F4) VALUES ( 'CCP', 0.0115, 0.008202, 120)

Is there an alternate way to insert multiple records in MS Access Table using a query.
 

Edit table only via forms

Preview
I have this form and onClick of the button a VB code is executed. The code contains an insert query to insert values in a table. But the user can also add values directly by opening the table (I've hidden the table, but still, its easy to unhide it and add values).

Is there any way to edit table only via forms? Like, if the user tries to add values directly in the table, he should get an error message.something like "U can't add values directly! Enter via forms" or something like that
 

Insert Into statement

Preview
I am trying to get an INSERT INTO statement going to save some data from a form back to a table.

The help in Access itself shows the following format for the INSERT INTO when of the fields in a table are going to be saved.

You can omit the field list, but only if you supply all the values that record can contain.

INSERT INTO tblCustomers VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')

What i have put in as code is; INSERT INTO tblReviewers VALUES (Me.txtClaimRef, Me.txtDate, Me.cboReviewer, Me.cboHandler)

But whenever i put this in I get a "Compile Error: Expected: End of Statment" message box with the text "tblReviewers" highlighted. So far as i can see I have followed the format exactly and i'm not sure what i can do to get around this problem.
 

paste values from one table to another table in form

Preview
I need insert values from table tbl_parts (column 1,2,3.) to tbl_materials - in tbl_materials is primary key. the values will be inserted if form is on load 1 Table tbl_materials columns MainID, MatID, PN, Desc, Suplier, quantity 2 Table tbl_parts(lines are filled) MatID, PN, Desc, Suplier, quantity I have this code, but I dont know define primary key, and access say error Run-time 3075 DoCmd.RunSQL "INSERT INTO tbl_materials (MatID, PN) SELECT (MatID, PN) FROM tbl_parts" I need insert to field "quantity" for each record another values