Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006

    Unanswered: Needs Help in query


    Can any one please help me in this criteria "(((tech_id.CORP)=Val(Left([LastValidTech].[Account3],5))))". Rest of the reocrds in LastvalidTech table which are not match i want to remove all the join with tech_id table and display those records too. Why i want to remove the join from tech_id because corp has four digit value and if put a criteria not equals
    to corp then it shows 100s of records from tech_id which dont match with LastvalidTech.Account3. Records which match the criteria first it shows then remove all the links with tech_id and shows the remaining account and all of the other table information. Is it possible that i do like that or may be other way.

    SELECT LastValidTech.TicketNum, LastValidTech.Account1, tbl_Events.txt, tbl_Events.PPVVOD_Amt, LastValidTech.Account2, LastValidTech.Account3, LastValidTech.Account4, LastValidTech.LstVldTech, tech_id.TECHCONT, tbl_PPVResearch.RequestDate, tech_id.CORP
    FROM tech_id INNER JOIN (tbl_Events INNER JOIN ((LastValidTech INNER JOIN tbl_PPVResearch ON LastValidTech.TicketNum =
    tbl_PPVResearch.TicketNum) INNER JOIN tbl_ValidDisputes ON tbl_PPVResearch.TicketNum = tbl_ValidDisputes.TicketNum) ON
    tbl_Events.TicketNum = tbl_PPVResearch.TicketNum) ON tech_id.TECH = LastValidTech.LstVldTech WHERE (((tech_id.CORP)=Val(Left([LastValidTech].[Account3],5))));


  2. #2
    Join Date
    Dec 2004
    Madison, WI
    If I'm reading your problem right, you could have an "intermediate" query which formats the tech_id.CORP field the way you want it to and then in your next query link to that field. Might not be the best solution but it may work. You could also possibly do a make-table query and then link to that "temp" table. This will cost you a little bit slower result return. I'm not sure what values you're dealing with and if an instr or like statement might also work in your query.
    Last edited by pkstormy; 09-15-06 at 09:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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