By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Copy Same records

P: n/a
Roy
Hello,
Sorry for a lengthy post.
I develop a access 2000 application.As a part of the daily download,I
import data from a excel sheet into a access table.
The data is as follows:

Task Comments
11801 08/02/2004 07:15:43 CET: Submitted
08/02/2004 06:35:39 CET: Please provide Post Mortem
11804 08/18/2004 09:33:46 CET: Sent mail to Carey Walker chasing
the Post Mortem
08/05/2004 11:18:30 CET: Reassigned to Carey to complete
the post mortem
08/03/2004 14:42:46 CET: Reassigned to John to complete
the PM>
08/02/2004 08:40:52 CET: All Severity 1 problems must
have a post mortem produced and distributed within 24
hours. It is the responsibility of the person producing
the post mortem to send the completed post mortem out to
*GT EU Post Mortems
11805 08/02/2004 09:48:48 CET: Post Mortem completed
08/02/2004 08:49:04 CET: All Severity 1 problems must have
a post mortem produced and distributed within 24 hours. It
is the responsibility of the person producing the post
mortem to send
11815 08/03/2004 03:57:59 CET: All details provided to EDC. EDC
to investigate further.
08/13/2004 13:00:59 CET: All details provided to EDC. EDC
to investigate further.

I want to construct a table duplicating the task number for the
different comments associated with a task number.How to do this in a
query or a module,because this will be a part of a daily routine?
Currently,I am doing this copy and paste manually since the volume is
low but which is expected to go five fold soon.

Thanks,

Roy

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Roy
Repost!!!
Any workaround or solution to this????

Nov 13 '05 #2

P: n/a
Roy wrote:
Hello,
Sorry for a lengthy post.
I develop a access 2000 application.As a part of the daily download,I
import data from a excel sheet into a access table.
The data is as follows:

Task Comments
11801 08/02/2004 07:15:43 CET: Submitted
08/02/2004 06:35:39 CET: Please provide Post Mortem
11804 08/18/2004 09:33:46 CET: Sent mail to Carey Walker chasing
...
I want to construct a table duplicating the task number for the
different comments associated with a task number.How to do this in a
query or a module,because this will be a part of a daily routine?
Currently,I am doing this copy and paste manually since the volume is
low but which is expected to go five fold soon.

Thanks,

Roy


I couldn't understand your question so I'll just write something :-).

I have a main form with a subform containing line items in datasheet
view. The subform contains a combobox with some common things that
people can choose. In addition to the selections from the combobox
there is a set of standard notes that can be selected. The standard
notes have to be marked with a standard note identifier (not shown) and
must be selected from a list of literal industry standard descriptions.
tblStandardNotes is a local table.

tblStandardNotes
NoteID Auto
PreClickThisNote Y/N
Include Y/N
StandardNote Text

qryStandardNotes
SELECT Include, StandardNote FROM tblStandardNotes

frmSubStandardNotes
RecordSource: qryStandardNotes

frmSubLineItems
RecordSource: SELECT Remarks FROM tblLineItemsLocal ORDER BY
LineItemID;

When the user clicks the option button for general notes, the standard
remarks are shown (in datasheet view also) with some Include boxes
checked. The user can check or uncheck the Include box in the subform.
When they are done selecting standard notes they click the option
button for line items. They can then choose to append all the standard
notes they selected as line items.

'Begin Code behind form-------------------
Private Sub optStandardNotes_Click()
Dim strSQL As String
Dim lngHold As Long

optLineItems.Value = 0
optStandardNotes.Value = -1
'First, set the prechecked items
strSQL = "UPDATE tblStandardNotes SET tblStandardNotes.Include =
[tblStandardNotes].[PreClickThisNote];"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'Wait a moment
For lngHold = 1 To 1000
DoEvents
Next lngHold
'Now, swap subforms
SubformMain.SourceObject = "frmSubStandardNotes"
End Sub

Private Sub optLineItems_Click()
Dim Response As Variant
Dim strTitle As String
Dim strMessage As String
Dim MyDB As Database
Dim NoteRS As Recordset
Dim strSQL As String
Dim lngI As Long
Dim lngCount As Long

optLineItems.Value = -1
optStandardNotes.Value = 0
strTitle = "Action Confirmation"
strMessage = "Click OK to append the selected notes."
Response = MsgBox(strMessage, vbOKCancel, strTitle)
If Response = vbOK Then
'Add the selected notes
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblStandardNotes WHERE [Include] = -1"
Set NoteRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM tblLineItemsLocal"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If NoteRS.RecordCount > 0 Then
NoteRS.MoveLast
lngCount = NoteRS.RecordCount
NoteRS.MoveFirst
For lngI = 1 To lngCount
MyRS.AddNew
MyRS("Remarks") = NoteRS("StandardNote")
MyRS.Update
If lngI <> lngCount Then NoteRS.MoveNext
Next lngI
End If
NoteRS.Close
Set NoteRS = Nothing
Set MyDB = Nothing
End If
'Now, swap subforms
SubformMain.SourceObject = "frmSubLineItems"
End Sub
'End Code behind form-------------------

I realize that it would have easier to use an Append Query in
optLineItems_Click() but this code is old, it wasn't intended to be
seen by anyone else and I haven't gotten around to changing it yet.
Perhaps you are trying to create a table of distinct tasknumber,
comment pairs that grow daily yet without adding duplicates. Maybe
these ramblings will help you clarify what you are trying to do.

James A. Fortune

Nov 13 '05 #3

P: n/a
rkc
Roy wrote:
I develop a access 2000 application.As a part of the daily download,I
import data from a excel sheet into a access table.
The data is as follows:

Task Comments
11801 08/02/2004 07:15:43 CET: Submitted
08/02/2004 06:35:39 CET: Please provide Post Mortem
11804 08/18/2004 09:33:46 CET: Sent mail to Carey Walker chasing
the Post Mortem
08/05/2004 11:18:30 CET: Reassigned to Carey to complete
the post mortem
08/03/2004 14:42:46 CET: Reassigned to John to complete
the PM>
08/02/2004 08:40:52 CET: All Severity 1 problems must
have a post mortem produced and distributed within 24
hours. It is the responsibility of the person producing
the post mortem to send the completed post mortem out to
*GT EU Post Mortems
11805 08/02/2004 09:48:48 CET: Post Mortem completed
08/02/2004 08:49:04 CET: All Severity 1 problems must have
a post mortem produced and distributed within 24 hours. It
is the responsibility of the person producing the post
mortem to send
11815 08/03/2004 03:57:59 CET: All details provided to EDC. EDC
to investigate further.
08/13/2004 13:00:59 CET: All details provided to EDC. EDC
to investigate further.


There's no way to tell from your post how the sheet is actually
formatted. Are the comments for each task all in one cell or
multiple cells?

I you can't import the data via File, Get External Data,
code could certaily be written to import it via automation.
Couldn't say how without actually seeing it.


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.