Re: Conflicting Record Problems

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 news:B57FF1A0-1213-4D1C-8AA5-BF27A3AF7D59@xxxxxxxxxxxxx,
NoviceIan typed:
> 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


.

Relevant Pages

  • Re: IS THERE A FORMULA FOR THIS PROBLEM ?
    ... MICHAEL KENT wrote:> I am trying to summarise data for a labour organogram for the first> time. ... The data briefly comprises of a list of staff names, the> category of staff and a couple of ... > columns in which to enter the number of days of sick in one column> and the number of days off on holiday in the other. ... Adding the total> number of holiday days by staff category is easy enough by using> SUMIF formulas. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: A formular to look up data and return dates
    ... Copy these formulas down for the other staff members. ... Blank cells and any cells containing anything other the H or S are ... taken as sick or Holiday. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Conflicting Record Problems
    ... Reason A/L, Sick Leave, Special, ... As I mentioned we have an index to prevent staff leaving twice on the same ... "Nick Coe " wrote: ... > Need more specific info on your table design to be able to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Conflicting Record Problems
    ... "Nick Coe " wrote: ... > thinking about criteria and table aliases. ... > Open a query design grid, drop in the table you want TWICE. ... >> staff leave. ...
    (microsoft.public.access.modulesdaovba)
  • CVP Announces 2006 staff
    ... the start of our 2006 Design and Instructional Team. ... Our most current addition to the design staff is J.C. Caceres. ... Auditions are this coming Sunday, Sept. 25 from 10am-5pm at East Wake ...
    (rec.arts.marching.colorguard)