Finding Missing Text v2

[Lythandra]

Hi there,

What I am trying to do is find missing "Cost Codes" for specific Jobs and
Phases.

My table(s) are:

Table 1: (tbl Project ID) Contains the Project ID (Job ID) and related info

Table 2: (tbl Phase ID) Contains the Phase ID and related info (also has a
field for Project ID, can have multiple Phases per Project ID thus many
entries)

Table 3: (tbl Cost Codes) Contains the Cost Codes (also has fields for
Project ID and Phase ID), each Cost Code will be 2 distinct records in this
table per Project/Phase. One entry for 3V-000 and another for 3V-100. This is
the table where the Cost Codes are missing from.

I have a query that is as follows:

SELECT MASTER_JCM_JOB_3V.Status, MASTER_JCM_PHASE_3V.Job,
MASTER_JCM_PHASE_3V.Phase, MASTER_JCM_COST_CODE_3V.Cost_Code
FROM MASTER_JCM_JOB_3V INNER JOIN (MASTER_JCM_PHASE_3V LEFT JOIN
MASTER_JCM_COST_CODE_3V ON (MASTER_JCM_PHASE_3V.Phase =
MASTER_JCM_COST_CODE_3V.Phase) AND (MASTER_JCM_PHASE_3V.Job =
MASTER_JCM_COST_CODE_3V.Job)) ON MASTER_JCM_JOB_3V.Job =
MASTER_JCM_PHASE_3V.Job
WHERE (((MASTER_JCM_JOB_3V.Status)<>"Closed") AND
((MASTER_JCM_PHASE_3V.Phase) Not Like "*.*") AND
((MASTER_JCM_COST_CODE_3V.Cost_Code) Not In (Select [tbl Lost Cost Codes
Check].[Cost Code] From [tbl Lost Cost Codes Check])));

What I am doing here is selecting currect jobs and then filtering out the
junk phases and then I was trying to use a subquery as criteria to give me my
missing Cost Codes.

It does Work well, just not as Intended. The "tbl Lost Cost Codes Check" was
just a list of my 2 Cost Codes. What the query is not doing is telling me
when a Phase does not have a 3V-000 or a 3V-100 Cost Code attached to it.

I'm still a bit stumped on this, can anyone see a way to find out which
Phases have the missing Cost Codes?

Thanks In Advance

 

[Lythandra]

Of course right after putting my thoughts into text, the solution hit me.

I made two make table queries. I did this because the actual queries getting
the data were slow slow slow.

One with the actual data of Project, Phases and Cost Codes.

One with Project, Phases and my two Cost Codes per Phase. I did this part by
making a quick table with just my two cost codes in it and then putting it in
the query but not linking it thus makes each Cost Code appear for each phase.

I then just did a normal unmatched query. Viola!! Easy now.