Multiple data lookups in views 1

[bombplayer]

Is there a way to complete multiple lookups in a view? For instance, a data source references other table and gives the referenced table id.

AuditTrail.TableName
AuditTrail.TableID

Tablename1.ID

Tablename2.ID

 

[tlbroadbent]

I'm unclear about your question. I'm not sure what you want to achieve. It would be helpful if you posted sample data from the tables and the expected output of the view. I did make some assumptions and came up with the following query.

SELECT
AuditTrail.TableName,
AuditTrail.TableID,
Case TableName
When 'Tbl1' Then tbl1.ID
Else tbl2.ID
End AS TblID,
Case TableName
When 'Tbl1' Then tbl1.Col1
Else tbl2.Col1
End AS DataCol1,
Case TableName
When 'Tbl1' Then tbl1.Col2
Else tbl2.Col2
End AS DataCol2
FROM AuditTrail
LEFT JOIN Tbl1
ON AuditTrail.TableID = Tbl1.ID
LEFT JOIN Tbl2
ON AuditTrail.TableID = Tbl2.ID Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[bombplayer]

Sorry for the lack of information. The table format is similar to this:

Audittrail.Tablename, audittrail.TableID, Audittrail.OldValue, audittrail.Newvalue

Where tablename references another table and tableid references the table index.

I am going to try to use your example and see if I can manipulate it to work for me

Thanks for your help