Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL question

Status
Not open for further replies.
encore0000

encore0000

MIS
Feb 29, 2004
75
US
I have a multisearch form performing a search on the tables based on what the users enters in the textboxes. The results are displayed in a texbox at the bottom of the page. tblTicketInfo is the main database. user_ID is a foreign key inside tblTicketInfo which has a lookup to user_ID inside the tblUserInfo which is a is the primary key in there.

tblUserInfo contains lastname first name and phone number. When I execute my multisearch, the search results display the user_ID as a number instead of the last name. I want to be able to dispaly the last name from the user_ID number in my search results. Please let me know if u have questions

here is the code:

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tblTicketInfo.ticket_ID, tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo"

strWhere = "WHERE"

strOrder = "ORDER BY tblTicketInfo.ticket_ID;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtMachine) Then '<--If the textbox txtMachine contains no data THEN do nothing
strWhere = strWhere & " (tblTicketInfo.Machine_Number) Like '*" & Me.txtMachine & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtUser) Then
strWhere = strWhere & " (tblTicketInfo.user_ID) Like '*" & Me.txtUser & "*' AND"
End If

If Not IsNull(Me.txtLocation) Then
strWhere = strWhere & " (tblTicketInfo.location_ID) Like '*" & Me.txtLocation & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (tblTicketInfo.ticketStatus_ID) Like '*" & Me.txtStatus & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox


DoCmd.OpenForm "frmAllTickets", , , "[ticket_ID] = " & Me.lstCustInfo, , acDialog 'open another form with ticket details

End Sub

 
Sort by date Sort by votes
Left Join To tbluserInfor and add the Lastname field to your query...

strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.LastName, " & _
"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " &_
"tblTicketInfo.ticketStatus_ID, blTicketInfo.location_ID " & _
"FROM tblTicketInfo Left Join tblUserInfo On (tblTicketInfo.User_id = tblUserInfo.User_id) "
 
Upvote 0 Downvote
I still see the colum user_ID in the search results. I want to do the same with location_ID. the table tblLocation has two field (location_ID and location_name). I want to only display location_name and not location_ID.
 
Upvote 0 Downvote
Should I just exclude user_ID from the Select statement and have condition as right now "FROM tblTicketInfo Left Join tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID)
 
Upvote 0 Downvote
Ok was able to remove the user_ID from the search results. Its working great but I have the same problem with ticketStatus_ID. I need to get ticketStatus from tblticketStatus. how would the join be done?
thanks


 
Upvote 0 Downvote
Thnaks alot, The technique worked but I have the same problem with ticketStatus_ID.

ticketStatus_IDD is a foreign key inside tblTicketInfo which has a lookup to ticketStatus_ID inside the tblTicketStatus which is a is the primary key in there.

tblTicketStatus contains ticketStatus and ticketSTatus_ID. When I execute my multisearch, the search results display the ticketStatus NOT ticketSTatus_ID. I want to be able to dispaly the ticketStatus from the ticketStatus_ID in my search results. Please let me know if u have questions

Here is the code i made changes as u said

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource

strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
"tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo RIGHT JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID)"

strWhere = "WHERE"

strOrder = "ORDER BY tblTicketInfo.ticket_ID;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtMachine) Then '<--If the textbox txtMachine contains no data THEN do nothing
strWhere = strWhere & " (tblTicketInfo.Machine_Number) Like '*" & Me.txtMachine & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtUser) Then
strWhere = strWhere & " (tblUserInfo.last_name) Like '*" & Me.txtUser & "*' AND"
'strWhere = strWhere & " (tblTicketInfo.user_ID) Like '*" & Me.txtUser & "*' AND"
End If

If Not IsNull(Me.txtLocation) Then
strWhere = strWhere & " (tblTicketInfo.location_ID) Like '*" & Me.txtLocation & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (tblTicketStatus.ticketStatus) Like '*" & Me.txtStatus & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub



Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox


DoCmd.OpenForm "frmAllTickets", , , "[ticket_ID] = " & Me.lstCustInfo, , acDialog

End Sub



 
Upvote 0 Downvote
Just add another Left Join to your from clause... The syntax is the same.
 
Upvote 0 Downvote
Would it be a right join or a left join??
I currently have a right join. so it would be

strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
"tblTicketStatus.ticketStatus, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo RIGHT JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID)LEFT JOIN tblTicketStatus On (tblTicketInfo.ticketStatus_ID = tblTicketStatus.ticketStatus_ID"


Thanks alot
 
Upvote 0 Downvote
The way you wrote your query, left join... Right and left do the same thing, it just matters which table you want everything from, the left (tblTicketInfo) or the right (tblUserInfo).

strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
"tblTicketStatus.ticketStatus, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo Left JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID) LEFT JOIN tblTicketStatus On (tblTicketInfo.ticketStatus_ID = tblTicketStatus.ticketStatus_ID"
 
Upvote 0 Downvote
My Search is returning nothing now.. It only returns when I use the following For some reason its not working..what could be the reasons

'strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
'"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
'"tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
'"FROM tblTicketInfo RIGHT JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID)
 
Upvote 0 Downvote
You must not have a field in tblticketinfo.user_id that matches tblUserInfo.user_id for anything that may be returned by the where statement you append later. USE LEFT JOIN.
 
Upvote 0 Downvote
ok used that i think there something wrong with this line..

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
 
Upvote 0 Downvote
Note the addition of the following lines in your procedure...

If strWhere = "WHERE" Then
Msgbox "You must enter at least one criteria value. Please try again."
exit sub
End if

You must enter a value OR not include a where clause. This forces the former. Otherwise, the criteria you are using just might not return values.

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource

strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
"tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo RIGHT JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID)"

strWhere = "WHERE"

strOrder = "ORDER BY tblTicketInfo.ticket_ID;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtMachine) Then '<--If the textbox txtMachine contains no data THEN do nothing
strWhere = strWhere & " (tblTicketInfo.Machine_Number) Like '*" & Me.txtMachine & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtUser) Then
strWhere = strWhere & " (tblUserInfo.last_name) Like '*" & Me.txtUser & "*' AND"
'strWhere = strWhere & " (tblTicketInfo.user_ID) Like '*" & Me.txtUser & "*' AND"
End If

If Not IsNull(Me.txtLocation) Then
strWhere = strWhere & " (tblTicketInfo.location_ID) Like '*" & Me.txtLocation & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (tblTicketStatus.ticketStatus) Like '*" & Me.txtStatus & "*' AND"
End If

If strWhere = "WHERE" Then
Msgbox "You must enter at least one criteria value. Please try again."
exit sub
End if

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
 
Upvote 0 Downvote
I am not getting any results in the Results box. However I do get the messagebox when nothing is entered. I can send you a screenshot of my Relationships. is there any way to send it to you. or i can email it to you also i u like...thanks alot for your help
Regards,
Salman

this is how my Procedure looks like

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource

'strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
'"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
'"tblTicketInfo.ticketStatus_ID, tblTicketInfo.location_ID " & _
'"FROM tblTicketInfo RIGHT JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID)"


strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
"tblTicketStatus.ticketStatus, tblTicketInfo.location_ID " & _
"FROM tblTicketInfo Left JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID) LEFT JOIN tblTicketStatus On (tblTicketInfo.ticketStatus_ID = tblTicketStatus.ticketStatus_ID"



'"FROM tblTicketInfo LEFT JOIN tblUserInfo ON (tblTicketInfo.user_ID = tblUserInfo.user_ID) LEFT JOIN tblTicketStatus ON (tblTicketInfo.ticketStatus_ID =tblTicketStatus.ticketStatus_ID"

'strSQL = "SELECT tblTicketInfo.ticket_ID, tblUserInfo.last_name, " & _
'"tblTicketInfo.Machine_Number, tblTicketInfo.user_ID, " & _
'"tblTicketStatus.ticketStatus, tblLocation.location_name " & _
'"FROM tblTicketInfo RIGHT JOIN tblUserInfo On (tblTicketInfo.user_ID = tblUserInfo.user_ID) RIGHT JOIN tblTicketStatus On (tblTicketInfo.ticketStatus_ID = tblTicketStatus.ticketStatus_ID)"

strWhere = "WHERE"

strOrder = "ORDER BY tblTicketInfo.ticket_ID;"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtMachine) Then '<--If the textbox txtMachine contains no data THEN do nothing
strWhere = strWhere & " (tblTicketInfo.Machine_Number) Like '*" & Me.txtMachine & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtUser) Then
strWhere = strWhere & " (tblUserInfo.last_name) Like '*" & Me.txtUser & "*' AND"
'strWhere = strWhere & " (tblTicketInfo.user_ID) Like '*" & Me.txtUser & "*' AND"
End If

If Not IsNull(Me.txtLocation) Then
'strWhere = strWhere & " (tblTicketInfo.location_ID) Like '*" & Me.txtLocation & "*' AND"
strWhere = strWhere & " (tblLocation.location_name) Like '*" & Me.txtLocation & "*' AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (tblTicketInfo.ticketStatus_ID) Like '*" & Me.txtStatus & "*' AND"
End If

If strWhere = "WHERE" Then
MsgBox "You must enter at least one criteria value. Please try again."
Exit Sub
End If


'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub



Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox


DoCmd.OpenForm "frmAllTickets", , , "[ticket_ID] = " & Me.lstCustInfo, , acDialog

End Sub
 
Upvote 0 Downvote
Here are the tables and the relationships.
Thanks again

tblTicketInfo
ticket_ID (PK) AutoNumber
ticket number
ticketStatus_ID (FK)
open date date open date
close date date close date
priority_ID (FK) Lookup lookups priority table
machine_number (FK) Lookup machine id from tbl (generic)
procedure text procedure followed
comments text comments
website text website reference if used
severity text severity of problem
user_ID (FK) Lookup user id tblUserInfo.user_id

tblTicketStatus
ticketStatus_ID (PK) AutoNumber
ticketStatus Text ticket status (open/close)

tblTicketPriority
priority number ticket priority (1-4)
priority_ID (PK) AutoNumber

tblUserInfo
user_ID (PK) text userid by first initial last name
first_name text users first name
last_name text users last name
email text email address
phone integer contact number
company text HSC,Noblestar,XO etc
location_ID (FK) location id

tblLocation
location_ID (PK) autonumber generic number
location_name text location number (Res,DC,NY)

tblMachineInfo
machine_number (PK) AutoNumber
workstation # (HSC121)
os OS (2000/XP/98)
serial_number text machine serial number
model_desc number model description


 
Upvote 0 Downvote
You set the rowsource but never requeried it, try adding the following line of code to your module.

Me.lstCustInfo.requery
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

combo
  • Locked
  • Question
SQL data types in CREATE/ALTER TABLE queries 1
Replies
2
Views
2K
combo
combo
Swi
  • Locked
  • Question
Jet Sql Statement - Select Distinct Multiple Columns 1
Replies
4
Views
1K
Swi
Swi
serino
  • Locked
  • Question
Help with SQL inner join statement 1
Replies
2
Views
560
serino
serino
Wolfdzn
  • Locked
  • Question
Join two seperate SQL into one
Replies
6
Views
742
dhookom
dhookom
teach314
  • Locked
  • Question
GROUP BY question 1
Replies
2
Views
288
teach314
teach314

Part and Inventory Search

Sponsor

Back
Top