Calculating / lookup automatically

[thegooser]

Hi all....

I have a problem that I'm finding really hard to explain, but here goes.

I have a database for a taxi company to keep track of the jobs we do, which driver did them, and when.

One of the tables is the drivers working times layed out like so: Name of driver | Date and time shift started | Date and time shift finished.

Another table has the details of the jobs that we have done and is layed out like so: Date | Time |(a few fields for the job details) | Driver.

Is it possible for access to look at the date and time that the job took place and tell me the date that the driver started the shift by putting it in a new field?

At the moment, I have to link all the data into excel and use a sumproduct to do it, but now I want to use the brilliant reporting features of access to do things on. In excel I have this next to every job:

=SUMPRODUCT(--(Table_Query_from_MS_Access_Database_1[Driver]=$I3), --(Table_Query_from_MS_Access_Database_1[Start Date And Time]<=$D3), --(Table_Query_from_MS_Access_Database_1[Finish]>=$D3),Table_Query_from_MS_Access_Database_1[ShiftID]))

Any ideas how I can either get access to add a feild onto the job table and automatically look up the date the driver started the shift that he did the job in, or alternativly, look up the cell in excel and input it?

Thanks for any help anyone can give me.

[jgelpi16]

Would a DLookup() feature work?
http://techonthenet.com/access/funct...in/dlookup.php

Dim vResult as Date

vResult = DLookup("FieldYouWantToReturn", "TableFieldIsLocatedIn", "FieldYouWantToMatch = DriverID")

[alansidman]

I think your answer is quite simply create a query in Access based upon your two tables, which should be joined on the driver fields. Now you can use whatever criteria you wish to extract the information in the two tables.

alan

[thegooser]

I think your answer is quite simply create a query in Access based upon your two tables, which should be joined on the driver fields. Now you can use whatever criteria you wish to extract the information in the two tables.

alan

I need to use the result as another field in the jobs table though. I don't get how I can add the desired result in the record.

[thegooser]

Would a DLookup() feature work?
http://techonthenet.com/access/funct...in/dlookup.php

Dim vResult as Date

vResult = DLookup("FieldYouWantToReturn", "TableFieldIsLocatedIn", "FieldYouWantToMatch = DriverID")

I'll read up on it. It isn't something I have ever done before so I'll try to get it figured out. Where would I place that code?

[jgelpi16]

I am pretty sure alansidman's method will work. My method is a VBA solution where his is a query solution. My code would be put in a button or maybe a "lost focus" event if you want it to be triggered as soon as the object has lost focus. I recommend putting it in a button if only for testing. Below is a more complete set of code that you should put in the click event of your button:

Dim vResult as Date
Dim vDriverID as String
Dim mySQL as String

vResult = DLookup("FieldYouWantToReturn", "TableFieldIsLocatedIn", "FieldYouWantToMatch = DriverID")

mySQL = "UPDATE [table_name] SET [column_name] = #" & vResult & "# WHERE [other_column]='DriverID';"
CurrentDb.Execute mySQL, dbFailOnError

This will look up the Date you want and update the record in the new/desired table.

[alansidman]

Here is my take on it. Make sure you format your search input the same as set up in the table.

[thegooser]

Here is my take on it. Make sure you format your search input the same as set up in the table.

I think I am either explaining it wrong or understanding it wrong.

I need access to automatically add the shift date on the end of every record. I don't want the ability to look them up.

[alansidman]

Still not sure what you are doing. Removed the Search. Here is the query with what you asked. It contains the information you have requested. The order of the columns can be moved in the query to however you want them arranged.

This is a simple join of two tables. You store your data in the tables and retrieve the information through queries. There is no reason to put the information in the Driver table if you can generate the report (query) with all the information available. Databases are not spreadsheets and if you are thinking that way, you need to change your thought process.

Alan

[thegooser]

I can't see what this does?

When ever I put in a new driver shift, then a new job, it duplicates it on the query. I'd send my database over but I have been using it for the last few months and now it has lots of peoples addresses on it, so I can't really put it up in the forum.

I might have to just do it in excel for now.

Alternativly, is there a way that the record can look for the matching record in excel and return a certan cell?

[alansidman]

Why don't you make a copy of the database. Delete all but a few records and then sanitize those few records. I know it sounds like a lot of work, but we are not getting what you are trying to accomplish and this may be the only way to solve it.

Alan

[thegooser]

I have made a copy of my database, deleted all the entries, and added some sample data.

It is still 6 megs so unfortunatly I had to up it to my server. You can get it here: http://www.westcornwallcars.com/copy.accdb

I think the easiest way to see what I am trying to achieve is to open the "All Jobs" Table and have a look. There is only two jobs in here right now. If you also open the "Drivers Working Times" table you can see when there drivers were working.

The two jobs in there are both the same data and time (3am on 3rd feb 2001) and the two drivers both did one of these jobs each.

However... (and this is my problem).... "Nigel" started his shift at 11:30 pm on the 2nd of Feb, so in the soon to be named "Shift Started" field in the "All Jobs" table I need it to say "02/02/11". It will know this because it will look up the date and time, see when that driver started the shift, and return that date.

Poor old "Jimmy" didn't start his shift until 1am (on the 3rd of Feb) so the added field needs to say "03/02/11" because that was the date that "Jimmy" started his shift the day that he did the job.

So I need access to add another field on the "All Jobs" table. Then look up to see when the driver that did the job started his shift, and return the date automatically without me ever having to touch it.

The idea is that I can then do reports on drivers categorising their jobs into the date that they started their shift, which if they work past midnight will sometimes be different to the date that they did the actual job.

I think I have made it sound really confusing again, but I think by looking at the two tables "All Jobs" and "Drivers Working Time" will make it all clear.

And thanks for your help.... and patience.

[alansidman]

Ok. I have looked and your explanation makes more sense, but I think you are missing something on database normalization. If information is in one table and you wish to have the information linked to information in another table and generate a report, then you do that through a query. You do not duplicate the information in more than one table. Forms cannot be bound to more than one table, so what you are attempting to do can be achieved through a query and the query becomes the record source for your report. I am sure I can help you to get there. Tell me which fields in which tables you want to appear in your report. Do you have one of the queries started? Have you begun the particular report you want? Which ones?

BTW--I did a compact and repair and the size of the database is now 780kb. You should C&R on a regular basis. In this case, when you deleted all the data, MS does not give back the kbs until you do a C&R.

Alan

[thegooser]

Ok. I have looked and your explanation makes more sense, but I think you are missing something on database normalization. If information is in one table and you wish to have the information linked to information in another table and generate a report, then you do that through a query. You do not duplicate the information in more than one table. Forms cannot be bound to more than one table, so what you are attempting to do can be achieved through a query and the query becomes the record source for your report. I am sure I can help you to get there. Tell me which fields in which tables you want to appear in your report. Do you have one of the queries started? Have you begun the particular report you want? Which ones?

BTW--I did a compact and repair and the size of the database is now 780kb. You should C&R on a regular basis. In this case, when you deleted all the data, MS does not give back the kbs until you do a C&R.

Alan

Thanks Alan.

Basically, once a week I need to print off the jobs that each driver did per day, but I need to print them by the day they started the shift.

So the report would look a little like this:

Sunday
Date | Time | Job ID | Cost

Monday
Date | Time | Job ID | Cost

Tuesday
Date | Time | Job ID | Cost

... listing the jobs by day for the week and totalling them up. The day that they need to be in is the day that they started the shift and not necessarily the day that the job was on.