Request for T-SQL query

Question

Hi ,

i am new to T-SQL.i want T-SQL query for getting output like below image.I am using MS-Access as Database.

Thank you.

		INPUT-1				INPUT-1				OUTPUT REQUIRED
		TABLE-1				TABLE-2				TABLE-3
	NAME	INCOME	DATE		EMP	SALARY	DATE			21-10-16	20-10-16	19-10-16
	JAMES	21	21-10-16		LEE	23	21-10-16		NAME
	DAVID	12	21-10-16		RAY	43	21-10-16		JAMES	21	12	121
	RAJ	23	21-10-16		NIKE	23	21-10-16		DAVID	12	13	21
	BILL	12	21-10-16		SONY	54	21-10-16		RAJ	23	14	212
	JAMES	12	20-10-16		LEE	23	20-10-16		BILL	12	12	12
	DAVID	13	20-10-16		RAY	54	20-10-16		LEE	23	23	12
	RAJ	14	20-10-16		NIKE	23	20-10-16		RAY	43	54	34
	BILL	12	20-10-16		SONY	22	20-10-16		NIKE	23	23	23
	JAMES	121	19-10-16		LEE	12	19-10-16		SONY	54	22	12
	DAVID	21	19-10-16		RAY	34	19-10-16
	RAJ	212	19-10-16		NIKE	23	19-10-16
	BILL	12	19-10-16		SONY	12	19-10-16

Answer 1

Hi GN_M,

This forum is discuss Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor.

Since your problem is related to query Access Database, I help you move this thread to Access forum to get a better help. Thanks for your understanding.

Best Regards,
Weiwei

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

You say "T-SQL", but you also say your database is Microsoft Access.  T-SQL is the SQL dialect that is native to MS SQL Server, not Access, so I want to make sure you really mean T-SQL, and not Access SQL?  Are you working within a Microsoft Access application, or are you working within SQL Server, with maybe an Access database as a linked server?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Here is a generic sql query that will run under most any RDBMS (sql server, Access, Oracle, ...) -- I noticed that Access sort of tweaked the date entries  -- supposed to be 10/21, 10/20,  10/19

SELECT DISTINCT Name, ordering, tblno
FROM (select * from   
(SELECT * 
FROM tbl1 union all select * from tbl2) as t1)  AS t2
order by tblno, ordering

Note:  in this sample I had to add 2 additional columns, Ordering, and TblNo.  Ordering is text, TblNo is Number.  Here is the raw data for these tables

ID	Name	Income	Datefld	Ordering	TblNo
1	james	21	10/16/2021	a	1
2	david	12	10/16/2021	b	1
3	raj	23	10/16/2021	c	1
4	bill	12	10/16/2021	d	1
5	james	12	10/16/2020	a	1
6	david	13	10/16/2020	b	1
7	raj	14	10/16/2020	c	1
8	bill	12	10/16/2020	d	1
9	james	121	10/16/2019	a	1
10	david	21	10/16/2019	b	1
11	raj	212	10/16/2019	c	1
12	bill	21	10/16/2019	d	1

ID	EMP	Salary	Datefld	Ordering	TblNo
1	lee	23	10/16/2021	a	2
2	ray	43	10/16/2021	b	2
3	nike	23	10/16/2021	c	2
4	sony	54	10/16/2021	d	2
5	lee	23	10/16/2020	a	2
6	ray	54	10/16/2020	b	2
7	nike	23	10/16/2020	c	2
8	sony	22	10/16/2020	d	2
9	lee	12	10/16/2019	a	2
10	ray	34	10/16/2019	b	2
11	nike	23	10/16/2019	c	2
12	sony	12	10/16/2019	d	2

and here is the output

Name	ordering	tblno
james		a		1
david		b		1
raj		c		1
bill		d		1
lee		a		2
ray		b		2
nike		c		2
sony		d		2

If you only want to see the Name column -- just remove the Odering and TblNo name is the (first - top most) select statement.  But you will need to keep the Order By Ordering, TblNo to maintain the format that you describe in your post.

---

Rich P

Answer 4

Hi GN_M,

First of all I assume that you are working in MS Access.

I try to create a two tables Sal1, Sal2 as you had mentioned above.

Sal1:

Sal2:

I can see that in both tables field names are different and you want to combine the records from two tables. so you need to use Union Here. then I find that you want to access salary based on date. for that you need to use where condition. we can write only one where condition in one query. so you need to execute separate query for getting result for a particular date.

Query1:

SELECT ename , income as '21-10-16' from sal1 where edate=#10-21-2016#;
union
SELECT emp , salary as '21-10-16' from sal2 where edate=#10-21-2016#;

Output:

Query2:

SELECT ename , income as '20-10-16' from sal1 where edate=#10-20-2016#;
union
SELECT emp , salary as '20-10-16' from sal2 where edate=#10-20-2016#;

Output:

Query3:

SELECT ename , income as '19-10-16' from sal1 where edate=#10-19-2016#;
union
SELECT emp , salary as '19-10-16' from sal2 where edate=#10-19-2016#;

Output:

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].