Correlated Subquery Help

[DeadPixel81]

Hi,

I am an Access newbie and I am looking for help with a problem I have encountered. Please see my attached image for a summary of the tables I am working with.

Basically, the complexity of this query is beyond my tiny intellect; and my research into nested queries, inner and outer joins and subqueries has left me more confused than anything.

Here is a brief summary of my tables:

A [File] has multiple [Parent Stages] that has multiple [SubStages].
 
Each File is either open or closed.
 
Each Substage belongs to a Parent Stage and has a status of 1(Complete), 2(Incomplete), 
3(Not Applicable). 3(Not Applicable) is included as Completed.
 
Each Substage has a 'completion weight' within its Parent Stage.

Here is what I am trying to do:

I want to pull all open files. For each file I want the summarized completion percentage of 
each stage.

Here is what I think that would look like, based on my summary picture with data:

File_ID | Drafting | Closing | Buyer  |  Category 
54         75%        50%       Ange    ITT
57         75%        100%      Sienna  RFP

1. All rows with a SubActivityStatus of 1 or 3 in tbl_FileStatus are selected.

2. The completion weight and ParentStage for each row is appended to the end, this is obtained from tbl_SubStages based on the tbl_FileStatus.[Substage_Reference_ID] = tbl_SubStages.[SubStage_ID].

3. The completion weight for each Substage is added together for each individual parent stage. So there are 2 parent stages: Drafting and Closing. All completion weight numbers for Drafting are added together for file number 54. Then all completion weight numbers for Closing are added together for file number 54. Then the same is done for file 57.

4. The files are checked against the tbl_Files using tbl_FileStatus.[File_Reference_ID]. I need to determine if the file has been checked off as being closed. If tbl_Files.[Closed] = True for a file, I need to remove that file from the selection.





I hope I am making sense. I tried to make this as easy to follow as possible. If any more information is required, please let me know. Any help or hints on where to look or how to get started would be greatly appreciated.

Thank you,
DeadPixel

 

[plog]

You have a structure issue, tbl_FileStatus is unneccessary. It's data (SubActivityStatus, File_Reference_ID) should be in tbl_SubStages. You do that and this query is simple:

SELECT File_ID, SUM(Iif(ParentStage_Reference=1, SubStage_Completion_Weight,0) AS Drafting, SUM(Iif(ParentStage_Reference=2, SubStage_Completion_Weight,0) AS Closing, Buyer, Category
FROM tbl_SubStages
INNER JOIN tbl_Files ON tbl_Files.File_ID = tbl_SubStages.File_Reference_ID
WHERE (SubActivityStatus=1 OR SubActivityStatus=3) AND Closed=False
GROUP BY File_Reference_ID, Buyer, Closing;

 

[DeadPixel81]

Thank you for the response! I will take a look and see if I can accomplish this. There is a problem I have to get around though.

I separated tbl_Substages from tbl_FileStatus so that substages could be added in the future through a form. When a new file is created I have written a VBA event to check tbl_Substages and create a new row for every substage in tbl_FileStatus.

If I combine tbl_FileStatus into tbl_Substages I don't know how else I could implement this. I guess the alternative is to set up the SubStages in VBA. Although, that would mean that the code requires changing if a substage is added or needs changing.

 

[plog]

VBA event to check tbl_Substages and create a new row for every substage in tbl_FileStatus.

If I combine tbl_FileStatus into tbl_Substages I don't know how else I could implement this.

You don't need to. That event is obsolete. The tbl_FileStatus data is now in the same record as tbl_Subtages. If a tbl_Substages record gets created, it will have fields for the data that was once in tbl_FileStatus.

 

[DeadPixel81]

You don't need to. That event is obsolete. The tbl_FileStatus data is now in the same record as tbl_Subtages. If a tbl_Substages record gets created, it will have fields for the data that was once in tbl_FileStatus.

Sorry, I was away for the weekend.

I must be too new to grasp the concept above, easily.

tbl_FileStatus has a record for whether every substage in every file is completed or not.
tbl_SubStages has a record for every substage.

When a new file is created, the event looks at tbl_Substages to determine how many records to add in tbl_FileStatus. The number of substages in tbl_Substages can increase over time.

Currently there are 57 substages. So, when I create a file using a button in Access, 57 records will be added to tbl_FileStatus with the ActivityStatus of 2(incomplete). In the future, if 10 more substages have been created over time, a new file will create 67 records within tbl_FileStatus. Currently, there are 6 files in existence. Therefore there are 6x57=342 records in tbl_FileStatus.

How can I fluctuate the number of records in tbl_FileStatus for files being added without having a separate table to house the fluctuating substages? I get that currently tbl_FileStatus has a record for every SubStage, but I do not get how the table can account for changes in the number of substages. When a new substage is created I do not want it to affect previously created files.

 

[plog]

Your structure is correct, I misinterpreted your initial diagram. I thought tbl_SubStages did the job that tbl_FileStatus actually does.

Can you post a .mdb file with some sample data? I understand your issue now, I just need some data to test my solution on.

 

[DeadPixel81]

That's good to hear, I was getting frustrated with myself for not being able to understand, haha.

Sure, please keep in mind - I am a beginner so I am positive that some of the ways I did things are over complicated or not entirely correct.

 

[plog]

I need a .mdb format

 

[DeadPixel81]

Access wouldn't let me save as an MDB so I created one an imported everything.

Most of the functionality has been lost due to changes with tempvars, etc. However, the structure is there. I hope this works.

Thank you so much for all of your help.

 

[plog]

That's not the same thing as you initially presented (field names are different, data isn't the same). So, based on the file you have given me, what do you expect as the resulting data? Be sure to provide me with field names as well.

 

[DeadPixel81]

I simplified it in my original post in an effort to make things easier to read/understand. Sorry about that.

Based on the tables that I attached above, I would like the results of the query to look like this:

File ID | Buyer | Description | Assess |    Draft |    Post |  Close |  Eval |  Award |  Contract
54        Teemo   This is th...   -           -         -        -        -        -         30%
57        Singed  Descrip.....    67%        50%        -        -       67%       39%       21%       
58        Singed  This is a r..   67%        60%        -        -         -         -         -

[File ID], [Buyer] and [Description] are from tbl_Files
The file must be [Closed]=False from tbl_Files
Assess, Draft, Post, Close, Eval, Award and Contract are stages 1,2,3,4,5,6,7
The percentages are from tbl_Substages.[Substage Stage Weight]
They are added together based on the stages above, which are referenced in tbl_Substages.[Stage Reference ID]
The substage must be status 1 or 3 which is from tbl_FileStatus.[SubActivityStatus]

 

[plog]

This is going to take a cross-tab query built upon a sub-query. Here's the SQL for the Sub-query:

SELECT tbl_FileStatus.[File Reference ID], tbl_Stages.[Stage Name], Sum(tbl_SubStages.[SubStage stage Weight]) AS Per
FROM tbl_Stages RIGHT JOIN (tbl_SubStages RIGHT JOIN tbl_FileStatus ON tbl_SubStages.[SubStage ID] = tbl_FileStatus.[SubStage Reference ID]) ON tbl_Stages.[Stage ID] = tbl_SubStages.[Stage Reference ID]
WHERE (((tbl_FileStatus.SubActivityStatus)=1 Or (tbl_FileStatus.SubActivityStatus)=3))
GROUP BY tbl_FileStatus.[File Reference ID], tbl_Stages.[Stage Name];

Save that query, naming it 'PercentComplete_sub'. Then for your results this is the SQL to use:

TRANSFORM Sum(PercentComplete_sub.Per) AS SumOfPer
SELECT tbl_Files.[File ID], tbl_Files.[Buyer Reference ID], tbl_Files.Description
FROM tbl_Files LEFT JOIN PercentComplete_sub ON tbl_Files.[File ID] = PercentComplete_sub.[File Reference ID]
WHERE (((tbl_Files.Closed)=False))
GROUP BY tbl_Files.[File ID], tbl_Files.[Buyer Reference ID], tbl_Files.Description
PIVOT PercentComplete_sub.[Stage Name];

 

[DeadPixel81]

Wow, you are great. This works perfectly, I can't thank you enough. The mechanics of these queries are quite beyond me right now. I'll be spending some time to desipher and understand them, that is for sure!

Cheers mate, if you are ever in the Vancouver, BC area shoot me a message. I owe you a few beer!

 

[plog]

No problem. Just for your reference, the sub-query is the work horse of the thing. It gets all the values you need, applies the criteria, etc. It just doesn't produce the exact format you wanted--that's why I used the second query. A cross-tab query will convert the values of one column of data into column headings.