Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Query Help

    Hi: Can any one please help me in creating query.I input from user the account number, Technician ID and then search the match in table tech_id with tech_id.CORP and tech_id.TECH and print the tech_id.TECHCONT. In table tech_id the CORP is equals to the first five digit of account number in both tables. I have to search the account number in both tables (Tbl_ValidDispute,tbl_PPVResearch). Tbl_ValidDispute and Tbl_PPVResearch they both are connected with TicketNum.In tbl_PPVResearch there are three fields (FS_TechID1, FS_TechID2, FS_TechID3). If FS_TechID3 is not empty means its a lastvalidtechnician or if FS_TechID2 is not empty and FS_TechID3 is empty it means FS_TechID2 is a lastavlidtechnician otherwise FS_TechID1 is a last valid technician.

    Same in Tbl_ValidDispute i have to search all three otheraccounts. First if otherAcct3 is not empty and match with tech_id.CORP also match the LstVldTech with tech_id.TECH and if they both match then print the TECHCONT. If otherAcct3 is empty but otherAcct2 is not empty then match with tech_id.CORP also match LstVldTech with tech_id.TECH if both match then print the tech_id.TECHCONT. if otherAcct2 and otherAcct3 is empty then match with otherAcct1 and print the TECHCONT.


    Thanks,

    ------------------------------------------------------

    tbl_PPVResearch
    ---------------
    TicketNumAccountNum FS_TechID1 FS_TechID2 FS_TechID3
    328 07836-033752-01 309
    329 07882-022113-12 786
    330 07837-026980-07 773
    334 07837-337410-03 590
    335 07876-537867-04 222
    336 07868-580112-06 643
    348 07876-579223-02 228
    349 07875-192714-06
    351 07870-451873-05
    352 07840-940441-02 101 20
    353 07818-242399-05 71
    356 07844-251648-08 85 415 415
    362 07862-215662-01 304 70
    366 07840-356692-02 119
    377 07836-421118-01 522
    392 07869-874266-02 316
    395 07872-119872-03 419 669 538
    397 07844-071040-06 471
    400 07804-006655-05 369
    402 07876-523152-01 CUST PICKUP
    409 07836-596979-01 713 424 185
    423 07870-739378-03 373
    427 07883-596396-05 855 214
    431 07836-547313-02 557

    Tbl_ValidDispute
    ----------------
    TicketNum OtherAcct1 OtherAcct2 OtherAcct3 LstVldTech
    478 07837-237258-02 07837-335799-01 07237-404300-01 251
    647 07837-443814-01 07837-421471-05
    743 07837-22220-02
    761 07837-5690-01 07837-474611-02
    751 07837-531065
    1004 07837 242858-02
    1450 07836-640954-02 678
    1495 07837-024196-04 261


    tech_id
    -------
    CORP TECH TECHCONT
    07883 214 Fuentes
    07836 185 Cruz
    07837 261 Long
    07837 251 Bing
    07801 415 Messina
    07801 416 Malgliolo
    07801 417 Malgliolo

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Think you need look in the Left right and mid string and the instr() and the Split() command also the replace command

    you can write Your own Functions to

    first I would Right a Function To breack the the Code so that they are the same

    Code:
    mark 1
    Function Get_it(ThisValue, Iwant)
    Dim TempValue() As String
    TempValue = Split(ThisValue, "-", -1, vbTextCompare)
    Get_it = TempValue(Iwant)
    End Function
    use like

    aa = Get_it("07836-033752-01", 1)

    aa = "033752"

    so in a Query

    aa:Get_it([FS_TechID1],1)
    Last edited by myle; 09-03-06 at 19:41.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Code:
    mark 2
    Function Get_it(ThisValue, Iwant,splitit)
    Dim TempValue() As String
    TempValue = Split(ThisValue, splitit, -1, vbTextCompare)
    Get_it = TempValue(Iwant)
    End Function
    use like

    aa = Get_it("07836-033752-01", 1, "-")

    aa = "033752"

    so in a Query

    aa:Get_it([FS_TechID1],1,"-")

    or

    aa:Get_it([FS_TechID1],1," ") <= A space
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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