Report based on Multiple Date Fields

[viper210]

I have created a form which tracks activities/status of my clients, and on this form, I have 30 unique date fields to keep track of when 30 activities must/will take place.

Our clients go through a process which in some cases can take up to a year to complete. And throughout the course of the year, my organization must perform services or act in behalf of the client on specific dates.

We have carefully and methodically identified all 30 activity dates and have that level of detail on the main form.

We simply need a report (A tickler report if you will) that will list only those clients who have upcoming activities (current week/following week) that may be in one of the 30 date fields so that we can be proactive in responding to the client in a timely manor.

What I am attempting to do is print a report showing only the clients who have populated dates in one or several of the date fields that fall in between the range of a 1 week time span. (EX: 7/23/2012-7/27/2012)

I know I will need Start and End Date fields for the date range but don’t know if I should put this on a command button or build a query (don’t know how to build either one)

Some of the activity date fields are:

• PropSaleDate
• ActualSaleDate
• RedemptionEndDate

I found the following code to create a query to track one date field:


Private Sub cmdPreview_Click()

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptSales"
strDateField = "[PropSaleDate]" lngView = acViewPreview


If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If



If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
If you look at the strDateField, it only tracks only oneof the many fields I have to track. How do I modify the code to track multiplefields?
It would be helpful if I could perform a print preview prior to printing

Remember if you can assist, please reply in the most elementary terms that you can.

Thank you in advance for your assistance

[jzwp11]

Welcome to the forum!

Following normalization rules, if you have 30 events related to client, those events should be RECORDS in a table not fields within a record since they describe a one-to-many relationship (one client-to-many events).

Further, if you go through the same 30 events for multiple clients, that describes another one-to-many relationship (one event can apply to many client). To represent this in tables requires 3 tables as follows

tblClients
-pkClientID primary key autonumber
-txtFirstName
-txtLastName

tblEvents (30 records in this table)
-pkEventID primary key, autonumber
-txtEventName

tblClientEvents (30 records per client in this table)
-pkClientEventID primary key, autonumber
-fkClientID foreign key to tblClients
-fkEventID foreign key to tblEvents
-dteClientEvent (date of the event)

[viper210]

Thank you so much for your response, I feel that I am finally getting somewhere with a resolution.

I will like to clarify what is already contained in my main table Called “Intake”:

1. “ClientID” is an Auto number and already the Primary Key
2. First Name and Last Name are fields in this table
3. All 30 Date fields are also in the table

My question is should I still create these 3 unique tables and go ahead with your original recommendation or modify your process?

Secondly, after creating these tables can you elaborate on how to create the Start Date / End Date criteria to generate the report?

[jzwp11]

My question is should I still create these 3 unique tables and go ahead with your original recommendation ...?

Setting up the table structure correctly is critical to any successful relational database application, so YES, please set up the 3 tables.

Since your Intake table has the 30 date fields, you will need to move any data (if any has been entered to this point) out of there at some point, so I would leave that table alone and create the ones I suggested. You will need to create some append queries to move data from the Intake table into the normalized tables I suggested. Once the data move is completed & the data verified, then the Intake table can be deleted.

Some general recommendations
1. Do not use spaces or special characters in your table or field names
2. Do not use reserved words as table or field names. (Here is a list)
3. Do not use table level lookup fields, even though Access has this capability. This site explains the problems table level lookups can cause. Lookups are best left for forms.

Secondly, after creating these tables can you elaborate on how to create the Start Date / End Date criteria to generate the report?

There are actually several ways you could do this. In any of those ways you will need a query and you would base your report on that query.

A very basic query where you physically enter the dates of interest directly in the query is shown below.

SELECT txtFirstName, txtLastName
FROM tblClients INNER JOIN tblClientEvents ON tblClient.pkClientID =tblClientEvents.fkClientID
WHERE dteClientEvent BETWEEN #7/23/2012# and #7/27/2012#

Another approach is to use a form to supply the dates & you would reference the form controls in the criteria/WHERE clause of the query

SELECT txtFirstName, txtLastName
FROM tblClients INNER JOIN tblClientEvents ON tblClient.pkClientID =tblClientEvents.fkClientID
WHERE dteClientEvent BETWEEN forms!formname!control1name AND forms!formname!control2name

A third approch is to base the report on the query with no criteria (no WHERE clause). Using an unbound form with two date controls and a button, filter the report by the dates entered as the report opens. This would utilize the docmd in code.

SELECT txtFirstName, txtLastName
FROM tblClients INNER JOIN tblClientEvents ON tblClient.pkClientID =tblClientEvents.fkClientID

[viper210]

Forgive me for just now responding to your reply. I have been out of the office.

I’m a little confused about the distinction between EventName and ClientEvent because I thought they were one in the same. On my form I have 30 event names (Field Headers) and in their corresponding fields is where I put a date for those events.

Here is an example of some of the 30 event/date fields:

[jzwp11]

The client events table holds the events as they relate to the particular client as records. So client 1 would have 1 record in the table with an foreign key value that relates to the event called hearing and the associated date, and then another record with a foreign key value that relates to the adjoun event and its associated date and so on.

I've attached a sample database. Open the form named frmClientEvents and see what the data looks like and then go into the tblClientEvents to see what is actually stored.

[viper210]

Thank you for this extremely helpful information. I am getting close to completing the modifications to my forms and tables but ran into this problem:

Your frmClientEvent form lists the date client events and the events vertically.

As you can see from the example listed below that I have to track identical dates/events in multiple courts, and due to space constraints on the screen, it is more efficient to display the date fields horizontally.





I had planned to identify the Events in the different courts as: BFile, BHearing, BAdjourn for Bankruptcy Court, DFile, DHearing, DAdjourn, for District Court, and etc…

My question is am I on the right track, and what further modifications do I need to make to your procedures to make this work?

[jzwp11]

From the looks of it, you have different fields for different courts. Again, following normalization rules, the courts should be records in a table. So before you work on forms, you need to analyze your current tables and see if you they follow normalization rules. If they do not, the table structure must be fixed first. Here are some video tutorial sites that discuss normalization and a few other topics that you might find helpful. (Thanks to forum member "orange" for the list of sites)

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

[viper210]

First of all, I would like to thank both you and Orange for the Video Links you two provided. I reviewed them several times and finally have a good understanding of database structure and normalization.


I had a couple of questions that I needed clarification/verification before I can move forward:

In reply #2:

You had me build these 3 tables:

tblClients
-pkClientID primary key autonumber
-txtFirstName
-txtLastName

tblEvents (30 records in this table)
-pkEventID primary key, autonumber
-txtEventName

tblClientEvents (30 records per client in this table)
-pkClientEventID primary key, autonumber
-fkClientID foreign key to tblClients
-fkEventID foreign key to tblEvents
-dteClientEvent (date of the event)

I’m confused about the primary key / foreign key setup. How can you set foreign key ClientID to tblClients when they are in the same table and the same for EventID to tblClients. Or am I interpreting your instructions incorrectly.

[jzwp11]

The ClientID in tblClients is the primary key; the ClientID field in tblClientEvents is the foreign key (that is why I use the pk (primary key) & fk (foreign key) prefixes to distinguish them). An example might help, let's say that you have a client in the tblClients that has a primary key value of 5. Since it is the primary key, there will only be 1 record in tblClients that has a primary key value=5. All events (in tblClientEvents) related to that client will have a value of 5 in the fkClientID field of tblClientEvents. It is the pk-->fk link that forms the relationship between the client and the their particular events (one(client)-to-many(events) relationship). Please let me know if that does not help.

[viper210]

Hello again, I apollogize for my Access ignorance. I have followed your instructions explicitly but still having problems setting the primary/foriegn key. here is a image of the "Relationship Map" and the tblClientEvents table and maybe you can tell me where I am going wrong.

[jzwp11]

You have some serious structural issues with your database. Regarding the clientID and the varlous courts, if the client has a case in a court, the foreign key in the client table should be in reference to the court not as you show it where you have the clientID in the court table. The court table should only describe the court. Now for your bigger issues. You have many different types (probate, bankruptcy etc.) of courts but you also have several jurisdictions (federal, state, county, etc.). There is a whole hierachical structure to the court system, but I don't know if you need to capture that entire structure within your database.

tblJurisdictions (records for federal, state, county etc.)
-pkJurisID primary key, autonumber
-txtJurisdiction


tblCourtTypes (holds records for probate, bankcruptcy, circuit, district, supreme--just generic names)
-pkCourtTypeID primary key, autonumber
-txtCourtType


Now associate the jurisdiction with the court type

tblCourts
-pkCourtID primary key, autonumber
-fkJurisID foreign key to tblJurisdictions
-fkCourtTypeID foreign key to tblCourtTypes
-txtCourtName

Now if you really need to incorporate the full structure of the court system, that will require some work on organizing the various actual legal location entities: states (OH, MI, KY etc, counties within the states, cities within the counties, townships etc. You will have to let me know if you need that level of detail or just the simple structure I propose above.

I will proceed as if you do not need that level of detail

Now the clients, the tblClient should only contain basic information about the client; there should be no case information in the client table.

tblClient
-pkClientID primary key, autonumber
-txtFName
-txtLName


I will assume that a client can have many cases (one-to-many relationship), so we need a table to hold the cases and we need to relate those cases back to the client.


tblClientCases
-pkClientCasesID primary key, autonumber
-fkClientID foreign key to tblClients
-txtCaseNumber


Now one question that comes to mind is that can several clients be associated with 1 case? If that is indeed true, the above structure will need to be modified. You will have to provide more info on that.


Is the client case always associated with only 1 court/court type/state combination or can it be many? I am guessing that it could be many if a particular case is appealed to a higher court. If my assumption is correct then we have yet another one-to-many relationship (a case can be heard in many courts). Also a court can hear many cases so yet another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities that is a many-to-many relationship which requires a junction table

tblCourtCases
-pkCourtCasesID primary key, autonumber
-fkCourtID foreign key to tblCourts
-fkClientCaseID foreign key to tblClientCases


The current structure assmes that the case number stays the same as it is appealed through the higher courts, if that is not the case and the case number changes with each court it is associated with then the case number field would go in tblCourtCases.

I assume that the events we have been discussing are actually associated with the court cases is that true? If so, the events should not be associated with the client directly as you show in your current structure.