-
August 16th, 2004, 04:36 PM
#1
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
-
August 16th, 2004, 06:35 PM
#2
insert into table2 as Select from table1
or somehing like that. all lines from table1 will be inserted on table2. is that?
Meu sítio
FORMAT C: Yes ...Yes??? ...Nooooo!!! ^C ^C ^C ^C ^C
If I die before I sleep, I pray the Lord my soul to encrypt. If I die before I wake, I pray the Lord my soul to brake.
-
August 16th, 2004, 07:37 PM
#3
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.
-
August 16th, 2004, 08:03 PM
#4
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--
-
August 16th, 2004, 08:05 PM
#5
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.
-
August 16th, 2004, 08:24 PM
#6
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.
Don\'t SYN us.... We\'ll SYN you.....
\"A nation that draws too broad a difference between its scholars and its warriors will have its thinking done by cowards, and its fighting done by fools.\" - Thucydides
-
August 16th, 2004, 08:39 PM
#7
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--
-
August 16th, 2004, 09:41 PM
#8
select * from table1
UNION
select * from table2
it will combine both in one VIRTUAL table (you can create a VIEW with thist)
Meu sítio
FORMAT C: Yes ...Yes??? ...Nooooo!!! ^C ^C ^C ^C ^C
If I die before I sleep, I pray the Lord my soul to encrypt. If I die before I wake, I pray the Lord my soul to brake.
-
August 17th, 2004, 12:01 AM
#9
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
-
August 17th, 2004, 07:38 PM
#10
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>
If you spend more on coffee than on IT security, you will be hacked. What\'s more, you deserve to be hacked.
-- former White House cybersecurity adviser Richard Clarke
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|