Getting a list of records that fall between given dates???

[sarajini]

Hi All,
I am a brand new Access user, and I have a rather complex project. How do I use Access to pick out records that fall between certain dates? Please excuse my lack of knowledge about Access terms.

I work in a law office. We have many cases that have an open date and, when we finish them, a close date. Our office wants to take all these cases (they are now all on paper) and put them in a database.

I want Access to spit me back lists of all the cases that were “open” during certain time periods. For example, I’d like to have a program (a query?) that would give me a list of all cases that were “open” during, say, May 2003. I’m envisioning that Access would show me a list of cases following these specifications:

1)The “open date” would be AFTER 4/30/03 and would be BEFORE 6/1.

2)The “close date” for all the cases would be AFTER 6/1 (or blank, since some cases would have no close dates at all.)

Another complication: Sometimes we have clients who have several different cases. So I’m thinking that I should set the client ID as the DB’s primary key, and put all the dates for our cases in subforms. How can I get my program to pick out client records that have ANY cases “open” during the date parameters?


I’d like to write some more “programs” along these lines. For example, I want Access to show me a list of all cases whose “close date” was between two given dates. Also, I want Access to show me a list of all cases “open” during a certain month, but only those under a given “attorney name.”

How should I go about starting this project (aside from reading lots of books on Access, which I plan to do)? I don’t even know whether I should be creating a filter, a query, or something completely different. Will this require some Visual Basic?

I’d appreciate any pointers (or even referrals to other websites or books). I’m using access 2000 and 2002.
Thanks so much!!!
--sarajini

P.S. I work for a no-fee public-service law firm, so you would be helping out some very nice lawyers!!!

 

[Jack Cowley]

You have covered a lot of ground in your post so a specific answer could be long and complex. Let me start off by saying that if you are not really sure how to 'normalize' your tables then that is the first area that you need to understand. Once you know how to normalize your tables then all the rest will be easier. The basic table struture is the key to an efficient and fast database.

You mentioned a separate table for dates when you should probably have a table for Clients and Cases. Now that my be too simplistic since I don't know your data, but you do so you will have to decide how to normalize the tables.

Searching for data between two dates is easy to do, but first you need to get your tables squared away and once you do then I think that would be the appropriate time to learn how to filter the data.

I know this does not answer your question directly, but I think you need to get the struture correct before you plunge into data retrieval...

Jack

 

[sarajini]

how do you "normalize" tables?

Hi Jack,
What does it mean to "normalize" tables?

I think I would like a "master" table for the client name and important info.
Why do I want a separate table for Cases? Because a client can have more than one case, and cases can be of three different types.

So I was thinking I would have something like:

Master Table (holds client name and clientID)
^
Three different Sub- tables (They would hold all the cases that belong to the same client. The dates would be here.)

I am wondering how to connect the Master Table to the sub-tables.
On the master datasheet, I want to be able to expand each master record to show the records from all of the three sub-tables.
On the master form, I want there to be a subform which shows the cases from all of the three subforms.

How do I do that? Would that be with a query?

Thanks so much!!!!!!

You have covered a lot of ground in your post so a specific answer could be long and complex. Let me start off by saying that if you are not really sure how to 'normalize' your tables then that is the first area that you need to understand.
Jack

 

[jfgambit]

An EXECELLENT post by a great Access Wizard...Jerry Dennison:

By request I'm copying and pasting a little ditty on the Forms of Data Normalization I wrote in an effort to help a poster better understand how to normalize their data structure. It is not all inclusive, nor does it adequately (at least in my mind) address the Forms but it may offer some small help to those struggling with their table structures.


Strictly speaking, addresses do not describe the entity that is people. And many would espouse that you separate addresses into their own table and relate them back to the people that reside at that address at that time. This is one of those grey areas where we all tend to violate the forms to a degree. This in no way implies that we should violate them with impunity. Now, when it comes to the definition of entity, attribute, and relationships these are actually quite clearly defined (they're just extremely difficult to understand in the raw form). Database objects have absolutely nothing to do with the data model. It is the data model that the definitions detail as far as I'm concerned. I may as well try to spell out my understanding of the Forms in as simple terms as possible.

First Normal Form:
Every table should have a Key <---- this means that each record must be uniquely identified
All ATTRIBUTES must be atomic <----- this means that there should be no repeating groups within a field (i.e. multiple values within a field separated by a comma or other delimiter) Strictly speaking, this phrase has nothing to do with repeating groups of fields or tables, that is actually covered (however esoterically) by other Forms. BUT, I tend to expand the meaning of this Form to include repeating groups wherever they may be found.

Second Normal Form:
Must be in First Normal Form <---- the forms are heiarchical, each is dependent on the one before it
A RELATION is in second normal form if each ATTRIBUTE is fully functionally dependent on the ENTIRE primary key <---- this means that no subset of the key can determine an attribute's value

Third Normal Form:
Must be in Second Normal Form
A RELATION is in third normal form when no non-key attribute is dependent on any other non-key attribute <---- this and 2NF are the primary Forms that prohibits the storage of calculated values or transitive dependencies.

BCNF
Must be in Third Normal Form
All candidate keys must satisfy the test for third normal form <---- a candidate key is of itself a potential unique identifier of the entity, generally speaking candidate keys are mutli-field constructs. This does not mean you should use a candidate key as the PK, it means that it could satisfy the requirements of uniqueness. For most entities, there are many candidate keys.

Fourth Normal Form
Must be in 3NF/BCNF
There can be no nontrivial multivalued dependencies in a relation <---- This is a fairly common reduction that most people achieve without even knowing it. This form prohibits independent multivalued components of the key. For example, if an employee can have many skills and many dependents you would move the skills and dependents to separate tables as they are not related in any way.

Fifth Normal Form
Must be in Fourth Normal Form
This is Nervana of DB design and is seldom reached. Basically, it advocates that you continue splitting the structure down until either of two states exist: that you've split so far that the resulting tables could not be joined to reconstruct the original, OR further splitting would be trivial.

Natually, this doesn't even come close to really describing what you're trying to accomplish. There are also definitions that need to be understood, specifically around what an entity is, what an attribute is, what a relation is, functional dependency (a tough one, the formal definiton of Functional Dependence is: For any relation R, attribute A is fully functionally dependent on attribute B if, for every valid instance, the value of B determines the value of A.), then of course there's multivalued dependency, trivial dependency, and last but not least candidate key.

Now, to boil all of this down to something usable. You must ask yourself the following:

Am I repeating groups? These can be multiple values in a single field, repeated TYPES of fields that share the same datatype and a common root name or root structure (these are the multiple date fields in Drew's db), or repeating tables of the same entity type (these can be noticed because they generally have the same fields but in different tables).

Am I trying to store derived or calculated values? (we should all know by now not to store calculated values)

Do I have multiple tables with more than one index based on multiple fields? (this usually indicates combining of entities, remember: an table is an entity and an entity a table)

Do I have a large number of values being repeated in a column (field)? This does not include FK's linked to another table's PK. It does include just about anything else. If you find that you're repeating a lot of values then you MAY need to move this to it's own table. This particular question is lowest in priority.

I hope this long winded diatribe helps a little.

Jerry Dennison


To view the original post go here: Normalization

 

[Jack Cowley]

Sarajini -

For a simple tutorial on Normalization go here.

hth,
Jack

 

[sarajini]

Hi Jack and thanks! You guys are really friendly! I think I'll keep it all in the Second normalization level. I have one ClientID, so I have a one-to-many relationship. I will connect all the cases to that one ClientID.

Yes, there will be lots of duplication of entering for fields like "referral source." But we're converting from Excel, where that stuff is all listed in full already. I don't want to confuse people who will be entering data into tables after me.

What's confounding me right now is the (simple?) question of getting all of the three subdatasheets/subforms to pop up in the main datasheet/form.

--sarajini

 

[Jack Cowley]

A main form can be in Datasheet view but you cannot have subforms too. Your main form must be in Form View in order to add subform(s). And with a One to Many relationship you shouldn't have a Datasheet view for the main form (One side of the relationship) anyway.

If the users are used to Excel then if you are moving to Access you will need to do a bit of retraining. If you duplicate data for the easy of the user then you are defeating Access and might as well stick with Excel. That is my 2 cents worth....

Jack

 

[sarajini]

Okay...I'm going to focus on adding subforms.

I am in Form View. How do I get there to be multiple forms? I want the forms from three tables (two have the same fields, one does not) to pop up in the same subform box on the main form.
Can I make all three tables pop up in the same box? (I want them all to count as "records"--so that you could see "Record 1 of 3" in the subform box.

If it's not possible to make all 3 pop up in the same box, how do I make the two subforms with all the same fields pop up in the same box?

also: what's the matter with a datasheet view for the main form? that it will only show the "one" side and not the "many"--the subdatasheets? I want the datasheet to expand to show the three subdatasheets as well.

thanks so much!
sarajini

A main form can be in Datasheet view but you cannot have subforms too. Your main form must be in Form View in order to add subform(s). And with a One to Many relationship you shouldn't have a Datasheet view for the main form (One side of the relationship) anyway.

Jack

 

[Jack Cowley]

You can't use a datasheet view for a main form if you want to add subforms as there is no place to add the subform(s). The datasheet takes up the entire form.

The only way you can have records from different tables in a single form or subform is if you have a query based on the tables and base the form on the query.

You will find a tutorial here on how to create subforms....

hth,
Jack

 

[sarajini]

You will find a tutorial here on how to create subforms....
[/B]

looks good...is there also a tutorial that would help me build the type of query I want?

thanks again!!!
sarajini

 

[Jack Cowley]

You are welcome!

I used Google to search the Internet for the Tutorial that I suggested so you may want to try that. And you can look at that website to see if they have a tutorial about queries.....

Good luck!

Jack

 

[sarajini]

The only way you can have records from different tables in a single form or subform is if you have a query based on the tables and base the form on the query.

OK...I read the tutorial and looked for more online.
I tried making a query for the three tables. It has each of the different fields for each of the tables I'm manipulating.
Then I went to make a subform. Problem is, I want each of the three subforms to pop up as separate entities WITHIN the same subform box. I don't want there to be one subform that combines all the fields from the tables.
Is that possible? Or should I just make separate subform boxes for the separate subforms?

Thanks for your patience in the face of all my bugging!
sarajini

 

[Jack Cowley]

You can make a separate subform for each item that you want to appear on the mainform. As long as you can properly relate the data you can have the three separate subforms on your main form. These three subforms can be in a forth subform if you like so you and have your mainform and subform with 3 subforms within the first subform.

I hope that is what you are after...

Jack

 

[sarajini]

hmmm...that's a good idea, but only kind of what I was looking for. thank you so much for all your attention...I am thinking that what my boss suggested doesn't exist in access. ; )

I'll try for two subform boxes: one subform to hold the set of cases with different fields, and one subform to hold the two sets of cases that have identical fields.

I think the best way to go is try to combine the two sets of cases with identical fields into the same query.

How would I do that?
The two tables have the same number of fields and the same name for the fields. I want to display them in the same datasheet view, listing each field only once. Old records (1-100) should be listed _above_ new records (101-200).

Then..it seems...I'd have a nice "combined" query that I could base a single subform on.

Would I use a union query? don't those work only when the identical fields also have identical values? There's gotta be something simple for this, it seems!

thanks again, jack!!!
sarajini

 

[Jack Cowley]

Why do you have two tables with the same fields? I assume that they have similar data and if that is so then you should only have one table. I am beginning to think that your database structure is not correct, especially if you have two tables with the very same fields and this may be why it is difficult to display the data you want. Tell my why you have the two tables and lets start there...

Jack

 

[sarajini]

Good question, Jack. Let's see if this is reasonable:

We have 9000 cases. All cases from roughly #4000-#9000 are in an Excel database. For old cases, we have about 4000 paper files, and nothing on the computer.

The old cases are completed. We need to add each record in one fell swoop. For the new cases, we are adding bits and pieces of records as we complete them.

We are going to work on entering in whole old cases as we enter new cases piecemeal.

Some "old" cases actually have open dates and case numbers AFTER those of new cases. I don't want people to get the two types of cases (those that need to be all filled in, and those that need to be partially filled in) all mixed up. I want to be able to take a quick look at the oldcases table to see how far along we are in entering them in.

However, knowing how to combine two tables with the same fields would be helpful. how would one go about doing that?

Regarding the larger database.
I have one table for clientinfo. The table will be connected to the 2 case tables and 1 table for brief services, because one client can have any combination of cases and brief services. I do plan on making tables for most repeated data (demographic info, referral source) so we don't need to continually type those in.

thanks again,
sarajini

 

[Jack Cowley]

You only want one table for cases, old, new, complete or incomplete.. Entering them in any order is not a problem for Access, only people, so you need a way that you can see the data in a way that you can relate to it and that is what queries are all about. You can add a Yes/No field to your table called Completed. Completed cases get this box checked. Using a query you can see completed cases or those that are not completed. If you have more than one type of case (Old, New, Completed, Incomplete, for example) then add a Foreign key to the table and create a new 'lookup table' with the case 'types' and store the primary key of the case type in the field. Now you can look at Old cases, Completed cases or whatever and you can add to the list of Case Types if you need to.

hth,
Jack

 

[raskew]

The table setup sounds like it would be very similar to Access database template Students and Classes. There would be three tables:
tblClients with unique client info.
tblCases with case detail
tblClientsAndCases would link the two primary tables.

This arrangement could be particularly useful if the possibility exists that more than one client is involved with a particular case.

You might want to take a look at and build the template, if for no other reason than to provide ideas for table usage.

Bob

 

[sarajini]

You only want one table for cases, old, new, complete or incomplete..
You can add a Yes/No field to your table called Completed. Completed cases get this box checked.

Hi Jack,
That makes so much sense!!!
I just have two more questions about implementing this solution:

1) How should I combine the old and new tables? I'm working with mockups right now. I tried to do a make-table query from the union query that I used to list the Old cases above the New cases. But the unionquery is in SQL, and access won't let me get into "design view" for the query.

2) I think I'll make a "new" box to check. When I actually import the data from excel... how should I tell access to check "new?" on all the records imported from the "new" table?


thanks again!!!!
sarajini

 

[Jack Cowley]

1) Use an Append query to append data from one of the tables to the other.

2) This one I am not so sure about as I haven't done any importing from Excel. My idea would be to get the last ID in the table and then after the import use an Update query to update the check box for all records that have an ID >= the ID before the records were added. You can do all of this in code so that it is automatic.

hth,
Jack