Hi All
I have 2 tables, one with records for staff shift data (tbl:duty). The other table (tbl:shiftdefin ition) has shiftname and their definitions.
The 'duty' table has a field 'duties' and the records will have multiple characters in this field. The 'shiftdefinitio n' table has a field 'dutycode' with single character and another field as 'definition'. I need to join this 2 tables on the 'shiftname' and 'dutycode' to get all records...
rajeevs
Last Activity: Dec 31 '18, 08:28 AM
Joined: Jun 12 '07
Location:
-
Join Fields from 2 tables
-
Dear ADezii
Awesome. Even you mentioned that we are very close, but I feel that we have done. That is my first impression after a quick look. I will run with more records and let you know the out come. Thank you so much -
Dear ADezii
The date is based on login and logout. for each hour in a login date the duration need to be found. and if the login and logout is different dates, then upto time 2359 the duration will fall into login date. any duration after 2359 of the login date will be recorded for the logout date. an example is if login 01-Aug-17 2350 and logout 02-Aug-17 0015, then there will be 2 entries need to be added to PositionHrs. 1 entry for 01-Aug-17...Leave a comment:
-
Dear ADezii
Thank you for the effort and patience you have shown. Instead of repeating the code block, is there any way we can while adding the record which has next day logout to have the date captured from the logout. But not for the time period 2300-2359 - That should have date as login date.
Other wise the date will show previous date for hours in next date.Leave a comment:
-
Dear ADezii
Thank you for the kind reply. I am attaching the same DB you have provided, but with manually adjusted expected results in the"PositionHrs " table. As you can see from the "MasterLogs " table, record 4 spans to 2 dates and in the expected results "PositionHr s" table the expected results are in record number 12 to 15. Hope that will give you a clear picture about what I am looking for. The login and logout...Leave a comment:
-
Dear ADezii
Sorry for the delayed reply. I was busy with some personal travelling. The solution is workable but only one issue I am having. When it capture "Start" and Finish" for the logout in next date, that particular entry "00:00" 00:59 or any time in tha next date, the Date should show the logout date. Because "Start" and "Finish" time is in the next date. Hope my explanation is clear. Please...Leave a comment:
-
Dear ADezii
Sorry, you misunderstood. I thought the demo revised 2 DB still incomplete and you are trying again with some options. That is why I suggested an alternate approach. If the revised2 is workable, I can start using it. Please let me knowLeave a comment:
-
Dear ADezii
Thank you for the willingness and patience to resolve my issue. I have tried the second demo also. The Date field in the "PositionHr s" table can be from the date part of login entry. I have a humble suggestion for a different approach which I feel may workout, but my little knowledge cannot put it in a code. It is like, First, read each record from MasterLogs table, then add a record in the "MasterLogs " table...Leave a comment:
-
Dear ADezii
Thank you for the time you spent to support me. It is a great help.
I was trying with few sample entries, and noticed that when the logout extends to next day the code is not processing that record. Example, if login 01-Aug-17 22:58 and logout 02-Aug-17 00:35, there is no records written to the PositionHrs table. In reality there will be entries for each date with login same date but logout after midnight.
Hope you...Leave a comment:
-
Dear ADezii
Thank you very much for your kindness and support. This is a great help I appreciate much. Both errors are resolved after I selected the references for Outlook and Excel. I will wait for your kind updateLeave a comment:
-
Dear ADezii
Thank you for the demo and a positive approach. This looks like I am getting very close to the solution what I am looking for.
I am trying with the demo db but on this line "Debug.Prin t String(80, "-")" i was getting a vb error and when checked in vba references it shows missing outlook 14.0 and excel 14.0 object missing.
I have selected those objects in vba reference and on the form button click...Leave a comment:
-
vba to split duration in each hour and add record
I have an access table (MasterLogs)wit h Date, UserName, Position, loginTime, logoutTime.(Log in and logout field type is date&time). I am trying to read each record in this table and add records to another table (PositionHrs). The field in that table are:
Date
Username
Position
0000-0059
0100-0159
0200-0259
........
........
........
2300-2359
Column 4 on wards... -
Hi All
I think I will re phrase the question which will be enable me to get some help.
I have a table for users records with login logout times from certain position.
I would like to loop through this recordset and add to another table for each users time spent during each hour. The second table will have columns like user, position, and 24 other columns with header like 0000-0059, 0100-0159 etc.
SO if a user has login...Leave a comment:
-
crosstab query for duration of login logout
Hi All
I have a table with field names as username, Position,login, logout.
Login, logout fields data type is date and time (dd-mm-yy hh:nn)
I wish to generate a crosstab query with a 30 minute interval as column heading (0000-0029,0030-0059,0100-0129 etc.until 2330-2359)[preferred as 15 or slots like 0000-0015,0016-0030 etc or even 10 minutes slots], position as row heading, datepart from the login also row heading and the value... -
Dear NeoPa
Thank you for the reply. The truncate is happening even middle of the word, but after truncate the message body, it shows the signature text. That is why I said there is no way I can identify the reason.
May be someone can help with VBA script which can read all the sent mail items and write to Excel work sheet or Access table. I need to extract Sent Date, To, CC, Subject and mail body.
I will wait with a hope that...Leave a comment:
-
Dear NeoPa
Thank you for the reply.Point 1 & 2 are correct. I have office 2010 and using the same command sequence.
I tried linked table also. But same result.
There is no consistency of truncating. That is not truncating after any specific length.
I have noticed the longest message is more than 3900 chr but that is truncated after 3900 chr. lesser length also truncates. I have searched for vba assistance, but couldnt...Leave a comment:
-
emails to Access Table
Hi All
I am trying to import MS Outlook emails to a an access table using the access menu item External Data-More-Outlook Folder option.The body of the email imported to memo field but some message body are imported half only. It is not truncated 255 characters because I checked the length of the records in the memo field and some of them are more than 1000 characters.
Is there any other possible way to import all the text in message... -
Dear jforbes
It was amazing. I don't know how to thank you. I have tested with the actual data and it is perfectly imported. I don't know much about class modules but I will try to understand. I dont need 2 tables, this is more than enough for me. Thank you so muchLeave a comment:
-
Thank you jforbes for the kind reply.
I have found a code which is actually importing the txt to one table, but not what I expect. It splits and import data to 2 fields. The code is as below:
...Code:Sub ReadandImport() Dim DB As DAO.Database, RS As DAO.Recordset Dim str1 As String, str2() As String Dim i As Integer Set DB = CurrentDb Set RS = DB.OpenRecordset("Layers") OpenLeave a comment:
-
Text file to access Table
Hi All
I have a text file which has records in a multi line structure.
Each record is separated with a blank line in between.
Some records will have 9 fields (in text file it is 9 lines)
and some of them will have 12 fields (or 12 lines in the text file). If the line has only "Layers" in the start line that line and the next following lines to Tbl1 and if the line has "Groups" in the middle of the line...
No activity results to display
Leave a comment: