Help writing statement

[hc98br]

I am farliy compitent with SQL (but by no means an expert), however this problem has got me stumped. Any help would be greatly appricated.

I have defined two table is Access:

	events					users
	----------				----------
	id						userId
	title					  logon
	description				firstName
	full					  lastName
	expires					userLevel
	createdOn
	createdBy
	lastModifiedOn
	lastModifiedBy

I have then set up two one-to-many relationships between createdBy & userId and between lastModifiedBy & userId. The purpose being to record who created the record and who last modified the record.

I now need to write an SQL statement that returns all the event details plus the names of the people who created and who modified the record.


Thanks in advance - any help will be greatly appriciated.


Ben.

 

[SQLBI]

Ben,

Give the following a try:

SELECT E.Id, E.Description, E.CreatedBy, (U.FirstName & " " & U.LastName) AS Name1, E.LastModifiedBy, (U.FirstName & " " & U.LastName) AS Name2
FROM tblEvents AS E, tblUsers AS U
WHERE E.CreatedBy = U.UserID
AND E.LastModifiedBy = U.UserID;

That should work ok and you can then play about with the detail of the resulting recordset until you're happy.


Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk