Stumped With Dates

[Daryl]

I’ve developed a query that produces a list of forms and an effective date where the form may be applicable. But now I am stumped. I need to reduce the results to a specific state (which I’ve done and it works) and for a specific date (the stumped part). Let’s say user needs 2/15/2005. I think I can eliminate forms with effective dates greater than 2/15/2005 (not quite sure how yet) but I am perplexed how to eliminate certain forms that are no longer valid because they’ve been superceded. In the sample table for CA, I would expect the following forms retuned and no others: CG0001 1204; CG0002 1204; CG0009 1204; CG0033 10 01; CG0034 1204; and CG0035 1207. The expected results for the CT column would be different (though some would be the same).

Any advice to point me in the right direction would be appreciated.

FormNum Form_vdt CA CT
CG0001 0798 09/01/1999
CG0001 1001 03/26/2001 02/01/2002
CG0001 1204 07/13/2004 07/01/2005
CG0001 1207 12/05/2007 12/01/2007
CG0002 1001 03/26/2001 02/01/2002
CG0002 1204 07/13/2004 07/01/2005
CG0002 1207 12/05/2007 12/01/2007
CG0009 1001 03/26/2001 02/01/2002
CG0009 1204 07/13/2004 07/01/2005
CG0009 1207 12/05/2007 12/01/2007
CG0033 0196 03/26/2001 05/01/1996
CG0033 1001 07/13/2004 02/01/2002
CG0033 1204 12/05/2007 07/01/2005
CG0033 1207 12/07/2007 12/01/2007
CG0034 1001 03/26/2001 02/01/2002
CG0034 1204 07/13/2004 07/01/2005
CG0035 1001 12/05/2007 02/01/2002
CG0035 1204 03/26/2001 07/01/2005
CG0035 1207 07/13/2004 12/01/2007

 

[jzwp22]

I'm not clear on the logic of what determines when a form is superceded. Looking at just the CG0001 data and just the CA column, I would have picked the CG0001 1207 as the most current form since it has the most recent date in the CA column, but you picked CG0001 1204


FormNum Form_vdt CA
CG0001 0798 09/01/1999
CG0001 1001 03/26/2001
CG0001 1204 07/13/2004
CG0001 1207 12/05/2007

If my goal was to pull only those forms with the most recent date in the CA column, I would use the following nested query. Perhaps this will give you some ideas on how to solve your issue.

SELECT tblForms.FormNum, tblForms.Form_vdt, tblForms.CA
FROM (SELECT tblForms.FormNum, Max(tblForms.CA) AS MaxOfCA
FROM tblForms
GROUP BY tblForms.FormNum) qryMostCurrentCAForms LEFT JOIN tblForms ON
qryMostCurrentCAForms.MaxOfCA = tblForms.CA AND (qryMostCurrentCAForms.FormNum = tblForms.FormNum);

This query returns the following records:

FormNum Form_vdt CA
CG0001 1207 12/5/2007
CG0002 1207 12/5/2007
CG0009 1207 12/5/2007
CG0033 1207 12/7/2007
CG0034 1204 7/13/2004
CG0035 1001 12/5/2007

 

[giovaanni]

It looks like you have a date1 and date2 for your effective date range of your forms. In your query you could have a calculated column with an IIF function that looks like IIF(MyDate between date1 and date2, 1,0) then just select all records that return 1.