Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Multi-option Multi-Inner Join Query (2003)

    I have a table that holds all the techs we assign tickets to. I have a second table that holds the tickets. Each ticket can be assign a Tech and re-assigned a second Tech and some cases a third tech.

    I am trying to Query tickets with a Status = Assigned and the current assigned tech. Below I listed the different SQL statements pulled from three separate queries that pull the current assigned tech.

    I either need a way to put the three queries into one query (which I can't remember how to do.) or I need a way to make one query that will pull the same information. I tried several approaches but seem to get hung up because of the INNER JOIN between the tblTech and tblTickets.
    ----------------------------------
    FROM tblTechs INNER JOIN tblTickets ON tblTechs.LoginName = tblTickets.Tech1Assigned
    WHERE (((tblTechs.Status)="Assigned")) OR (((tblTickets.Tech2Assigned) Is Null) AND ((tblTickets.Tech3Assigned) Is Null));

    Query results show Assigned tickets with only the 1st Tech assigned
    ----------------------------------
    FROM tblTechs INNER JOIN tblTickets ON tblTechs.LoginName = tblTickets.Tech2Assigned
    WHERE (((tblTechs.Status)="Assigned")) OR (((tblTickets.Tech3Assigned) Is Null)

    Query results show Assigned tickets with a 2nd Tech assigned
    ---------------------------------
    FROM tblTechs INNER JOIN tblTickets ON tblTechs.LoginName = tblTickets.Tech3Assigned
    WHERE (((tblTechs.Status)="Assigned")) OR (((tblTickets.Tech3Assigned) Is Not Null)

    Query results show Assigned tickets with a 3rd Tech assigned
    --------------------------------

    My goal is to make a query that pulls ALL Assigned Tickets and the current assigned Tech. Any help would be greatly appreciated. Thanks everyone for your assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    You could create a union query. You have to do this in SQL view. It would look like this:

    SELECT * FROM qryFirst
    UNION
    SELECT * FROM qrySecond
    UNION
    SELECT * FROM qryThird

    where qryFirst etc. are the names of the three queries you have.

    If I were to design such a database, I would use a different setup: I'd remove the Assigned field from tblTechs and the Tech1Assigned etc. fields from tblTickets.
    Instead, I'd create an intermediate table tblAssignments with three fields:

    - LoginName (Text, links to LoginName in tblTechs), or even better, a numeric ID field for the tech.
    - An ID field for the ticket (links to the field that uniquely identifies the ticket).
    - CurrentlyAssigned (Yes/No) indicating whether the LoginName is currently assigned to the ticket.

    Each assignment would be a separate record in this table. So if ticket 37 has been assigned to tech A and then reassigned to tech B, there'd be 2 records, with CurrentlyAssigned = Yes for tech B.

    The query would simply select records for which CurrentlyAssigned equals Yes.

  3. #3
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    Hans, thanks for your help.
    I do see your point on both methods, but decided to use the Union Query code, and it works wonderfully.

    To continue on, I am using the Union Query in a form. This portion ties into my other post about using the login names in a combo box. I would like to have the form filter the results from the union query using the CurrentUser() as the filter. I wasn't able to find a place to do this and was wondering if I would have to use a parameter query to perform this.

    Thanks for your help, you have a gift for explaining and understanding.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    You can create a new query based on the union query, and specify CurrentUser() as criteria in the appropriate column. Use the new query as record source for the form.

  5. #5
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    Thank you for the help, I wasn't sure if there was another method. It worked and I appreciate your help.

  6. #6
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    I created the union query just like you said and it works perfectly. However when one of my users opens the query and there are no records that match the user, they get a blank page. I have a form that opens based on the query with the criteria that the Assigned Tech = currentuser()

    If there are no records I would like to display a message that says "No Tickets Assigned" is there a way to do this?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    You could test whether there are any records before opening the form, e.g.

    Private Sub cmdOpenOtherForm_Click()
    If DCount("*", "NameOfQuery") = 0 Then
    MsgBox "No Tickets Assigned", vbInformation
    Else
    DoCmd.OpenForm ...
    End If
    End Sub

    Substitute the appropriate names.

  8. #8
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Multi-option Multi-Inner Join Query (2003)

    Thanks Hans, that work perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •