Calendars - Date Ranges - Help

[Klubby]

I'm a little stumped on this one. I know there are brilliant folks out here to help, so I thank anyone in advance for some ideas.

I am creating a document inventory database for clients engaged in litigation cases. Previsouly, our firm would hold all the information in Excel which proved to be very inefficient.

Getting to the point, I need to set up some sort of form where a user selects the client and account (set-up in other forms), then is able to select the dates for the documents that are in-house. For example, brokerage statements come once a month and in a case, they could be requested monthly for the past three years.

I have the tables set-up for everything except for the date ranges. I need to have the user first identify which dates have been requested (January 1999 through December 2002) for each account/brokerage statement. The users would like to then have a simple form where they can just check boxes for each month/year for the documents that are in our posession.

This will generate reports based on what is in-house, what is missing, grouped by various types, etc...

I am just stuck on how to go about these dates. Any ideas.

Thanks again.

 

[FoFa]

If you ahve a Documents Table, with a date of the document, tied to your client or account or whatever, and have one row for each document, you can then select documents by date, by a type of document, etc. A simple Min(DocumentDate) and Max(DocumentDate) where account = xxxx would give you the date range. A list box could list all the documents and allow selection.

 

[Klubby]

I understand what you mean by that and I believe I have that set-up. My concern is how to capture each month and year in the row/table also based upon user checking the appropriate months in the form.

Another example... say we ask for a certain account's statments for each month from Janaury 1999 through March 2003. The litigant only provides maybe on the 1/99, 4/99 and 5/2001 stateemnts. I want the form to show maybe a drop down list for the years, the user would select 1999 and then can click checkboxes for each individual month, in this case, Januaryand April. then, the user can drop down the year again and choose 2001, then choose the May checkbox.

Then if we ran the missing document report for that account, it would show something like...

1999
February, March, May, June, etc....

2000
Jan, Feb, March, etc...

2001
Jan, Feb, March, April, June, July, etc...

 

[Klubby]

I'm still very stumped on this one..... ugh!

 

[FoFa]

Sounds like by your description, if I understand, you would need to HOLD the first year/months combinations before they select the year and the months again. So if they select 1999, and Jan and May, you need to hold 1/99, 5/99 before they select again. Have you considered just giving them a combo box of those selections (list might be long) say 1/99 2/99/ 3/99 etc. and allow them to select all of them at one time they want? Once you have the selections saved somewhere, you can run queries how you want. You could even use a work table to store the selected dates and use that in your queries. But maybe I am still not understanding the problem.