Refer to Current Record in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Refer to Current Record in SQL

Author

Topic

ahmadmahrous_1
Starting Member

4 Posts

Posted - 2012-07-16 : 08:52:18

Consider those two tables in Access 2010:

ActionReg
ActionID Taskt1 Actiont1
1 t1 t1a1
2 t2 t2a1
3 t3 t3a1
4 t1 t1a2
5 t1 t1a3
6 t2 t2a2

TaskReg
ActionID Taskt2 Actiont2
1 t1
2 t2
3 t3

I give the field Actiont2 the following properties (in Lookup tab in Design View):

Display Control = ListBox
Row Source Type = Table/Query

Now I want this query to retrive from Actiont1 into Actiont2 the records related only to the value of Task2 so the result will be:

TaskReg
ActionID Taskt2 Actiont2
1 t1 (t1a1,t1a2,t1a3)
2 t2 (t2a1,t2a2)
3 t3 (t3a1)

I used:

SELECT ActionReg.Action1
FROM ActionReg, TaskReg
WHERE [ActionReg]![Task1]=[TaskReg]![Task2];

But it retrives all records in Action1 if Task1 = any record in Task2.

ahmadmahrous_1
Starting Member

4 Posts

Posted - 2012-07-16 : 09:30:59

upppppppppppppppppppppppppppppp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 09:48:20

are you asking for Access query. then please post this in Access forum.
if it was t-sql, the solution would be like


SELECT tr.TaskID,tr.Taskt2,
STUFF((SELECT ',' + Actiont1 FROM ActionReg WHERE taskt1 = tr.Taskt2 ORDER BY ActionID FOR XML PATH('')),1,1,'')
FROM Taskreg tr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ahmadmahrous_1
Starting Member

4 Posts

Posted - 2012-07-16 : 12:58:24

quote:
Originally posted by visakh16

are you asking for Access query. then please post this in Access forum.
if it was t-sql, the solution would be like

SELECT tr.TaskID,tr.Taskt2,
STUFF((SELECT ',' + Actiont1 FROM ActionReg WHERE taskt1 = tr.Taskt2 ORDER BY ActionID FOR XML PATH('')),1,1,'')
FROM Taskreg tr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you and sorry for putting in wrong forum as I am new in the forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 22:06:14

ok..no problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources