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,475
    Provided Answers: 11
    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 18:41.
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,475
    Provided Answers: 11
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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