Conflicting Record Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

We have a staff database which we use to try and keep track of staff leave.
Recently however I've noticed that staff have been booked in for two
different reasons at the same time. For example annual leave from the
10th-17th and sick leave from the 12th-14.

We have an index which prevents staff having duplicates for the same leave
day however we've just realised there is no security to prevent the above
problem happening.

How would I prevent these 'problems' and how will I detect the ones already
in the system?

Ian
 
Depending upon how you've designed your tables you could use
a Select query to show the records where Holiday and Sick
are both Not Null. Or some variation thereof.

Exactly how you do it depends, as I said , on your table
design i.e. what fields you use, how you distinguish between
Holiday or Sick and so on.

I suspect you probably have a start date and an end date for
the leave period and then a field to show what type of leave
it is and that's how people are managing to enter
overlapping periods. If this is the case then start
thinking about criteria and table aliases.

Open a query design grid, drop in the table you want TWICE.
The second instance of the table is known as an alias.

Now, assuming the table design above, drag the start date
and end date from BOTH of the tables down into the grid.
Make sure the tables are not joined.
In the criteria for the start date of tbl2 put
=>tbl1.startdate and run it to see if it works. Then add a
criteria for tbl2.enddate of <=tbl1.enddate.

I'm making this up as I go along <g> so you'll likely need
to mess around with it...

HTH
--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In NoviceIan typed:
 
I'm not getting anywhere I keep getting Invalid Syntax message.
How would I prevent the problem in future?
 
Hi,

The table in question is called absence:

Absence #
Staff #
Reason (Text Field with value list combo box) A/L, Sick Leave, Special,
Study etc
Leave Date
Return Date
Hours Missed

As I mentioned we have an index to prevent staff leaving twice on the same
day however it is possible for them to return twice on a particular day.
This is the only prevention we have.

Ian
 
Back
Top