Union query to combine datas in 2 tables.

[pedie]

Hi, I need help with procedure to create union query to combine datas in 2 tables.
I'm not doing any projects as such just learning it...
I have googled and found that union query can do it.
So please help.

Thanks

 

[boblarson]

Hi, I need help with procedure to create union query to combine datas in 2 tables.
I'm not doing any projects as such just learning it...
I have googled and found that union query can do it.
So please help.

Thanks

pedie:

Your information is a little sparse but essentially you can do this:

Select Field1NameHere, Field2NameHere, Field3NameHere, Field4NameHere
From Table1NameHere
Union
Select Field1NameHere, Field2NameHere, Field3NameHere, Field4NameHere
From Table2NameHere

if you want all records even if they are duplicated you would use UNION ALL

Select Field1NameHere, Field2NameHere, Field3NameHere, Field4NameHere
From Table1NameHere
UNION ALL
Select Field1NameHere, Field2NameHere, Field3NameHere, Field4NameHere
From Table2NameHere


And you can add information like if you wanted to know which table it came from:

Select Field1NameHere, Field2NameHere, Field3NameHere, Field4NameHere, "Table1" As MySource
From Table1NameHere
UNION
Select Field1NameHere, Field2NameHere, Field3NameHere, Field4NameHere, "Table2" As MySource
From Table2NameHere


So I hope that gets you started anyway.

 

[pedie]

Hi Bob, thank you very much..i think this should work...make sense..but i need to check it out...'m installing office10 at the moment..i try right after it finishes...

'll get back soon.

I think in a query we need to ; ' etc in the end of the query do i need to place this or just "," after field names like you have shown?

 

[Norie]

Pedie

You should put ; and the end of a query, but sometimes Access will let you away with not doing it.

It has nothing to do with fields, a list of fields is separated by a column not a ;.

Note if you do use ; for a UNION query it goes right at the end - not after each separate 'sub' query.

 

[boblarson]

Access doesn't require the semi-colon. It is really optional.

 

[pedie]

Thanks Norie for clarification...
I had problem installing office10, so i'm reinstalling 07...
So 'll be checking this out latter..

 

[pedie]

pedie:

Your information is a little sparse but essentially you can do this:

Bob, thanks alot...that worked great...
Thank you very very much!

 

[pedie]

Can I also please help me out with this...
I have a table with 2 fields...
Owner and 2nd_Owner

I have two access 07 files...
Can a quiry/code run from one db1 and add rec to table in db2

when the code runs i want db1, form1.textbox1 value to go to db2 table called "tb_ownership" field 2nd_Owner. and owner field be updated with environ ("Username")

Thanks again for valuable support..

 

[boblarson]

The easiest method is to link the table from the second database and then just run an append query in the first to update that linked table.

 

[pedie]

Access doesn't require the semi-colon. It is really optional.

The easiest method is to link the table from the second database and then just run an append query in the first to update that linked table.

Thanks again Bob, the thing is that the admin will not be able to access db1 from db2..only db1 can update db2 [because db2 is main database..]
So can a user click on something and then add this small data by unlocking the maindatabase and locking it back when the update is done?

Thanks again.