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.
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.
9 2972 NeoPa 32,556
Expert Mod 16PB
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.
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..taking 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 32,556
Expert Mod 16PB
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.
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 telecomunications 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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB @Kan09
That's great. Glad I was able to help.
PS. We all miss the obvious at times. I know I do. @Kan09
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 ;)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andrew McLean |
last post by:
I have a problem that is suspect isn't unusual and I'm looking to see if
there is any code available to help. I've Googled without success.
Basically, I have two databases containing lists of...
|
by: Henry |
last post by:
I have a table that stores a list of zip codes using a varchar column type,
and I need to perform some string prefix pattern matching search. Let's say
that I have the columns:
94000-1235
94001...
|
by: Stuart E. Wugalter |
last post by:
Hiya Folks!
I need some help with some string pattern matching. Let's say field1 (in
Table1) contains the strings in question and it's data type is memo. I have
a form that uses Table1 as its...
|
by: David C. Barber |
last post by:
I'm trying to determine if any matching records exist on a LIKE query
performing a partial match of last names to a remote back-end database in
the most efficient manner possible. LAN Traffic...
|
by: solandre |
last post by:
hi there,
i have to find matches in two sorted arrays. there are two good
possibilities as a temporary solution, performing different, depending
on the inner structure of the data. but i try to...
|
by: Ole Nielsby |
last post by:
First, bear with my xpost. This goes to
comp.lang.c++
comp.lang.functional
with follow-up to comp.lang.c++
- I want to discuss an aspect of using C++ to implement a
functional language, and...
|
by: Petepinca |
last post by:
I have 2 Access tables. One has a field with a 4 character product id. Example data: "3210". The other table has the same product id's for matching records BUT has leading characters: "RD3210", etc....
|
by: mmarif4u |
last post by:
Hi everyone.
i make a page that a user input thier icnumber with confirm ic number,
it saves the data to mysql db with current date and a random access code
generated automatically, NOW i have...
|
by: Drayno241 |
last post by:
I'm working in access 2002. I have three tables :
1- District Data (Student ID, name, grade, etc)
2- Rosters (RRec ID,Campus, Teacher ID)
3- Students on Roster(SRec ID, RRec ID, Student ID)
...
|
by: santhoshs |
last post by:
Hello
I am required to parse two files that contain email addresses and figure out a way to get the matching and non-matching email addresses from both the files. I was able to get the matching...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |