Adding Info from one Table to Another Automatically

[pspring1]

I need to find a way to add information from one table to another, automatically how can I do this? Thanks!

 

[Golom]

The general solution is the INSERT statement as in

INSERT INTO tbl (fld1, fld2, ...)
SELECT f1, f2, f3, ... From tblSource Where ...

but more information is required for us to be more precise.

 

[pspring1]

My ultimate goal is to put all this data into a report the way I want to. SO I turned my different queries into make table queries and have been cutting and pasting them into one table which the report is based on. I need to do 2 things:
1- bring a bunch of rows from one table and have it placed at the bottom of another table.

2- bring unrelated data into the same table so i have a way to retrieve it int he report. i cannot just paste it like i am doing it for what id id above bc it is not part of those columns they are totals that will apearonce once at the bottom of the report.

 

[pspring1]

is it possible to pull single cell data from different areas and put it into one report? like one cell in a Cost column in table1 and one cell from purchases cell in table2?

 

[SQLSister]

Write an insert query with a join if you need information from two tables.

Insert into table1(field1, field2, field3)
Select table2.field4, table3.field4, table2.field6 from table2 inner join table3 on table2.idfield = table3.idfield{/code]
Now one must consider when doing this, how the join will work. If you have a one to one relationship between the tables your insert will contain one record for each id field (say the customer ID for instance). If there is a one to many relationship, then there will be muliple entries for each id field based on the number of times it appears inthe table with the many side of the relationship.  This is fine if it is what you want, but if it is not, you need to specify a way to determine which one record from the many table that you want. This can get somewhat complex.

Questions about posting. See faq183-874
Click here to learn [url=http://www.google.com/tsunami_relief.html]Ways to help with Tsunami Relief[/url]

 

[EuroRant]

Sorry to break in here, but I was wondering if this same solution would work for the question I posted yesterday:
(Can update/change be done "automatically"-thread700-992302)
"I am wondering if it is possible to update/change a field in the main Index table and for that same exact update/change to "automatically" take place in it's corresponding sub-table. If so - how is it done?"

When you suggested the code:

Insert into table1(field1, field2, field3)
Select table2.field4, table3.field4, table2.field6 from table2 inner join table3 on table2.idfield = table3.idfield{/code]
Where in the Query is this insertion placed?
Am I assuming that to be placed in the Criteria field of the Query Create mode?
Cheers.

 

[SQLSister]

This inserts into table1. You can only insert into one table at a time.

To perform the same thing with multiple tables is a design issue. First you would create a form in access that when the save button is pressed runs inserts into two differnt tables. If the tables are related on a particular key field, it may be necessary to do the first insert, reurn the new key field value (if it is an autonumber field) and then do the second insert. If the tables are related and share common fields that make up the primary key/foreign key relationships, you can set up referential integrity so that if the primary key changes, the foreign key will changes as well in the related table. Same if you delete a records, it will automatically delete related records.

Fot the situation you described in the other thread, the situation is more complicated. You would have to do all your data entry through forms, not allowing anyone to directly access the tables. The form would then have to perform two updates every time data was changed. In a database such as SQL Server, you could set up a trigger to perform this type of task, but triggers are not available in ACcess to my knowledge.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief