Multi-Step Query Troubles ... Sample Attached (1 Viewer)

[padlocked17]

All,

Base Info:
AllEventsRaw Union Query

SELECT SSAN, Task_ID, QTY, ACC_DT, Method, ENT_DT, PgmCode
FROM Flying_Current
UNION ALL SELECT SSAN, Task_ID, QTY, ACC_DT, Method, ENT_DT, PgmCode
FROM Flying_Archive;

DualCredits Query

SELECT DISTINCT DualCredits.Task_ID, DualCredits.Force_ID
FROM DualCredits;

Desired Outcome (A Query with the following Column Headers)

SSAN
Task_ID
ACC_DT_FINAL (MAX of ACC_DT for either the Task or a Task that is Dual Credited)

I'm trying to take a Union Query that contains a giant list of all the Tasks (Task_ID) someone has accomplished with Accomplished Dates (ACC_DT) and combine that with a DualCredits query to generate a list with Distinct rows for each person based on the Task_ID.

For example someone could complete Task_ID 'P020' 20 times, but the only ACC_DT I'm concerned with is the Max Value for Task_ID 'P020' or if P020 can receive credit from another task such as P028 or P029, I want the ACC_DT from P028 or P029 to be used as the ACC_DT for P020.

I've attached a sample database of what I'm trying to work on. I edited all the sensitive info with bogus numbers.

I feel like I figured it out (although I'm not sure the way I accomplished it is the best) up to the Step3 query. You can see in record #174 it yields a date of 4/17/2009 when in the Step2 query (records 208-215) the Max date is 4/13/2011 and the should be the correct date that it gives me.

If any of this made sense and someone could help, I would very much appreciate it. I'm beating my head against a wall on this one. I very well could be going about this the entirely wrong way.

 

[boblarson]

Russ:

Check out the two queries I did (I used your union query as the base):

qryGetMaxDateOfTraining (my first one)
qryFinal (which has the latest info for each training for each person)

 

[padlocked17]

Bob, thanks for taking a look.

The problem is that it isn't referencing the DualCredits table/query at all. Essentially that DualCredits table will allow another task (with the date in the union query) to override the date for a task which is defined in the DualCredits table.

Does that make any sense?

 

[boblarson]

No, it doesn't make sense can you maybe draw a visual (I'm more visual) of what you are meaning?

 

[padlocked17]

Hmm. I'm thinking of how to visually lay it out. Let me try explaining it once more.


Union Query

• Simply lists every time a task was completed and when.

As an example, I can use a Totals query to find the Max Date of the Task with Task_ID of "P020". My DualCredits query shows me what other task's dates can be used in lieu of the "P020" max date if there is an entry in the DualCredits query. In the FixedDualCredits query it shows that P010, P028, and P029 can all have their accomplished dates override the dates for P020 if one of those dates is more recent. My Step1 query shows under the "ReceiveCreditFrom" column which tasks should also be looked at to find the true max date out of all of the possible tasks.

If that helps, I'll put away Microsoft Paint, otherwise I'll see if I can't come up with a illustration of what I'm looking for.