Multiple Query

[Eric]

I am trying to get the technician contractor(TECHCONT) of last field
technician (FS_TechID or LstVldTech). It could be in Tbl_ValidDispute
or Tbl_PPVResearch.

In table Tbl_PPVResearch first i have to match the first 5 digits of
Account number with Tech_ID.CORP and once it match i have to find last
field technician from all three
fields(FS_TechID1,FS_TechID2,FS_TechID3). First five digits of account
number in both tables are the CORP in table Tech_ID. The structure of
Tbl_PPVResearch is if FS_TechID3 is not empty it means that FS_TechID3
is a last field technician or if FS_TechID3 is empty then FS_TechID2 is
last field technician or if FS_TechID3 and FS_TechID2 is empty than
last field technician is FS_TechID1. Once i got the FS_TechID then
match it with Tech_ID.Tech and if match then get the technician
contractor from Tech_ID.TECHCONT

In another table Tbl_ValidDispute first five digits of all three
accounts are CORP. I have to match first the accounts with CORP and if
one is match then i have to match the LstVldTech with Tech_ID.Tech and
get the technician contractor (TECHCONT)
Thing which is in my mind is to write three separate queries first to
get the corp from two tables (Mid([AccountNum],2,4) AS Corp and then
write main query. Any help in creating query very appreciate.

Thanks in advance.

Tbl_PPVResearch
---------------
TicketNum Autonumber PK
AccountNum Type Text 00000-000000-00
FS_TechID1 Type Text 00000
FS_TechID2 Type Text 00000
FS_TechID3 Type Text 00000

Tbl_ValidDispute
-----------------
TicketNum
LstVldTech Type Text 00000
OtherAcct1 Type Text 00000-000000-00
OtherAcct2 Type Text 00000-000000-00
OtherAcct3 Type Text 00000-000000-00

Tech_ID
-------
ID PK
CORP Type Text 00000
Tech Type Text 00000
TECHCONT Type Text

[pietlinden]

Eric wrote:

I am trying to get the technician contractor(TECHCONT) of last field
technician (FS_TechID or LstVldTech). It could be in Tbl_ValidDispute
or Tbl_PPVResearch.

Tbl_PPVResearch
---------------
TicketNum Autonumber PK
AccountNum Type Text 00000-000000-00
FS_TechID1 Type Text 00000
FS_TechID2 Type Text 00000
FS_TechID3 Type Text 00000

Tbl_ValidDispute
-----------------
TicketNum
LstVldTech Type Text 00000
OtherAcct1 Type Text 00000-000000-00
OtherAcct2 Type Text 00000-000000-00
OtherAcct3 Type Text 00000-000000-00

Tech_ID
-------
ID PK
CORP Type Text 00000
Tech Type Text 00000
TECHCONT Type Text

Getting the first/last TechID is going to be a nightmare if you don't
NORMALIZE. there's a reason for all those papers the Codd wrote. The
OterAcct(1,2,3) and FS_TechID(1,2,3) are key indicators that you need
to normalize in a bad way. If I were you'd, I'd stop for a while and
read up on normalization theory, because in this case, it really
matters. I'd start with Hernandez's Databases for Mere Mortals.

Why? Try querying this kind of structure. Hope you like UNION
queries. LOTS of them. Because that's the only way you can get this
kind of structure to be even close to functional. And even then it's
going to be a pain.

Glad it's your DB and not mine.

[Eric]

Database is old which is running in my company and it contains more
than 100,000 records. I need the report and i dont know do i use the
query or do i write a VB method to fetch the record.

When user enter the CORP and Tech, it goes to Tbl_PPVResearch and match
it with first 4 digit of AccountNum and Tech with FS_TechID1,
FS_TechID2, FS_TechID3. I need the last technician from all three
FS_TechID. If it match it fetch the record.
Then it goes to Tbl_ValidDispute search the CORP in all three
OtherAcct(OtherAcct1,OtherAcct2,OtherAcct3) if it match then it search
the Tech in LstVldTech once both match it fetch the record.

Thanks,
pi********@hotmail.com wrote:

Eric wrote:

I am trying to get the technician contractor(TECHCONT) of last field
technician (FS_TechID or LstVldTech). It could be in Tbl_ValidDispute
or Tbl_PPVResearch.

Tbl_PPVResearch
---------------
TicketNum Autonumber PK
AccountNum Type Text 00000-000000-00
FS_TechID1 Type Text 00000
FS_TechID2 Type Text 00000
FS_TechID3 Type Text 00000

Tbl_ValidDispute
-----------------
TicketNum
LstVldTech Type Text 00000
OtherAcct1 Type Text 00000-000000-00
OtherAcct2 Type Text 00000-000000-00
OtherAcct3 Type Text 00000-000000-00

Tech_ID
-------
ID PK
CORP Type Text 00000
Tech Type Text 00000
TECHCONT Type Text

Getting the first/last TechID is going to be a nightmare if you don't
NORMALIZE. there's a reason for all those papers the Codd wrote. The
OterAcct(1,2,3) and FS_TechID(1,2,3) are key indicators that you need
to normalize in a bad way. If I were you'd, I'd stop for a while and
read up on normalization theory, because in this case, it really
matters. I'd start with Hernandez's Databases for Mere Mortals.

Why? Try querying this kind of structure. Hope you like UNION
queries. LOTS of them. Because that's the only way you can get this
kind of structure to be even close to functional. And even then it's
going to be a pain.

Glad it's your DB and not mine.

[pietlinden]

Use a query. The reason I said to normalize this is that querying a
non-normalized database is a total PITA. You get the OR problems like
the ones you posted. And forget summarizing data. You _can_ use union
queries to get normalized views of the data, but union queries are not
updateable.

i would still create queries to sort of normalize the data, and then
base my final queries on the "normalized" views. This design is a
disaster.

[pietlinden]

Eric wrote:

Database is old which is running in my company and it contains more
than 100,000 records. I need the report and i dont know do i use the
query or do i write a VB method to fetch the record.

If you don't know how to write queries, you're in pretty deep trouble.
I would go out and get a good book on Access if I were you. John
Viescas's books are good, Roger Jennings is good. For Dummies books
are usually worthless.

I don't mean to be ugly at all, but making a badly denormalized
database functional is not that easy. If you absolutely have to leave
the database structure as is, I would union the repeating junk together
into a virtual table and use those to query.

There are just so many normalization problems with this database it's
not even funny.

Basic union query...

SELECT tbl_PPVResearch.AccountNum, FS_TechID1 As FS_TechID
FROM tbl_PPVResearch
WHERE tbl_PPVResearch.FS_TechID1 IS NOT NULL
UNION ALL
SELECT tbl_PPVResearch.AccountNum, FS_TechID2 As FS_TechID
FROM tbl_PPVResearch
WHERE tbl_PPVResearch.FS_TechID1 IS NOT NULL
SELECT tbl_PPVResearch.AccountNum, FS_TechID3 As FS_TechID
FROM tbl_PPVResearch
WHERE tbl_PPVResearch.FS_TechID1 IS NOT NULL;

Do that with each table that has fieldName(1,2,3...)
Then you should be able to get reasonable queries out of this thing.

Once you've done that for all the screwy tables, base your queries on
these union queries. As long as you don't try to run updates and
deletes through these queries, you can get the information you need out
of it, I think.

God be with ya. You may need it.