Complicated (for me, anyhow) query relating to records management

[joshuahmaddox]

Hi - I need help figuring out how to query data so that it only shows me results that meet very specific criteria. I may be in over my head here, but I will explain as best I can.

I have a table which has BOX_NUM, JACKET_ID and CLOSED_DATES. BOX_NUM and JACKET_ID are unique. The BOX_NUM represents a physical box and the JACKET_ID represents a physical record in that box. CLOSED_DATE represents the date that a physical records were closed. There can be 1 or more JACKET_ID in a BOX_NUM and each can have identical or unique closed dates.

I need to run a query that shows me only BOX_NUMs that contain only JACKET_IDs that have a CLOSED_DATE > 10 years. If a BOX_NUM has at least 1 JACKET_ID that has a CLOSED_DATE < 10 years or is null, then it does not qualify. I can easily run a query to show me only JACKET_IDs with a a close date > 10 years, but it does not tell me which BOX_NUMs contain only JACKET_IDs with a CLOSED_DATE > 10 years. Instead, it only shows JACKET_IDs in a BOX_NUM that have been closed for greater than 10 years.



I am admittedly a complete rank amateur and I am sorry if I have not explained this very well. Please let me know if I need to explain better.

Thanks.

[burrina]

BOX_Num Is Not Null and CLOSED_Date > 10 and JACKET_IDs Is Not Null
Is this what you are asking?

[joshuahmaddox]

My God man! You are a genius! Not sure this solves all my problems but it is a great start. THANK YOU!

I still do not understand how this works though. BOX_num is never null nor is Jacket_Id, I get that. But why is it returning only boxes with only jackets closed for greater than 10 years, if you don't mind?

[burrina]

So, BOX_num and Jacket_Id are unnecessary in your query since they as you said always have values? You are really only asking for CLOSED_Date criteria?
I am not following? If I understand correctly, remove criteria for Box_num and Jacket_Id

[joshuahmaddox]

I was mistaken. That did not work. The first 10 boxes I checked were correct, but then I discovered many that weren't.

I'm not just looking for jackets with closed dates greater than 10 years. I am looking for boxes that contain only jackets closed greater than 10 years. If a box has 1 or more jackets that have not been closed for greater than 10 years, I do not want it to show up.

BOX_NUM	JACKET_NUM	CLOSE_DATE
8339848397	023888.00078.002	06-Jan-09
8339848397	001521.00004.002	26-May-98
8339848397	001977.27089.001	06-May-97
8339848397	003833.00021.003	18-Mar-03
8339848397	007366.00028.002	02-Aug-02
8339848397	025861.00018.002	06-Jan-09
8339848397	026958.00001.002	22-Sep-00
8339755020	001521.00043.001	05-Feb-02
8339755020	001521.00046.001	05-Feb-02
8339755020	001521.00047.002	05-Feb-02
8339755020	001521.00048.001	05-Feb-02
8339755020	001586.00017.001	05-Feb-02
8339755020	001708.00005.001	05-Feb-02
8339755020	001827.00010.002	07-Oct-02
8339755020	001827.00016.002	07-Oct-02
8339755022	001997.00006.001	05-Feb-02
8339755022	005025.00021.001	28-Feb-02
8339755022	005025.00022.001	28-Feb-02
8339755022	007366.00028.001	02-Aug-02
8339755022	004906.00009.001	21-Oct-03
8339755022	004906.00010.001	22-Oct-03
8339755022	007344.00010.001	06-Jan-09

In the table above, I would want to run a query that shows only box number 8339755020 since every jacket number in it has a closed date greater than 10 years. 8339848397 and 8339755022 both have at least one jacket closed less than 10 years ago, so I do not want them to appear in my query results.

[burrina]

You said Boxes always have a value, so that can be taken out of the criteria! So, Jacket_Num Is Not Null and Close_Date < 10 , is this now what you are asking?

[joshuahmaddox]

I do not believe boxes can be taken out because what I want to ultimately end up with is a list of boxes. If I put in the criteria you have listed above, then it shows me every jacket in a box that has been closed for more than 10 years. It does not show me only the boxes where every jacket contained in it has been closed for greater than 10 years. Every jacket in a box has to be closed greater than 10 years. If even one jacket in a box does not meet that criteria, then it should not show up in query results.

[burrina]

So all dates (Close_Date) less than 10 years, YES or NO ?
Do ALL Boxes have a Value? Yes or No?
Do all Jackets have a Value, Yes or No?

[joshuahmaddox]

No. Greater than 10 years.
Yes.
Yes.

[joshuahmaddox]

I need a way to count the jackets in a box, then count the jackets in the box with a closed date greater than 10 years. If jackets in box = jackets in box with close date greater than 10 years, then show results in query. If jackets in box does not equal jackets in box with close date greater than 10 years, then do not show results in query.

[burrina]

So Close_Date IsDate And >10

[joshuahmaddox]

Yes.

I really, really appreciate your willingness to help.

[burrina]

So try this under your Close_Date criteria.

Is Not Null And >10

[joshuahmaddox]

That's not going to work. That only tells me if a jacket in a box has been closed for greater than 10 years. It does not tell me that all jackets in a box have been closed greater than 10 years.

[burrina]

Perhaps someone else can assist. We are not on the same page. Sorry!