Union query (1 Viewer)

vellam

New member
Local time
Today, 17:48
Joined
Jul 8, 2021
Messages
2
Hi Everyone,

I have 2 tables in access database.

I wanted to create a full outer join so it will show all records from both tables and I created it.

Problem statement:
Workers table has 1 row for employee A and OT table has 2 records for employee A. So when i join them using my query, it gives 2 rows for Employee A.
I am fine with that also. When I use this query output data for my analysis, Salary Limit number shows double, because in query result it is coming twice.

Is there a way entire query will remain same but Salary Limit value will be considered only once in duplicate record cases.

I have attached my access database here. Kindly suggest some solution.

Thanks in advance.
 

Attachments

  • Database3.accdb
    916 KB · Views: 282

Ranman256

Well-known member
Local time
Today, 08:18
Joined
Apr 9, 2015
Messages
4,339
outer join cannot show all records in both tables.
you need 2 outer queries: 1 show all recs in tbl1, then 1 to show all in tbl2.
 

isladogs

MVP / VIP
Local time
Today, 12:18
Joined
Jan 14, 2017
Messages
18,186
A FULL outer join is possible using a union query. That's one of its main uses in Access.

@vellam
I had a quick look at your database earlier.
The main issue is that your tables aren't normalised. That's why you are getting more than one record in each table for the same employee.
You need to split the data for each table into two tables with a one to many relationship. Once you have done that, your issue will be easily solved.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Jan 23, 2006
Messages
15,364
Further to comments provided by others, you should avoid spaces in field and object names.
There are several articles and youtube videos on database normalization . Getting your tables an relationships identified is essential to design.

Lots of articles/tutorials in the Database Planning and Design link in my signature.
 
Last edited:

vellam

New member
Local time
Today, 17:48
Joined
Jul 8, 2021
Messages
2
A FULL outer join is possible using a union query. That's one of its main uses in Access.

@vellam
I had a quick look at your database earlier.
The main issue is that your tables aren't normalised. That's why you are getting more than one record in each table for the same employee.
You need to split the data for each table into two tables with a one to many relationship. Once you have done that, your issue will be easily solved.
Thanks for the suggestion "isloadogs".

I am new to Ms Access. Can you guide me how to that.

That would be helpful.
 

isladogs

MVP / VIP
Local time
Today, 12:18
Joined
Jan 14, 2017
Messages
18,186
Please check out the links suggested by @jdraw. The time spent studying about normalisation will be repaid many times over in times saved in the future
 

Users who are viewing this thread

Top Bottom