Query to see available employees

[marcellvries]

Hi there,

I designed a planning system which works great. However there are always things to improve.

It looks like this:

qryPlanning
PlanningID LocationID EmployeeID Start End
1419 36 81 15-2-2009 13:00:00 15-2-2009 18:00:00
1420 36 88 15-2-2009 13:00:00 15-2-2009 18:00:00
1421 36 1 7-3-2009 16:15:00 8-3-2009 0:15:00
1422 36 44 7-3-2009 16:15:00 8-3-2009 0:15:00
1433 36 88 21-3-2009 17:15:00 21-3-2009 22:15:00
1434 36 81 21-3-2009 17:15:00 21-3-2009 22:15:00

I have a form (frmAvailable) with the fields “Start” and “End”.

I would like to create a query which makes it possible to see all employees that are still available for a specific timeframe (between Start and End on frmAvailable).

I tried to create multiple queries to achieve this goal but I simply can’t get it to work. I could post all the queries I created but I think in this case it won’t make things easier for you.

I hope you can help me out.

Thank you in advance.

Gr. Marcell

 

[HarleyQuinn]

Depending on the format of data on your form, something like (untested):

SELECT blah
FROM tblBlah
WHERE frmAvailable.Start >= [Start] and frmAvailable.End <= [End]

It would also depend on how you define 'available', could someone start before the timeframe specified on the form yet still be 'available' as they've not finished their shift or do they (as per the query above) have to start and end within the specified period on the form?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Got that query written out all wrong...

SELECT blah
FROM tblBlah
WHERE frmAvailable.Start <= [Start] and frmAvailable.End >= [End]

Think that's a bit better...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.