Requery causing duplicate table entry

[MLEBL]

I'm creating a scheduling application for a hospital department that indicates when they've exceeded the minimum required staff for a particular shift. I have a table keeping track of the count and another table that contains the miniumum values based on specific shifts and roles. For example, I know the exact number of staff needed at 2 AM, and I have a staff member working from 11PM through 7AM so I need to indicate that this person will fulfill one of the required slots at 2AM. I also need to account for staff carrying over into a different day, as indicated in this example, so I have to create counts for multiple days (as in day before scheduled date or day after scheduled date). I need to provide this information for multiple days. When I created the information for the initial day, I started getting the non-descript "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience." I embedded a requery command in the code and it all functions correctly for the first day. When I try and enter information on subsequent days, I get an error that the requery command is creating a duplicate entry in the database. Since this code finds current entries for a role and a shift then adds one to the count, I don't understand how it could be creating duplicate values. Here's the vb code for the module:

Option Compare Database
'OpenArgs:=Me.ScheduleDate & "|" & Me.StaffRole & "|" & Me.StaffShift
Private Sub Form_Load()
DoCmd.Requery
Dim TempRole As Integer
Dim strSQL As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim varSplit As Variant
Dim FinalLoop, LoopCount, TempAMG, TempARN, TempCAR, TempCCA, TempCRN, TempEDU, TempEMT, TempIT, TempLPN, TempMGR, TempPRR, TempRN, TempUC As Integer
Dim Starting As String
Dim OriginalDate As Date
Dim tsd As Date
Dim OriginalDatePlusOne As Date
If Me.OpenArgs <> vbNullString Then
    varSplit = Split(Me.OpenArgs, "|")
    OriginalDate = varSplit(0)
    OriginalDatePlusOne = DateAdd("D", 1, varSplit(0))
    TempRole = varSplit(1)
End If
'if schedule date exists
If DCount("[ScheduleDate]", "CoreScheduleCountOfPosition", "[ScheduleDate] = #" & OriginalDate & "#") Then
    Me.ScheduleDate = varSplit(0)
    tsd = DateAdd("d", 1, OriginalDate)
 
'if schedule date + 1 exists
    If DCount("[ScheduleDate]", "CoreScheduleCountOfPosition", "[ScheduleDate] = #" & tsd & "#") Then
        FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
        Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
        DoCmd.Requery
        Me.Recordset.FindFirst "[ScheduleDate] = #" & OriginalDate & "# and [TimeSlot] = '" & Starting & "'"
'    End If
'update loop
        For LoopCount = 1 To FinalLoop
            Call Initialize
            If TempRole = 1 Then
                Me.ManagerCount = TempMGR + 1
            End If
            If TempRole = 2 Then
                Me.AsstManagerCount = TempAMG + 1
            End If
            If TempRole = 3 Then
                Me.EducCount = TempEDU + 1
            End If
            If TempRole = 4 Then
                Me.ITCount = TempIT + 1
            End If
            If TempRole = 5 Then
                Me.PRRCount = TempPRR + 1
            End If
            If TempRole = 6 Then
                Me.CARCount = TempCAR + 1
            End If
            If TempRole = 7 Then
                Me.ARNCount = TempARN + 1
            End If
            If TempRole = 8 Then
                Me.CRNCount = TempARN + 1
            End If
            If TempRole = 9 Then
                Me.RNCount = TempRN + 1
            End If
            If TempRole = 10 Then
                Me.LPNCount = TempLPN + 1
            End If
            If TempRole = 11 Then
                Me.EMTCount = TempEMT + 1
            End If
            If TempRole = 12 Then
                Me.UCCount = TempUC + 1
            End If
            If TempRole = 13 Then
                Me.CCACount = TempCCA + 1
            End If
            Me.CSCStatus = "W"
            If LoopCount <= FinalLoop Then
                DoCmd.GoToRecord , , acNext
            End If
        Next LoopCount
        Else:
 
'create and update second set if it doesn't exist
            Dim tc As Integer
            For k = 1 To 24
                    If k >= 1 And k <= 10 Then
                        Me.TimeSlot = "0" & k - 1 & ":00"
                    Else:
                        Me.TimeSlot = k - 1 & ":00"
                    End If
                Me.ScheduleDate = tsd
                Call SetToZero
                Me.CSCStatus = "W"
                If k <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next k
        FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
        Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
        Me.Recordset.FindFirst "[ScheduleDate] = #" & varSplit(0) & "# and [TimeSlot] = '" & Starting & "'"
        For LoopCount = 1 To FinalLoop
            Call Initialize
            If TempRole = 1 Then
                Me.ManagerCount = TempMGR + 1
            End If
            If TempRole = 2 Then
                Me.AsstManagerCount = TempAMG + 1
            End If
            If TempRole = 3 Then
                Me.EducCount = TempEDU + 1
            End If
            If TempRole = 4 Then
                Me.ITCount = TempIT + 1
            End If
            If TempRole = 5 Then
                Me.PRRCount = TempPRR + 1
            End If
            If TempRole = 6 Then
                Me.CARCount = TempCAR + 1
            End If
            If TempRole = 7 Then
                Me.ARNCount = TempARN + 1
            End If
            If TempRole = 8 Then
                Me.CRNCount = TempARN + 1
            End If
            If TempRole = 9 Then
                Me.RNCount = TempRN + 1
            End If
            If TempRole = 10 Then
                Me.LPNCount = TempLPN + 1
            End If
            If TempRole = 11 Then
                Me.EMTCount = TempEMT + 1
            End If
            If TempRole = 12 Then
                Me.UCCount = TempUC + 1
            End If
            If TempRole = 13 Then
                Me.CCACount = TempCCA + 1
            End If
            Me.CSCStatus = "W"
            If LoopCount <= FinalLoop Then
                DoCmd.GoToRecord , , acNext
            End If
        Next LoopCount
        End If
    Else:
 
'create if second exists but first doesn't
    DoCmd.GoToRecord , , acNewRec
        If DCount("[ScheduleDate]", "CoreScheduleCountOfPosition", "[ScheduleDate] = #" & OriginalDatePlusOne & "#") Then
           For i = 1 To 24
                Me.ScheduleDate = OriginalDate
                If i >= 1 And i <= 10 Then
                    Me.TimeSlot = "0" & (i - 1) & ":00"
                Else:
                    Me.TimeSlot = (i - 1) & ":00"
                End If
                Call SetToZero
                Me.CSCStatus = "W"
                If i <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next i
            FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
            Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
            Me.Recordset.FindFirst "[ScheduleDate] = #" & varSplit(0) & "# and [TimeSlot] = '" & Starting & "'"
            For LoopCount = 1 To FinalLoop
                Call Initialize
                If TempRole = 1 Then
                    Me.ManagerCount = TempMGR + 1
                End If
                If TempRole = 2 Then
                    Me.AsstManagerCount = TempAMG + 1
                End If
                If TempRole = 3 Then
                    Me.EducCount = TempEDU + 1
                End If
                If TempRole = 4 Then
                    Me.ITCount = TempIT + 1
                End If
                If TempRole = 5 Then
                    Me.PRRCount = TempPRR + 1
                End If
                If TempRole = 6 Then
                    Me.CARCount = TempCAR + 1
                End If
                If TempRole = 7 Then
                    Me.ARNCount = TempARN + 1
                End If
                If TempRole = 8 Then
                    Me.CRNCount = TempARN + 1
                End If
                If TempRole = 9 Then
                    Me.RNCount = TempRN + 1
                End If
                If TempRole = 10 Then
                    Me.LPNCount = TempLPN + 1
                End If
                If TempRole = 11 Then
                    Me.EMTCount = TempEMT + 1
                End If
                If TempRole = 12 Then
                    Me.UCCount = TempUC + 1
                End If
                If TempRole = 13 Then
                    Me.CCACount = TempCCA + 1
                End If
                Me.CSCStatus = "W"
                If LoopCount <= FinalLoop Then
                    If Me.TimeSlot = "23:00" Then
                        Me.Recordset.FindFirst "[ScheduleDate] = #" & OriginalDatePlusOne & "# and [TimeSlot] = '00:00'"
                    Else:
                        DoCmd.GoToRecord , , acNext
                    End If
                End If
            Next LoopCount
        Else:
 
'create both sets if neither exists
            For i = 1 To 24
                Me.ScheduleDate = OriginalDate
                If i >= 1 And i <= 10 Then
                    Me.TimeSlot = "0" & (i - 1) & ":00"
                Else:
                    Me.TimeSlot = (i - 1) & ":00"
                End If
                Call SetToZero
                If i <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next i
            For i = 1 To 24
                Me.ScheduleDate = OriginalDatePlusOne
                If i >= 1 And i <= 10 Then
                    Me.TimeSlot = "0" & (i - 1) & ":00"
                Else:
                    Me.TimeSlot = (i - 1) & ":00"
                End If
                Call SetToZero
                Me.CSCStatus = "W"
                If i <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next i
 
            FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
            Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
            Me.Recordset.FindFirst "[ScheduleDate] = #" & varSplit(0) & "# and [TimeSlot] = '" & Starting & "'"
            For LoopCount = 1 To FinalLoop
                Call Initialize
                If TempRole = 1 Then
                    Me.ManagerCount = TempMGR + 1
                End If
                If TempRole = 2 Then
                    Me.AsstManagerCount = TempAMG + 1
                End If
                If TempRole = 3 Then
                    Me.EducCount = TempEDU + 1
                End If
                If TempRole = 4 Then
                    Me.ITCount = TempIT + 1
                End If
                If TempRole = 5 Then
                    Me.PRRCount = TempPRR + 1
                End If
                If TempRole = 6 Then
                    Me.CARCount = TempCAR + 1
                End If
                If TempRole = 7 Then
                    Me.ARNCount = TempARN + 1
                End If
                If TempRole = 8 Then
                    Me.CRNCount = TempARN + 1
                End If
                If TempRole = 9 Then
                    Me.RNCount = TempRN + 1
                End If
                If TempRole = 10 Then
                    Me.LPNCount = TempLPN + 1
                End If
                If TempRole = 11 Then
                    Me.EMTCount = TempEMT + 1
                End If
                If TempRole = 12 Then
                    Me.UCCount = TempUC + 1
                End If
                If TempRole = 13 Then
                    Me.CCACount = TempCCA + 1
                End If
                Me.CSCStatus = "W"
                If LoopCount <= FinalLoop Then
                    DoCmd.GoToRecord , , acNext
                End If
            Next LoopCount
        End If
    End If
'End If
DoCmd.Requery
DoCmd.Close acForm, "BuildScheduleCount2"
End Sub
 
Public Sub Initialize()
            TempAMG = Me.AsstManagerCount
            TempARN = Me.ARNCount
            TempCAR = Me.CARCount
            TempCCA = Me.CCACount
            TempCRN = Me.CRNCount
            TempEDU = Me.EducCount
            TempEMT = Me.EMTCount
            TempIT = Me.ITCount
            TempLPN = Me.LPNCount
            TempMGR = Me.ManagerCount
            TempPRR = Me.PRRCount
            TempRN = Me.RNCount
            TempUC = Me.UCCount
End Sub
 
Public Sub SetToZero()
                Me.ManagerCount = 0
                Me.AsstManagerCount = 0
                Me.EducCount = 0
                Me.ITCount = 0
                Me.PRRCount = 0
                Me.CARCount = 0
                Me.ARNCount = 0
                Me.CRNCount = 0
                Me.RNCount = 0
                Me.LPNCount = 0
                Me.EMTCount = 0
                Me.UCCount = 0
                Me.CCACount = 0
End Sub
 

Any assistance with this would be greatly appreciated!

Thanks

[zmbd]

While I take a second to digest your code please do the following steps:

[Z{Edit} What version of Access are you using? From line 11 it appears that you are using one of the older versions as in 2003 the single line "dim (muli varible) as type" was discouraged and in 2007/2010 will cause you issues. You should go back and change anything looking like:
Dim FinalLoop, LoopCount, TempAMG As Integer
to
Dim FinalLoop As Integer, LoopCount As Integer, TempAMG As Integer
This way when you upgrade you wont run into the typecasting issue.{/edit}]


Please go to your vba editor in MS Access
Then go to the Ribbon/toolbar:> Tools :> Options
Editor tab:
Uncheck "Auto Syntax Check" all this does is add an annoying pop-up that says... you goofed... click ok... the line will already turn red when you mis-form the code.

Check Mark "Require Variable Declaration" (this will place the option explicate at the top of every new code module)
checkmark all of the remaining options.

I like an auto indent of 4; however, I wouldn't go less than 3 or more than 5 just as a personal preference.
The remaining stuff can be left as default.

Now go thru all of your form modules, standard modules, and class modules and make sure that the following two lines are the VERY first two lines in every one of these modules

Option Compare Database 
Option Explicit 

Now compile your project.
Fix any errors
Re-compile your project
Repeat the above three lines until you can compile the project without an error

If you do find and correct any errors, we'll need to see the new code.

[Rabbit]

This is only from a cursory glance.

1) On line 95, 151, 163, 231, 244 and you are creating new records. That's your most likely cause of duplicates.

2) Your database design looks unnormalized.

3) You are probably making this much more complicated than it needs to be with all that code. If you have all the necessary data in the tables, all you need is a single query to check whether or not a time frame is covered.

[zmbd]

Please list the relavent tables as follows:

Tablename
[fieldname] (type) (key/index) (relationships)

Example:
tbl_A
[A_pk] (autonumber)(PrimaryKey)
[A_fk_B] (long)(foriegn key 1 to Many with tbl_b)(required, no nulls)
[A_field1] (text(25))

tbl_B
[B_pk] (autonumber)(PrimaryKey)(B_1:m_with tbl_a)
[B_fk_C] (long)(foriegn key 1 to Many with tbl_C)(required, no nulls)
[B_field1] (text(25)) Required, no nulls, indexed no duplicates
[B_field2] (long)

Example data would help... just a few records

If needed for privacy
Names can be changed to "John Doe" and "Jane Doe" etc...
Phone numbers to [phone], [phone] etc...
Same thing with SSN

I don't need every table, just the ones that feed the form and feed each other.

I don't need every record, just an example or three so long as they reflect the overall data.

-
Why I'm asking is that it appears that you have hard coded a lot of your roles and other information and then are trying hand-bash your human resources against these hardcoded slots to get your counts. This is the source of your issues.

Depending on how your tables are setup, you may be able to do this with a crosstab query and may not need any VBA or at least will be able to cut your code to a smaller and more comprehensible set.
-

I realize that once you've put so much effort into doing the project it's somewhat daunting to have feedback that says...
"there's maybe a better way." Please don't take this as personal comment on your abilities - but as one peer to another in the spirit of a helping hand.

I would highly recommend that you go ahead and read thru the following two links... I have an suspicion that you'll find them useful:
A Tutorial for Access
Database Normalization and Table Structures.

[MLEBL]

Rabbit Responses:
1) the process works fine when I'm modifying the intial date, it's only when I move over to a subsequent date that I get the duplicate error.
2) in what sense? I have created several tables to minimize the amount of data that will be stored in the scheduling table (so instead of name, role, shift, ... being stored each time someone is scheduled, it only has to store the appropriate mapped key integer).
3) UNDERSTATEMENT! This thing is SOOO complex. I suppose I could create an update query that replaces the logic for incrementing the values in the count to simplify this section.

BTW, thanks for the help back in November . . . I warned you I'd be back!

[MLEBL]

zmbd response:
I tried making the changes you suggested and get the same errors. I'm running 2010 (code identified was probably a format I utilized from reviewing old Access suggestions). The only problem is that I started running into problems in my public sub module for Initialize saying that it couldn't find the variables. I've embedded the Dim statement into the sub module to get this working. It still works fine on the first day of scheduling but gives me the duplicate error on subsequent days. I've confirmed that the values I'm pushing are the appropriate values for the date I'm updating. Based on the point in the vb code where I get the error, it's identifying the date/role/shift as existing so it shouldn't be creating a new value. Would you be open to a participating in a webex so you can see this monster first hand. I believe that I've created my tables appropriately based on the functionality that I'm trying to create.

[zmbd]

Rabbit, looks like I cross posted with you!

I just found those same lines... I had to step out after my last post asking for the tables.... hydrogen leak inside one of my instruments. A very bad thing... easy fix... just bad thing.

MLEBL, you did warn us. :)
Tables please.
You shouldn't need any maketable or other things... I think once we see the DB structure the solution will present itself quite nicely. I have faith in Rabbit My SQL master, or perhaps this padawan will be able to find the solution. :)

Sorry, at work pc... webex is barred.

[Rabbit]

1) Again, it was only a cursory glance. But the fact that you are creating records each time it is run is probably the cause of the errors. So that's where you need to start investigating. You need to look at how it's creating those records and what it's putting into those records so that can figure out where it's causing the duplication.

2) You haven't posted your table structure so I can only make inferences about the design. The reason it looks like unnormalized is because you have fields like this: ManagerCount, ITCount, CARCount, etc. On the face of it, having multiple fields for different counts is unnormalized.

3) I don't have the time right now to get into this point but I will come back to it later today. I really do think you are overcomplicating the solution.

[MLEBL]

Here's the breakdown of the relevent tables:

Table CoreSchedule
DateTime Date/Time
ScheduleDate Date/Time Primary Key
StaffMember Number Primary Key (1:1 staff table)
StaffShift Number Primary Key (1:1 ListOfShifts table)
StaffRole Number lookup in ListOfPostiion table
Status Text Primary Key ("W"-working, "F"-final)
StaffShiftStart Date/Time
StaffShiftEnd Date/Time

Table CoreScheduleCountOfPosition
ScheduleDate Date/Time Primary Key
TimeSlot Text Primary Key
ManagerCount Number
AsstManagerCount Number
EducCount Number
ITCount Number
PRRCount Number
CARCount Number
ARNCount Number
CRNCount Number
RNCount Number
LPNCount Number
EMTCount Number
UCCount Number
CCACount Number
CSCStatus Text

Table ListOfPosition
ID AutoNumber Primary Key
Position Text

Table ListOfShifts
ID AutoNumber Primary Key
ShiftDescription Text
StartTime Text
EndTime Text
Duration Number

Table Staff
ID AutoNumber Primary Key
StaffName Text
Role Number Lookup in ListOfStaffRole
PreferredArea Number Lookup in ListOfAreas
PrimaryContactNumber Text
FTEStatus Number Lookup in ListOfFTE
PreferredShift Number Lookup in ListOfPreferredShift
PreferredHours Number Lookup in ListOfPreferredHours
HireDate Date/Time
DeployDate Date/Time
Agency Yes/No
Traveler Yes/No
ActiveStatus Yes/No
DistantEmployee Yes/No
Wave Number Lookup in Wave

[Rabbit]

I still don't have the time right now to go into the SQL solution but a quick note on the design. Most of it looks normalized except for CoreScheduleCountOfPosition.

The structure should be:
ScheduleDate Date/Time
TimeSlot Text
Position Text
StaffNeeded Number

The reason you would want to normalize down to this level is for future changes. If you add a new position to your old design, you would have to change the design of the table and update all your queries, reports, and forms. Whereas the normalized design only requires adding a row into that table. Everything else will handle itself.

When I get more time, I will go into the SQL solution.

[NeoPa]

Looking through the other comments briefly, it seems they cover the same sort of comments I am thinking of. You should not be considering storing half the information you are storing, but query it from the existing data. The db design is the most important aspect of any project and Normalisation should be the basis of any design. The more complex the design, the more important the Normalisation rules are. There is already a link to the article that I would like to draw your attention to. Please read it. Without that understanding it will be very difficult to make any progress on this project at all.

One point I would raise though, is that finding overlapping dates (or timestamps) is another fundamental of a project such as this. You may find some interesting points on the fundamentals of this in Time Interval Overlapping (MS Access 2003).

[MLEBL]

I started out normalizing to this level, but due to client requirements I couldn't figure out how to incorporate this into the design. The requirement is that the manager creates the required staffing numbers for each hour and each position in a spreadsheet design via this Access tool. The count table refers back to the requirements table to make sure that all appropriate positions are filled. This whole thing is easier to show than to explain, so maybe when you get some time I can establish a webex to show you how this works. I only have test staff in the table currently (Micky Mouse . . . etc), so it wouldn't be a problem showing you the database and contents.

[NeoPa]

I mean no disrespect, but if you're still thinking in terms of continuing with what you have already, I see no reason to believe that you could explain your situation clearly and succinctly even with a webex session, which would involve a level of time commitment on one of our parts. I see no signs of you having a clear enough appreciation of your own situation even, let alone the clarity to explain to people who all know better anyway (and have been trying to impart to you the results of their experience). It's possible I'm wrong, I suppose, but I'm not planning to spend a fair bit of time proving something I'm already reasonably certain of, to someone who isn't happy to accept advice after they've asked for it. Even when three separate and experienced helpers have all come on here and replied with the same basic advice.

I sympathise with you for the hole you've got yourself into, but until you stop digging there's little we can say to help anyway.

[MLEBL]

You may not have meant it, but it comes off as completely disrespectful. I've indicated that I initially started down the road indicated by all three experts only to have been derailed by the client's requests. My lack of experience with Access is probably the root of the issue, but I still don't see where you've provided direction to assist (only pointing to online articles on database normality). If you read Rabbit's post, all of my tables appear to be normailzed with the exception of the CoreScheduleCountOfPosition which I explained has to refer back to a detailed list of expected staffing counts created by the client. I suppose I'll have to continue muddling through this on my own.

[zmbd]

MLEBL:
whew... take a deep breath.
Give Rabbit and myself a chance to take a look here too.

We all understand that occasionally the end-user doesn't have the greatest idea or has a perceived need that we have to work with/around or teach them that there is a better way to do the task. Hopefully once we’ve hit things with a hammer or two, you’ll have a tool to work with to train the user.

I’ll also make note that you did not tell us until post #12 that table CoreScheduleCountOfPosition is a workaround for a customer requirement. It would have been most helpful to know that when you posted the table outline. We can only work with what you give us. Thus, I surmise Neopa’s frustration with the apparent lack of interest on your part in what we’ve suggested so far.

Now. If you look at when I posted the links all you had posted was code; that in itself has a few issues, and even then I could tell that there was some issue with the design of the database; however, without the tables I couldn’t even begin to help you unless I tried to build a database myself to solve your project. Something I just don’t have the time today to do.

Going back to the your tables that I asked for to confirm what I had suspected in that your database was not truly normalized. In that Rabbit found even one table that is not normalized, then by definition, makes the entire database non-normalized. HOWEVER, Master-Rabbit stated that there might be a SQL solution to your quandary; thus, all is not lost.

So, once again, please take a deep breath and let’s continue to work thru this.

As you can already see, providing little details like customer requirements will help us to understand what you’re after; therefor, if there are any more such tidbits you would like to share, now is the time do so.

As for the webex session… as I told you in my earlier post, My IT blocks all such third party services so you’re out of luck on that part from me. They even block skype and magic jack!

Anyway back to the lab to put out another issue. Shame I had to spend the time trying to calm people down here and in lab instead of doing the fun stuff in either place. Sigh
v:-(

[NeoPa]

I've tried to say it how it is. There is no intent to show disrespect, however, I'd be dishonest if I failed to say that I believe you are coming at this the wrong way, and your approach to the advice given, by all three experts, is unresponsive. Your post #12 seems to indicate that you believe, contrary to what you've been told by those that you have asked for help, that your way makes better sense. In such circumstances, it certainly doesn't make good sense to me to spend time sharing a webex session with you. I don't believe you have anything to teach me, from what I've read so far, and you seem quite unwilling to listen to what we say. I believe that, to make progress, you must at least start from an understanding that your first, and most important, task is to get the design for the project normalised. I've tried to express this position, as I don't believe anyone benefits from beating around the bush. In life, and especially in technical discussions, the truth is of the utmost importance to express. If people's feelings are hurt by the truth then I rarely believe the problem is with the truth. Please feel free to ignore what I post if that suits you better. I will continue to express the truth to the best of my understanding and ability.

PS. Clearly this is my personal view and, though I am a moderator, is not necessarily the view of the site on this occasion, nor of other experts. I'm sure Z & Rabbit will be happy to express their positions for themselves.

[MLEBL]

Thanks zmdb!

Here's all of the expectations and requirements:
1) Create an application where all staff members can be identified with their preferred location for work (since there are multiple departments within the primary location), preferred shift (morning, evening, night, or rotating), preferred hours (4, 8, or 12 hour shifts preferred), FTE status (as a decimal), active indicator, contract indicator, traveler indicator (with contract dates), extended travel time indicator (for staff living outside of the metro area), primary position (four identified for this particular department, and if the selected position is "RN", then track compliance dates for several required compliancies; if any other position, then track compliance date for CPR certification).
2) Provide a tool where the core staffing requirements can be identified by position and hour for a 24 hour period to include anticipated customer volume. Report the staff:customer ratio based on the number of customers expected at each hour and total for the day (total staff:customer).
3) Provide a tool where a two-week core schedule can be created in a list layout; list each staff member and allow the staffing manager to select the appropriate shift and role for each staff member. Must provide full two weeks while building to allow the builder to see what is currently scheduled, and it must compare the hourly schedule to the minimum staff requirements for each hour established in requirement #2 . . . and keep versioning as per state requirements.
4) Option: provide visual representation of daily requirements against daily schedule
5) Track compliancies. Build report indicating staff members whose compliancies are expiring within the next 30 days.
6) Build a variety of schedule reports (schedule for one particular staff member, daily schedule with all staff members, schedule by week (for one, two, four, or six week intervals) built as a table with each row indicating the staff member and each column as the scheduled date with the cell value indicating the shift for that staff member.
7) Build a position control report based on staffing positions approved and filled. For example, if the department is approved for 20 RNs and only 18 are hired, then the report must identify the 18 hired plus two blank lines indicating the shortage.
8) When selecting a staff member within the core schedule or daily schedule to work a shift, it cannot be 8 hours after or 8 hours before a previously scheduled shift.
9) after creating the core schedule, provide the scheduling manager with the ability to push the core schedule out for x number of bi-weekly iterations.

I think there's more, but this is what I'm tasked with completing. I've got almost two months invested currently and have most of the functionality working (or at least portions). I apologize for not providing all of the information up front, I just thought this might be a simple fix as I was just trying to figure out why a requery command would throw a "duplicate entry" error. I'm open to redesigning the entire database, but only if it will provide a fix to my issue and still meet the functionality requirements.

Thanks again, and I'm sorry for being such a pain!

[MLEBL]

Neopa, thanks for your honesty. I appreciate the feedback, but I'll look to Z or Rabbit for direction.

[MLEBL]

Thanks for the info! I looked through the templates and found nice examples of what I'd like my reports to look like (from the Excel samples), but there's only one Access entry from the grouping you suggested, and when I try to download it says "Template 'TimeCard.accdt' could not be instantiated. The Table 'Work Codes' could not be read from the template file. The object may be in an unrecognized format or contain invalid data."

I did find a resource scheduling database from 2003 that may contain some helpful information, so I'll give it a try to see if I can scavenge something from it. Have fun with in the lab and try not to blow up or burn down anything! Happy holidays!

[Rabbit]

Just a few notes about the normalization before I get into the SQL.

While I said the count table was unnormalized. I also said it "looks like", the implication being that I did not have a lot of time to look at it. Also, since I don't actually know the purpose of all the fields, I can't make any concise statements about the rest of the design.

For example, the list of shift table has a field named Duration. It looks like a field whose value would be derived from the other fields in the table. Therefore, storing that value also breaks normalization.

Now for the SQL. This will be a simplified example of what you will eventually need. But it shows the crux of the solution. We can work towards building in the different complexities present in your design and requirements but this is the basis.

If I understand your requirements correctly, you basically have a table of start and end date times. You need to know if a target date time have enough records that overlap that target date time. The SQL for that is this:

SELECT count(*)
FROM someTable
WHERE #01/01/2012 01:00:00 PM# BETWEEN 
   startDateTime and endDateTime

This simple query gives you a count of rows that overlap the target date time. If you have a table of target date times and grouping fields, then it's a simple matter of joining to that table, replacing the hard coded value with a field, and bringing in your grouping fields.

Unless the requirements are significantly more complex than this scenario, there's no need for all that code. But most any other requirements you need can be built into the SQL.

[NeoPa]

MLEBL:
Neopa, thanks for your honesty. I appreciate the feedback, but I'll look to Z or Rabbit for direction.

I'm perfectly happy with this. An appropriate response in my book.

Rabbit:
Unless the requirements are significantly more complex than this scenario, there's no need for all that code. But most any other requirements you need can be built into the SQL.

This largely expresses my views on the matter. You won't get much better understanding or help on SQL matters than from Rabbit, but I would draw your attention to the idea of overlapping time periods, as discussed in the link from post #11. It is something that many people struggle to deal with properly, yet the fundamental logic of it is not too long and complicated (It's just so counter-intuitive for most people) :
Definition of Terms :
Period 1 is from time A to time B.
Period 2 is from time X to time Y.
Criteria :
(X <= B) AND (Y >= A)

If both parts of this logic statement are determined to be TRUE then there is an overlap. If a touching overlap (where one starts immediately as the other ends) is not to be considered then lose the equals (=s).

[MLEBL]

Maybe I need more direction on this, but I think this would require a rebuild of my table for my staffing model as well as the count table (and several forms, reports, and queries). The staffing model table is built in the same format as the count table for ease of comparison. Would this be correct?

[MLEBL]

I actually found my own solution. I replaced the docmd.requery line with docmd.refreshrecord and it stopped erroring and functions properly. Thanks for everyone's assistance.

[Rabbit]

I'm glad you got it working but I want to caution you against the approach you've taken. It would behoove you to take a little extra time to do things correctly right now rather than taking a lot of extra time later on when changes come down the line.

As far as post #22, the SQL solution does not require changing the design of the staffing table.

[NeoPa]

I'm with Rabbit on this. I haven't determined for myself that no changes would be required (or even the reverse), but whatever changes are required to do it properly, were much better done now than later on when the pain will certainly be greater. That is the nature of such things. Experience teaches us this time and again.