Can Access look up values ?

[thegooser]

Now then....

Is it possible to do the following in Access?

I have a table with working shift start and finish times. I also have a table with Jobs / sales which have a time field and a date field.

My tables:
1) Jobs-
a. Date
b. Time
c. Driver


2) Drivers Working Times
a. Start Time & Date
b. Finish Time & Date


Can I get a new field in the Jobs table, and have Access look up what driver did the job and what date and time the job was done, and then return the value of the DATE THE SHIFT STARTED?

At the moment I have to import everything into excel just to do this. I use a sumproduct to look it all up. I would like to do it all in access though because I now need to use the reporting functions.

Any ideas?

 

[DCrake]

In your driver working times table you need to enter the job number against each date/time so you know what jobs they did on what dates. When you have done that then you can create sub totals.

 

[thegooser]

In your driver working times table you need to enter the job number against each date/time so you know what jobs they did on what dates. When you have done that then you can create sub totals.

On the job total can it not take the info thats in the working times table to tell me the shift start date?

 

[DCrake]

What is the differenc between the date and time in the first table and the start and end date time in the second table?

Also will the times be crossing midnight?

 

[thegooser]

What is the differenc between the date and time in the first table and the start and end date time in the second table?

Also will the times be crossing midnight?

The first table is Jobs. So I have an exact Time, Date, and Driver fields.

The second table is two date & times. One is the start time and date of a drivers shift. The other is the end time and date. So it is a table of driver name, when they started their shifts and when they finished their shifts.

So I have a list of jobs, with (among other things) date we did the job, the time of the job, and which driver did it. In the second table I have a list of who worked, and between what times.

I need a field in the first table (the jobs) to look up when the driver was working and return the Date that they started.

The drivers work over midnight regulaly, but I need to label the jobs by the date that their shift started when they did the job.

Is any of this making sence?