Need help

[rkisor]

Hi Experts,

I have four tables

Table1
Date	LOB	Emp Id	Location	Tickets Count
10/21/2015	LOB1	1111	CITYname1	1
10/21/2015	LOB1	2222	CITYname2	2
Table2
Date	LOB	Emp Id	Location	Tickets Count
10/21/2015	LOB1	333	CITYname1	1
10/21/2015	LOB1	444	CITYname2	2
Table3
Date	LOB	Emp Id	Location	Tickets Count
10/21/2015	LOB1	555	CITYname1	1
10/21/2015	LOB1	666	CITYname2	2
Table4
Date	LOB	Emp Id	Location	Tickets Count
10/21/2015	LOB1	777	CITYname1	1
10/21/2015	LOB1	888	CITYname2	2

I need to link all four tables into one to get the total tickets for each LOB. I have shown the output below

OUTPUT
Date	Emp Id	Location	LOB1_Tickets Count	LOB2_Tickets Count	LOB3_Tickets Count	LOB4_Tickets Count
10/21/2015	777	CITYname1	1	1	1	1
10/21/2015	888	CITYname2	2	2	2	2

Please help

[alansidman]

Your output example makes no sense given the data shown in your four tables. You are not showing any data for LOB2, LOB3 or LOB4, yet they appear in your result. Please clarify. Additionally, you have duplicated fields in each of the 4 tables. This is contrary to the proper design of RDBMS. Suggest you look at this link and redesign your DB to conform with the rules of normalization


http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[NTC]

fundamentally you put all the tables into the design view of the query; and then join on the common field. however as pointed out - there are some discrepancies in your post. I am going to guess that because you only display 1 employee column, 1 date column & 1 location column that these are common to all tables.

you can join on them - but - if there is no record for a date/employee/location in any one table then the record of the other tables don't display. So I think you need to create a master table that guarantees date, employee, location with a left join to the other tables (arrow point join line)....