Lookups .... I think!

[thegooser]

Hi all...

I have had a problem for a while when I was using Access 2007, but I recently upgraded to 2010 and I'm trying to solve it again. I have always linked my tables to Excel to solve this problem but I would love to do this all within access if poss.

Bassicaly I have two tables in question. One is jobs that we do for our taxi firm, the other is the dates and times that drivers started their shifts. I need the job table to magically know which drivers shift it was done in.

For example:

Jobs
Job: 1 - 03/09/11 16:15 - Nigel
Job: 2 - 04/09/11 19:45 - Mario
Job: 3 - 04/09/11 17:50 - Nigel

Shifts
Unique ID - Driver - Start Shift - End Shift
Shift 1 - Nigel - 03/09/11 15:00 - 04/09/11 01:00
Shift 2 - Nigel - 04/09/11 16:00 - 05/09/11 02:30
Shift 3 - Mario - 03/09/11 23:30 - 04/09/11 18:00

Using this shockingly complicatedly layed out example, I would need to know that Job 1 was done in shift 1, because the driver was Nigel and the date and time of the job fell in between the start and end time of shift 1.

I need this because of a few different reports I need to print and lookup but at the moment I have to link it to excel and use lookups etc to add the shift ID to the actual record.

I can get a query to pop up and ask me drivers name, shift start and shift end and it will report on that particular shift, but I think I need something a little more advanced to add the shift to the actual record.

Any ideas?

 

[pbaldy]

I think a non-equi join will work for you. See my response here:

http://www.access-programmers.co.uk/forums/showthread.php?t=201120

 

[thegooser]

Thanks for the help.

How would I create this and where would I put it?

 

[pbaldy]

You posted in Queries, and that's a query. You can start with a query with a simple join, and then modify it in SQL view.

 

[gemma-the-husky]

or a dlookup

search for the shift for a given driver where the shiftstarttime is <= job time, and the shiftendtime is >=job time

 

[thegooser]

thanks for the replys guys....

I just don't understand, with my limited knowledge of Access, how I would implement this. Ideally I would like it added as another field on either a query or on the table.