Solved: Repeating data in a table

[DomFino]

I have a table with repeating data that is causing problems with my application. I am using Access 2K and I read several posts on this site and others that talk about normilizing the data so there are not repeating fields. However, I just don't get it. Can anyone help me figure out how I should break this table out so that it is normailzed?

The code is on a subform (subfrmTickler) that has a report date (ReportDueDTq1) and a tickler date (TicklerDTq1) for each quarter of the current year. What is supposed to occur is an email is sent to the project manager between the tickler date and the report due date and once that is done the tickler check box is set to yes (TicklerSentq1).

I have managed to figure out how to do this for one quarter but am having a heck of a time figuring out the proper SQL that will permit this to occur each quarter. The two code areas in blue is where I think I need assistance. Not sure. SQL is not my strength so any suggestion on how I could get the code to work for each of the four quarters will be greatly appreciated.


FIELD...................TYPE.............Key

TicklerID................AutoNumber.... PK
GrantID.................Number
TicklerReason..........Memo
TicklerPerson ..........Text
ReportDueDate........Date/Time

ReportDueDTq1........Date/Time
TicklerDTq1.............Date/Time
TicklerSentq1...........Yes/No

ReportDueDTq2........Date/Time
TicklerDTq2.............Date/Time
TicklerSentq2...........Yes/No

ReportDueDTq3........Date/Time
TicklerDTq3.............Date/Time
TicklerSentq3...........Yes/No

ReportDueDTq4.......Date/Time
TicklerDTq4.............Date/Time
TicklerDTq4.............Yes/No

The actual code where I am using this table follows.

Private Sub Form_Load()
'call the routine to check for ticklers that are due
  CheckForTicklers
  
End Sub

Private Sub CheckForTicklers()
'checks to see if the given user is due for a reminder about a particular grant
On Error GoTo ErrorHandler

'set tickler dates and report due dates for current year
Dim yr As Long

yr = Year(Now())
Me.txtYear.Value = Year(Now())

ReportDueDTq1 = "1/15/" & Me.txtYear.Value
ReportDueDTq2 = "4/15/" & Me.txtYear.Value
ReportDueDTq3 = "7/15/" & Me.txtYear.Value
ReportDueDTq4 = "10/15/" & Me.txtYear.Value

TicklerDTq1 = "1/1/" & Me.txtYear.Value
TicklerDTq2 = "4/1/" & Me.txtYear.Value
TicklerDTq3 = "7/1/" & Me.txtYear.Value
TicklerDTq4 = "10/1/" & Me.txtYear.Value

'create a recordset of all unsent ticklers this user has set
Dim rstCurrent As Recordset
Dim strSQL As String
Dim strUserName As String

'get the user name
Dim sUser As String
	'get the login user name
	strUserName = Environ("USERNAME")

	sUser = strUserName

  
strSQL = "SELECT tblTickler.TicklerID, tblTickler.GrantID, tblTickler.TicklerReason, " & _
		  "tblTickler.TicklerPerson, tblTickler.ReportDueDTq4, tblTickler.TicklerSentq4, " & _
		  "tblTickler.TicklerDTq4, tblGrantNameLKU.GrantName, tblGrantIdentifierLKU.GrantIdentifier " & _
		  "FROM (tblGrantIdentifierLKU INNER JOIN (tblGrantNameLKU INNER JOIN tblGrants ON " & _
		  "tblGrantNameLKU.GrantNameID = tblGrants.GrantNameID) ON " & _
		  "tblGrantIdentifierLKU.GrantIdentifierID = tblGrants.GrantIdentifierID) " & _
		  "INNER JOIN tblTickler ON tblGrants.GrantID = tblTickler.GrantID " & _
		  "WHERE (((tblTickler.TicklerPerson)='" & strUserName & "') AND " & _
		  "((tblTickler.TicklerSentq4)=No) AND ((tblTickler.TicklerDTq4) Between " & _
		  "DateAdd('d',-14,Now()) And Now()));"
		  
Set rstCurrent = CurrentDb.OpenRecordset(strSQL)
If rstCurrent.RecordCount = 0 Then 'there are no ticklers pending
   'release the vaiable
	Set rstCurrent = Nothing
	Exit Sub
End If
' otherwise move to the last record to get an accurate record count
rstCurrent.MoveLast
rstCurrent.MoveFirst
Dim strSubject As String
Dim strBody As String

'loop through the recordset
  Dim j As Integer
  For j = 1 To rstCurrent.RecordCount
   strSubject = "Reminder on Grant - " & rstCurrent.Fields("GrantName") & "Grant Identifier - " & rstCurrent.Fields("GrantIdentifier")
  ' strSubject = "Reminder on Grant " & rstCurrent.Fields("GrantID")
   
	strBody = "You set a tickler to remind you about this Grant for the following reason: " & rstCurrent.Fields("TicklerReason")
	
	Call SendMessage(sUser, strSubject, strBody)
	'set the TicklerSent field to yes so this reminder will not be sent again
	rstCurrent.Edit
   rstCurrent.Fields("TicklerSentQ4") = vbYes
	rstCurrent.Update
	rstCurrent.MoveNext
  Next j
  
'release the variable
Set rstCurrent = Nothing

ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub

End Sub

[GP George]

The term "repeating data" refers to the fields in your table which contain the same data--in your case, "ReportDueDTq1", "ReportDueDTq2", etc. The way to solve your problem is not to use massive, complex SQL statements; rather, you need to redesign your table to eliminate the repeating groups.

FIELD...................TYPE.............Key

TicklerID...............AutoNumber.... PK
GrantID................Number
TicklerReason........Memo
TicklerPerson ........Text
ReportQuarter........Number (with values 1,2,3, or 4)
ReportDueDate.......Date/Time
TicklerDueDate.......Date/Time
TicklerSent...........Yes/No

Now, your query can select dates for the current quarter based on the value (1 to 4) in the field ReportQuarter.

HTH

George

[DomFino]

George,

Thanks for your reply and explanation. I finally get it, thanks to you. My goodness that was so simple, yet for me, I just could not get my thinking out of the box. I see what you mean and will modify my table.

Thanks so much for the your time and response.

Dom

[GP George]

You're very welcome.

George