Help in query

[Eric]

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

 

[pietlinden]

just looking at your data, you need to normalize in a serious way.
What's up with the Tech1, Tech2, Tech3 stuff? This isn't excel. Put
it in another table and join to the parent. No wonder you're having
problems with this. Same with the OtherAccount stuff. Time for a
tutorial on normalization. Then you can easily query with SQL. This
is going to be a nightmare, as you've seen.

 

[John Vinson]

Same in Tbl_ValidDispute i have to search all three otheraccounts.

Pietlinden is quite right. The reason you're having trouble is that
your table structure IS WRONG.

See:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

and

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Especially look at the "Database Design 101" links on Jeff's site.

Once you get your table structures correctly normalized you'll find
the queries are MUCH easier.

John W. Vinson[MVP]