I am having trouble with retrieving records on my database.
My layout was this..
UsersList Username Firstname LastName Role Records TheUser ApprovedBy DateApproved
The first text are the table names while the indented once are the field names.
I have a table named Records. TheUser and ApprovedBy was referring to the Username under the UsersList table.
Now my problem was, when I retrieve the records by aliases. I need to use the first name and last name of the users to generate report.
UsersList mzip Michael Zip Supervisor xtian Christian Holmes Trainee Records xtian mzip 1/22/2012 4:53:00 PM
If I will use this query, it will just show similar record.
SELECT (LastName + ', ' + FirstName) AS theTrainee, (LastName + ', ' + FirstName) AS PersonInCharge FROM UsersList INNER JOIN Records ON UsersList.Username = Records.theUser
theTrainee PersonInCharge Zip, Michael Zip, Michael
What I need is
theTrainee PersonInCharge Zip, Michael Holmes, Christian