Requery causing duplicate table entry

[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.refreshre cord 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.