matching data in access

[Kan09]

In a table have a column "start time" that is populated with the present time (hh:mm:ss) when a user starts entering data.
We also receive an Excel doc that is imported into the database. The Excel has a Time column and a total time column.

I need to match the 2 columns "Start time" and "time". How do i do that? The start time has the format hh:mm:ss while the Time column has the format hh:mm.

[NeoPa]

If the time values are store as Date/Time fields then comparisons should match.

When you say you enter "the present time (hh:mm:ss)" are you adding this automatically using the Now() function? If so you are not entering the current time alone, but also the current date. This obviously won't match anything stored simply as a time regardless of which elements (h; m; s) are stored.

[Kan09]

I'm sorry for the confusion. The current time is inserted into the table using the time function. This gives me for example: 13:24:52 in the "Start Time" field. In the Time field of the other table the value imported looks like this: 13:24:00. Every imported value has the 00 seconds.
I'm thinking i need to make the Start time field show the time without the seconds. I ca't think of any other way i can make this matching..takin g into account that the 2 tables aren't exactely related. Another problem is that The start time field is used along with the stop time field for calculating a time difference.

[NeoPa]

I never even realised the Time() function existed :D

I think I'm still a little confused though, as to why losing the seconds would cause any problems. It's a time value like all the others. Perhaps you could explain exactly what you're attempting and how you use each of the values.

[Kan09]

OK

This should be a customer support application. The users receive a phone call from the client and they start entering data into the dbs. There are three tipes of activities: phone calls recived directly form the clients (one table) , phone calls made or recived from the branches (one table) , and other activities (one table). Data from the 3 tables is entered via 3 forms.
Originally we wanted to make a timer so that we can measure the amount of time in witch every user solves the problem...but access not being able to do a timer we made a Start Time column on each table witch is populated when the user starts entering data into the form/table , a Stop time field witch get populated trough a on click event (save button). Aditionally we have a total time witch makes a time difference between the start and stop time trough the date2diff module (found on the net). All the above time fiels are calculated in hours, minutes and seconds. Thus we can make a raport for each user with the time he spend on the phone.

Aditionally we recive from the telecomunicatio ns department 2 excel files with the calls in and calls out for each user from the customer support. These 2 files need to be imported into access and then matched with the information enterd into the dbs by the users. I'm trying to make this match through the start time filed on the tables and the Hour field on the excel. The problem occurs here. The start time fields have the a time format different form the excel files. Ex: 14:24:36 on the tables 2:24 PM on the excel files. When i make the import into a data/time field the data gets formated from 2:24 PM into 14:24:00. So the matching will not work becouse the fields don't exactley match.


help?

[NeoPa]

That makes some sense. I will suggest a way whereby you can match up values which are not exactly the same but are within a minute of each other. Remember, unless both systems use the same clock source to determine when a call starts there will often be times stored with different minutes from the matching item. You may also get multiple calls for the same time on a help desk, unless you have only one person answering the phone and log the time of when it's answered. If this is the case then you have a non-flexible system that will start to fail if you ever expand the Help-Desk. Can you make it work reliably in the circumstances. These are issues for you to consider.

Assuming then that the Excel version, as well as the Access one, is stored as a Date/Time value (rather than a string - Your question would be pretty stupid if they were stored as strings and you didn't mention it before now), then some SQL similar to the following would enable you to match up the records from the two different sources :

SELECT *

FROM   [AccessTable] AS tA LEFT JOIN
       [ExcelTable] AS tE
  ON   (tA.TimeVal Between DateAdd('n',-1,tE.TimeVal)
                       And DateAdd('n',+1,tE.TimeVal)

If you find that the way the times are entered is not accurate enough for this, then you can increase the + & - values to suit the level of accuracy you find in your data.

[Kan09]

First of all i need to specify that i'm not a programmer. The first time i've seen access or any other relational database was 4 months ago...so i'm kind of a beginner at this.

i've tryed implementing that code like so:

SELECT * 
FROM   MakeTableUniteQuery AS tA LEFT JOIN 
       Customer_Calls_In AS tE 
  ON   (tA.[Start Time] Between DateAdd('n',-1,tE.Ora) 
                       And DateAdd('n',+1,tE.Ora)

I keep getting the syntax error in join operation mesaje. WHY???

[NeoPa]

You're missing a closing parenthesis ')' on line #5 (at the end). Otherwise the code looks good. It may be that. Let us know what happens when you change it.

[Kan09]

hmmm
yup it was obvious but i didn't see it. It works now. i'll do some tests on it as soon as i can find the time. Thank you very much NeoPa and i'm sorry you had to edit my previous post.

[NeoPa]

hmmm
yup it was obvious but i didn't see it. It works now. i'll do some tests on it as soon as i can find the time.

That's great. Glad I was able to help.

PS. We all miss the obvious at times. I know I do.

Thank you very much NeoPa and i'm sorry you had to edit my previous post.

Not a problem. I find myself doing that a lot as new posters understandably don't go through all the rules before posting. I'm pleased you noticed anyway (most don't). I doubt I'll be needing to do it for you in future ;)