Microsoft Access: Combine Tables (Like Fields)

It has been a while since I have worked with access and I ran into a slight problem. I can't seem to resolve it with a query. How do you combine two tables that contain the same information for two different departments?

Table 1

Name / Dept / Field 1 / Field 2

Table 2

Name / Dept / Field 1 / Field 2

insert into table2 as Select from table1

or somehing like that. all lines from table1 will be inserted on table2. is that?

If I need to find all that match field 2 from both tables, how can I do that? Both tables have the same exact fields I just need a method to combine the tables to search all for specific similarities.

hi


You want to Physically append the data in table2 to table1

Simply Copy the table2 and Paste it it will give you three options one of them would be append to excisting table .....it will append the content to the excisting table.

Or write in VBA code window in Access to do that ..

DoCmd.RunSQL "INSERT INTO TableTarget SELECT Table2.* FROM Table2;"

Then can easly search for recod...

You want to create a View /Query of sort to show records from two tables on the basis of a similar record

something like in VBA code window

DoCmd.RunSQL "Select dept, field1,field2,field3 from table 1,table2 where table1.field1=table2.field1 and field1<10;"

i don't have SQL server or Access to check it out..just did it on paper so you will have to check it out

--Good Luck--

No, I don't want to append the tables perm. I want to combine the tables in a query to search both tables for data and use it in my reports.

Do you have unique records for each record in both tables such as an SSN or something.

It's hard not knowing the purpose and field structure of the two tables to help.

Hi

You mean you have a different table for every Department..........The first rule of normalisation should have tried to stop you from doing that.... but oo well

And Access isen't making it easier either it wouldn't allow me to run Multiple queries at the same time.....

Now lets get this straight you have a different table for every department right.that means every table should only have one record........... about a particular department.

Or every table has multiple records including recurring ones....can't see how.......Any relation(Foreign key etc) between these tables? i don't think there can be .

I am still at loss why should have multiple tables of same fields in the same database....that's that's that's against the rules of normalisation:(......... don't you think you need to rethink the basic structure of the database(from what i am looking form here might be something else coz i can't make out without the actual structure of the database).


--Good Luck--

select * from table1
UNION
select * from table2

it will combine both in one VIRTUAL table (you can create a VIEW with thist)

if they have any kind of unique refrence in each row (like are all the names dif?) then create a new query - add in both tables and just drag then name of the field with the unique entry from table onto the corresponding field on the other table linking them

then right your query as normal - but it will limit it to only entries which appear as rows in both tables

is that what you mean??

v_Ln

From my SQL programming long ago I remember using joins (inner or outer). From what your describing you want an inner join. Check out:
http://www.google.com/search?hl=en&l...join+microsoft
or
even better:
Join Commands from Google

Hope that helps.

Cheers,
-D

<EDIT>
Did the google because I could not recall the syntax for a join in Access.
</EDIT>

Append Query!

Look for info on that! I'm sure it'll solve your problem!