Select Distinct Statement

[brittaink]

Anyone,
I am using a select distinct statement in my SQL. Please see underneath:

SELECT tblCircuits.id, tblCircuits.TN AS [Number], tblListDatoEquipmentType.Type AS [Asset Type], tblListLineType.LineType AS [Line Type], tblListDatoMake.Make AS [Make And Model], tblListSites.site AS Site, tblListBldgs.bldg AS Building, tblCircuits.RoomNumber AS Room, tblUseageHistory.id
FROM tblUseageHistory RIGHT JOIN (((((tblCircuits LEFT JOIN tblListDatoEquipmentType ON tblCircuits.AssetTypeOnEnd = tblListDatoEquipmentType.id) LEFT JOIN tblListLineType ON tblCircuits.[_LineType] = tblListLineType.id) LEFT JOIN tblListSites ON tblCircuits.[_Site] = tblListSites.id) LEFT JOIN tblListBldgs ON tblCircuits.[_Bldg] = tblListBldgs.id) LEFT JOIN tblListDatoMake ON tblCircuits.[_MakeandModel] = tblListDatoMake.id) ON tblUseageHistory.[_HardwareId] = tblCircuits.id
GROUP BY tblCircuits.id, tblCircuits.TN, tblListDatoEquipmentType.Type, tblListLineType.LineType, tblListDatoMake.Make, tblListSites.site, tblListBldgs.bldg, tblCircuits.RoomNumber, tblUseageHistory.id
HAVING (((tblUseageHistory.id) In (SELECT DISTINCT Last(tblUseageHistory.id) AS LastOfid FROM tblUseageHistory GROUP BY tblUseageHistory.[_HardwareId] ORDER BY Last(tblUseageHistory.id)ASC; ) Or (tblUseageHistory.id) Is Null))
ORDER BY tblCircuits.TN;


Basically there are x amount of mobiles. These mobiles are signed out regurlaly by different members of staff. I have a table of mobiles (tblcircuits) a table of the useage history (tbluseagehistory) and a table of users (tblusers). I am trying to acheive a list box to show the mobile number and the last person to sign it out. I have achieved this by doing the statement as shown. But it only shows the userid.

I was wondering how I can get the query to get the userid out of the select distinct and then show this as the name from tbl user instead of just the id (autonumber) number.

Any help would be appreciated.

Thank You

Keith

 

[twoplustwo]

Is this is a query or a recordsource for the listbox?

It sounds like the latter, in which case looking at the column count might help you - change the first column to 0, which will hide the ID and set the column you want to see to 4cm or sth.

Let us know how you get on.

 

[brittaink]

I dont think that you understand me MY BAD

Is this is a query or a recordsource for the listbox?

It sounds like the latter, in which case looking at the column count might help you - change the first column to 0, which will hide the ID and set the column you want to see to 4cm or sth.

Let us know how you get on.

The first column in the recordsource for this list box is indeed a id column which I have hidden but it is the id column of the select distinct query that I would like to convert into the value obtained from tblUsers

 

[twoplustwo]

Could you poast an example db for us?

 

[georgedwilkinson]

And you have the "Column Widths" property set to what?

Also check the "Column Count" property.