1) Run query through a table and 2) keep (or throw away) records

[Tamara]

Here's what the data might look like:

ID M MStatus Appl ApplStatus Art ArtStatus Appr ApprStatus
-- --- -------------- ----- -------------- ------ -------------- ------
------------
1 M1 In Progress 1A In Progress MA1 In Progress AP1 Pending
1 M1 In Progress 1A In Progress MA1 In Progress AP2 Pending
1 M1 In Progress 1A In Progress MA1 In Progress AP2 Approved
1 M1 In Progress 1A In Progress MA1 Complete AP2 Approved
1 M1 In Progress 1A Complete MA1 In Progress AP3 Pending
1 M1 Complete 1A In Progress MA1 Complete AP3 Approved

Each:
- Unique ID has 1-N milestones
- Milestone has 0-N applications
- Application (or if no Application, Milestone) has 1-N Artifacts
- Artifact has 1-N Approvers

The process is when All:

- Approver Status = Approved then the Artifact Status = Complete
- Artifact Status = Complete then the Application Status = Complete
- Application Status = Complete then the Milestone Status = Complete

Logic dictates that since there are multiple records, that all records would
get updated with the "complete" status but nooooo...that is not the case as
you can see. I have records with conflicting information. Sadly I have no
control over how the data is stored and updated. I can only retrieve the
data set and pull the desired records out of this pizzaship table.

So hopefully I know what I need to do (below) ... and I need help on how to
do it:

1) pull all records for ID and Milestone
2a) if there is one record where Milestone Status = complete then throw all
records in the query away (or save records somewhere where Status <> complete)
2b) if not, run a subquery on ID/Milestone/Application
3a) if there is one record where Application Status = complete then throw
all records in query away
3b) if not, run a subquery on ID/Milestone/Application/Artifact
4a) if there is one record where Artifact status = complete then throw all
those records
4b) if not, run a subquery to find duplicate
ID/Milestone/Application/Artifact/Approver and delete any duplicate records
5) Then count the remaining artifacts where Artifact Status <> Complete
6) Repeat from Step 1

I figure some sort of iterative query (which I haven't been able to figure
out) and either 1) delete records, 2) flag records, or 3) append remaining
records in a new table (or another option that someone smarter than me can
suggest).

Oh...and since I'm not a programmer and need to pass this on to someone else
with no programming experience, VB probably is not a good option.

TIA for any guidance and suggestions. Regards -Tamara

 

[Daryl S]

Tamara -

I don't know what you mean by "throw all records away", but I will focus on
showing the records that you would not have "thrown away"... I would do this
in two queries. The first one will pull all the records for IDs that have no
"Complete" MStatus, ApplStatus, or ArtStatus (I believe this will handle
steps 1 through 4a, maybe 4b). The second will count the results depending
on your needs. You will need to use the correct table and fieldnames in
your query, but otherwise, you should be able to paste these into the SQLView
in Query Design. Here is the first query:

SELECT DISTINCT ID, M, MStatus, Appl, ApplStatus, Art, ArtStatus, Appr,
ApprStatus
FROM YourTableName
WHERE
ID not in (Select ID from YourTableName WHERE MStatus = "Complete")
AND ID not in (Select ID from YourTableName WHERE ApplStatus = "Complete")
AND ID not in (Select ID from YourTableName WHERE ArtStatus = "Complete");

I am not sure what you call a duplicate in 4b. You only mention the
ID/Milestone/Application/Artifact/Approver fields, and not their status
fields. Would you include the second and third records in your example to be
duplicates, since only the ApprStatus is different? (Ignore the fact that all
M1 records would have been removed because of step 2a). If you don't care
about the different statuses, then you can take the first query above and
remove the MStatus, ApplStatus, ArtStatus, and ApprStatus fields from the
SELECT DISTINCT, and the second query will give you what you are asking for.
If you do not think of these as duplicates, then leave the status records in
place.

Then, to get a count the artifacts (none will be "Complete" due to step 4a),
do this:

SELECT ID, M, count(*)
FROM NameOfFirstQuery;

 

[John Spencer]

I am confused by what you mean when you say throw away.

Here is a query that will return all records that have any apprstatus that is
not approved and MStatus is complete.

SELECT SomeTable.*
FROM SomeTable
WHERE Exists (SELECT * FROM SomeTable as Temp WHERE Temp.ApprStatus <>
"Approved" and Temp.Id = SomeTable.ID)
AND MStatus = "Complete"

Trying to understand, but it seems as if you only want records where there is
== NO Milestone that is complete
== N0 ApplStatus that is complete
== NO ArtStatus that is complete

So step one would be to find the records that DO have one of those values complete

SELECT ID
FROM SomeTable
WHERE MStatus = "Complete"
OR ApplStatus = "Complete"
OR ArtStatus = "Complete"

Now use that in an unmatched query to get all the records that DO NOT meet
that criteria.

SELECT *
FROM SomeTable LEFT JOIN
(SELECT DISTINCT ID
FROM SomeTable
WHERE MStatus = "Complete"
OR ApplStatus = "Complete"
OR ArtStatus = "Complete") as Temp
ON SomeTable.ID = Temp.ID
WHERE Temp.ID Is NULL

And at this point I am lost as to what you want.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Tamara]

Daryl:

I figure I have three options to handle the records I don't want to count
(or handle the ones I want to count...1) Flag; 2) Delete or 3) Move/Copy to
new table. Let me test out your suggestion.