Unable to Find Solution on Query

[niri77]

I have unique situation not sure how to achieve this in Query.

For understanding I have attached mdb has following tables
'PR_RPT'
'SYS_RPT'
'TIME_RPT'
Final_PR_RPT

'PR_RPT' and 'TIME_RPT' has unique ID called as 'PTUNID', My Final goal is Final_PR_RPT.

I have created a Query1, which Look into 'PR_RPT' and 'TIME_RPT' and does a match on 'PTUNID' and populates all the 'SUM HR' Field Values from 'TIME RPT' table to matching 'PTUNID' in Column 'SUM HRS' similar to Final_PR_RPT,

In Addition to that what I am trying to do is

1- Look for PTUNID from PR_RPT to TIME_RPT if that doesnot match then
2- take Unmatching 'PTUNID', Look for which 'Project LEAD' owns the 'system ID' From 'SYS_RPT' Table then
3- Populate the unmatched 'PTUNID' 'SUM HRs' from 'TIME_RPT' Table against that 'Project LEAD' which Owns that unmatched system and has same ProjectID in PR_RPT in new column as Wrong SUM HR and WRONG SYS ID

Final_PR_RPT, shows how the result should stored.

I tried using IIF Statement but I believe I am not doing it right.


Thanks
Niri

 

[niri77]

tried using VBA but i donot know much in VBA so still struggling to find a way to do the above.

 

[niri77]

Not sure if I have nailed this down... but had to break this in pieces to achieve the final goal

Created a Query2 based on Not Equal Field of 'PTUNID' and Grouping the fields

Used Query 2 Values and IIF Statement to have the Values populated in Query1 and have similar set like Final_PR_RPT.

Note : Added one additional row of records in Time_RPT to do another questionable sanity check.

Not sure if I could have achieved this with single Query

 

[georgedwilkinson]

Can you give a little better understanding of what your problem/question is (be specific)? You seem to indicate it is solved in your last post but are still asking for help?

 

[georgedwilkinson]

Bump. Are you still there and need help?