Look for missing items across 2 tables

[shabar]

Hi All




Basically I have following 4 tables (including two lookup tables)


My intention is Basically first needs to find out what are the matching records
between ModTab and MedTab through link keys (ItemID & TrfCode through Lookup1) and then
missing OptCodes in MedTab which are in ModTab and viceversa.


Can I do this with one go

Key fields are the one with same name.


When linking MedTab & Lookup2 needs to use both StateCode and OptCode






ModTab
======
Component
Item ID






MedTab
======
TrfCode
OptCode
StateCode






Lookup1
=======
Item ID
TrfCode




Lookup2
========
Component
StateCode
OptCode




How should I do it






Appreciate your guidence




Cheers




Shabar

[June7]

Don't understand. How can you find 'missing OptCodes in MedTab which are in ModTab and viceversa' when OptCodes is not data in ModTab?


If you want to provide db for analysis, follow instructions at bottom of my post.

[shabar]

Hi June7

One more filter....

In case if I want to do the Lookup2.StateCode = MedTab.StateCode as follows how it is possible

If get Lookup2.StateCode value as "A1" needs to check "T1", "T2" values in MedTab.StateCode

But for other values needs to check one to one


Is there a SQL function in Access for this


Cheers

Shabar

Cheers

[June7]

Sorry, don't understand "value as "A1" needs to check "T1", "T2" values".

Maybe if I could see actual data would help.

[shabar]

Hi June7

Basically Lookup2.StateCode value has multiple values with value "A1"
When you join that field with MedTab.StateCode. If you get "A1" on Lookup2 table needs to check MedTab.StateCode values as "T1", "T2" not "A1"

For other values can check one to one like "T3" = "T3"


Hope this more clearer


Cheers

Shabar

[June7]

Does not help. What's wrong with A1? How does that translate to T1 and T2? Sorry, not making sense to me.

[shabar]

Does not help. What's wrong with A1? How does that translate to T1 and T2? Sorry, not making sense to me.

Hi June7

I came up with below query for my mentioned problem

SELECT *
FROM (SMPHQ_Active_Item INNER JOIN SMPHT_LKP_ProOpt ON SMPHQ_Active_Item.Comp = SMPHT_LKP_ProOpt.Comp) LEFT JOIN SMPHT_Med ON
(
((SMPHT_LKP_ProOpt.State = SMPHT_Med.StateCode) OR ((SMPHT_LKP_ProOpt.State = 'A1') AND ((SMPHT_Med.StateCode IN ('T1') AND SMPHT_Med.StateCode IN ('T2')) ) ) )
AND (SMPHT_LKP_ProOpt.OptCode = SMPHT_Med.OptCode)
);


But here problem is Even SMPHT_Med.StateCode has two records with T1, T2 when link thro SMPHT_LKP_ProOpt.State i.e. 'A1' it doesn't give those matching rows


I need to have un match row when When T1 or T2 were not there


Cheers

Shabar

[June7]

I've never seen queries using OR and AND operators in the join clause. I would have to work with data to see if I could figure this out. If you want to provide, follow instructions at bottom of my post.