Finding Duplicate Records In two catagories!

[mattlight]

Hi guys,

I'm back to plague the forum with more connundorums than the prophecies of Revelation!

Imagine an APPOINTMENT SYSTEM for doctors which allocated appointments for certain dates in certain treatment rooms (1-4)

What I want to do is ensure that there is no double booking for either the doctor or the treatment room. At the moment, it can be done visually but you can never depend on human analysis to spot when either a room or doctor is being double booked! I want a CONFIRMATION script that flags up a message saying "It's ALL good" or "uh uhhhhhhhh no way howzay - Double booked Room" or "uh uhhhhhhh no way can the doctor do two things at once"

Now, the appointments are all individual records viewed by the receptionist in a couple of portals. They are both based on the Date of Appointment and then one looks up a doctor's name, the other looks up all the rooms. Combined fields (ApptDate & "-" & DoctorName) the other (ApptDate & "-" & RoomNmb)

The appointment duration could be either 30,60,90 or 120 minutes each.

What I want to do is look for duplicates based upon appointment time and duration of appointment.

I have been able to graphically display the appointment time on a time grid which extends by a grid if duration one hour etc, using clour blocks.

To recap

I want to check room availability at certain times based on both appointment time and other time slots filled because of duration and for it to also check doctors based upon same as above.

I'm sure there's a standard script which hopefully someone might post and I can ammend it to look at more than one catagory.

Thanks for your help in waiting, speak soon

[danjacoby]

Off the top of my head:

Each record is a separate day.

Fields (x4, one set for each room) for each 30-minute time slot, which get checked off as that slot for that room gets booked. Plus one field for the date (which must be unique and not empty).

Global fields to create new entries.

Script to check the globals against the entire file for duplicates.

-----------------

The rest is just a lot of tedious work.

It'll work -- I don't guarantee that it's elegant.

[keshalyi]

If I understand you correctly, here may be a simpler way...

Now you want to verify basically that there are no duplicate day and time combos for any room, and for any doctor, non?

So make two calc fields, doctor-time-date and room-time-date. Make the first

<doctor>|<time>|<date>

the second

<room>|<time>|<date>

Then, just verify that neither of these fields is duplicated before you allow them to commit the recor

[mattlight]

Well, no - each appointment is seperate! Because it has load of info required from examinations and stuff - tried to do the one day thing even tried a record per time slot - no good. The way I have it works except I need to do a find of duplicates.

Any ideas - thanks.

Me