matching and different records

Hi,

I have an access database with 2 tables with column 'loginid'.

How can I write queries to get that can give me matching, different (not having in each other) records of these 2 coumns.

regards,
Baber.
baberaminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plummetCommented:
To view the records that exist in both tables:

select a.*, b.*
from tablea a
inner join tableb b
on a.loginid = b.loginid

To view those in tablea but not in tableb:

select a.*, b.*
from tablea a
left join tableb b
on a.loginid = b.loginid
where b.loginid is null

To view those in tableb but not in tablea:

select a.*, b.*
from tablea a
right join tableb b
on a.loginid = b.loginid
where a.loginid is null

Of course you need to change the table names and choose which columns to view, but that is the principal.

Hope it helps!
0
peter57rCommented:
If you work in the query grid, add both tables and join them on the matching fields.
Then  right-click the join line and choose Join type.

You will get a clear explanation of the choices you have.
0
baberaminAuthor Commented:
Thanks.

ok now How can I do

Column1 Union Column2
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

baberaminAuthor Commented:
and they should be distinct also
0
plummetCommented:
Do you mean you want Column1 joined to Column2 to produce one field?
0
baberaminAuthor Commented:
yes
0
plummetCommented:
You can concatenate fields in Access using the & operator:

select column1 & column2 as NewColumn, column3, column4 ....

So using the previous example:

select a.column1 & b.column2 as NewColumn
from tablea a
inner join tableb b
on a.loginid = b.loginid

or if you need a space between two fields:

select a.column1 & ' ' & b.column2 as NewColumn
from tablea a
inner join tableb b
on a.loginid = b.loginid

I hope this helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baberaminAuthor Commented:
Hi ,

I mean Column1 Union Column2 not joining the text. I want a new column with all the records of col1 and col2 but without any duplication.

0
plummetCommented:
So Column1 is from TableA and Column2 from TableB?

0
peter57rCommented:
"a new column with all the records of col1 and col2 but without any duplication."

Then you will have to use a Group By query, grouping on the concatenated field.
But if there are duplicate keys how do you decide which values to use for the other fields (I assume there ARE other fields)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.