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

Append Selected Records to Another Table

P: 5
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months.

Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning Report’, to management which contains numerous details including historical events, projects in place addressing that particular problem, a category type, scheduled routine maintenance items that are relevant, etc.

What’s key is that the reported data is only a snapshot of the situation at that time, and that we need to maintain a history of what was reported. (Events will occur in the 24-hour window, but the records may not be entered until after the cut-off time for the conference call and, those that were added in time may change or even be deleted. In fact, event data is constantly being updated. The purpose of this application is to keep a record of what was reported each day.

The Morning Report process begins by the analyst downloading a txt file ‘ExDwn.txt’ of all events within the last 180 days from a company system (the only access to the data is via the download). tblExDwn in a access database is linked to the download file.

Currently, the Morning Report is created “old school” by users downloading 180 days of events into ExDwn.txt, running a macro in Word which creates headers of event data from last 24 hours, and then running another macro to import the download into Excel. Then they scroll through the rows in Excel and copy and paste the related/relevant events into the Word document.

What I’m shooting for is this: The analyst runs the download, import/update access, opens a form (which amoung a million other things) displays the new events with a subform containing 180 days history. The user selects the related/relevant records and appends them to a table containing the history events for each current event.

Here’s my thinking so far…

Analyst downloads 180 days of events (service problems) from company system to ExDwn.txt. tblExDwn is linked to txt file. There is no unique field, but 3 (Tail, ExDate, ExTime) can be indexed together to identify unique records.

tblMR_Ex – From tblExDwn, new events from last 24 hours are appended to tblMR_Ex, with fields Tail, ExDate, and ExTime concatenated into txtMR_ExID as is PK. This table also has additional fields which capture analysis results such as category type, last service date, open projects on that system, etc.

frmMR_Ex bound to the new records (from last 24 hours) in tblMR_Ex.
sfmMR_Ex180 displays all records of the 180 days downloaded – Since I need a PK, I’m guessing these records must first go to another table via delete and append queries to overwrite the 180 days of records that were there the previous day. If so, that would be table would be tblMR_Ex180 (basically, a temp table). And, the subform bound to it and linked to main on fields [Tail] and [ATA].

What I need is to be able to select records in the subform and append them along with the PK of the main form to a history table tblMR_ExHist.

So, sfmMR_Ex180 shows 180 days of history for each new event displayed in frmMR_Ex using fields [Tail] and [ATA] to link. The analyst selects only those records which are truly related/relevant and appends those records to tblMR_ExHist.

Another wrench in the cog:

During the hour or so the report is being compiled, the analyst needs to be able to re-run the download and update the records which are to be reported on that day. So, if an event is deleted or revised then I would like some way to notify the user that the status of that record has changed. Perhaps a status field in frmMR_Ex would reflect if record has been added, deleted, or updated, and subsequently update the fields which were revised. And something to let the analyst know why a record suddenly dropped from list of new events to be reported on. I did find some great code for comparing and updating tables at Roger’s Access Library which could work except for a couple issues – 1) tblMR_Ex not only has the fields in ExDwn but also has about 15 addition fields which capture analysis results. At first attempts at using this code, it appears both tables must be structured the same. But, we can get back to that one later. So, how best to handle getting the new event data in and being able to update it several times during the process of creating that morning’s report?


My first question is how can I enable users to select only relevant records in sfmMR_Ex180 and append them with the MR_ExID from frmMR_Ex to tblMR_ExHist?

Please post if any of this is confusing or lacking necessary details. And many thanks for any and all assistance!!

Mike
Mar 7 '07 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,489
Mike,

I think you'll find that this suffers more from Too Much Information rather than too little. Very few members have the time to spend reading and understanding a whole 'Project'.
If you get nothing from this then consider how you can break it down a bit and leave out some of the information which isn't directly relevant to the question.
Bite sized chunks is the key to finding answers in a forum such as this. I know that it can be very hard, and even impossible sometimes, to produce a question in that form when the very nature of the question is complicated and involved.

Anyway, from my brief perusal of the question, I thought you were after a way of logging what has been included in the Morning Reports on a daily basis.
If, when you create the report (Export; Report; however you do it), you include in the code that does it some extra code to copy that selected data to another logging table (tblMR_ExHist?), then this can be used as a log to show what's been included. I'm sorry if I missed the point. I freely admit that I was unable to read and digest the whole question in detail, but I hope it helps somewhat.
Mar 8 '07 #2

P: 5
Thanks for your response, and the tip!

I'm trying to figure out out to select some records from the subform and append them (or their PK(s) with the PK of the main form...

tblMR_Ex is recordsource for frmMR_Ex
tblEx180 is recordsource for sfmMR_ExHistSelect

I've found some code elsewhere and hoping I can make it work for my application. This is basically my first attempt at using code, so please excuse any messes. Here's where I've gotten, but am not quite there...

Added field blnSelected (Yes/No) to tblEx180 - I'd like to append the record if check, and if subsequently delete it if unchecked.

This is on the after update event of blnSelected (checkbox). But, I'm getting data type mismatch error in the criteria section.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub blnSelect_AfterUpdate()
  4.  
  5.     Dim strSql As String
  6.     Dim strMR_ExID As String
  7.     Dim strEx180_ID As String
  8.     strMR_ExID = Me.Parent.MR_ExID
  9.     strEx180_ID = Me.Ex180_ID
  10.     DoCmd.SetWarnings (True)
  11.     If Me.blnSelected Then
  12.         strSql = "INSERT INTO tblMR_ExHist ( [MR_ExID], [Ex180_ID] ) "
  13.         strSql = strSql & "SELECT tblMR_Ex.[MR_ExID], tblEx180.[Ex180_ID] "
  14.         strSql = strSql & "FROM tblMR_Ex INNER JOIN tblEx180 ON "
  15.         strSql = strSql & "(tblMR_Ex.Tail = tblEx180.Tail) AND (tblMR_Ex.ATA = tblEx180.ATA) "
  16.         strSql = strSql & "WHERE tblMR_Ex.[MR_ExID]= " & strMR_ExID & " AND "
  17.         strSql = strSql & "tblEx180.[Ex180_ID] = " & strEx180_ID
  18.     Else
  19.         strSql = "DELETE tblMR_ExHist.[MR_ExID], tblMR_ExHist.[Ex180_ID]"
  20.         strSql = strSql & "FROM tblMR_ExHist "
  21.         strSql = strSql & "WHERE tblMR_ExHist.[MR_ExID]= " & strMR_ExID & " AND "
  22.         strSql = strSql & "tblMR_ExHist.[Ex180_ID]= " & strEx180_ID
  23.     End If
  24.         DoCmd.RunSQL (strSql)
  25.         Debug.Print strSql
  26.     DoCmd.SetWarnings (True)
  27.  
  28. End Sub
  29.  

This is on the current event of the main form, but need to clean up the above to see if this is going to work.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Current()
  5.     Call updateFromHistory
  6.  
  7. End Sub
  8.  
  9. Public Sub updateFromHistory()
  10.     Dim strSql As String
  11.     Dim strMR_ExID As String
  12.     strMR_ExID = Me.MR_ExID
  13.     DoCmd.SetWarnings (True)
  14.  
  15.     strSql = "UPDATE tblEx180 INNER JOIN tblMR_ExHist ON "
  16.     strSql = strSql & "tblEx180.[Ex180_ID]=tblMR_ExHist.[Ex180_ID] "
  17.     strSql = strSql & "SET tblEx180.blnSelected = True "
  18.     strSql = strSql & "WHERE tblMR_ExHist.[MR_ExID] = " & strMR_ExID
  19.     DoCmd.RunSQL (strSql)
  20.     DoCmd.SetWarnings (True)
  21.     Debug.Print strSql
  22. End Sub
  23.  
I'm totally green with VBA and SQL, so any thoughts, inputs, or suggestions would be greatly appreciated.

Thanks,

Mike
Mar 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,489
Mike,

Put yourself in my (our) position and remember that until you explain the context of your problem (briefly and concisely - Assume I'm not looking at post #1), anything else you write related to that goes straight over my head.
When I do get to the point that I understand the broader structure of your problem, I need to be able to read what you say without translation. That means you need to write it, then read it back to make sure it makes sense.
I'm trying to figure out out to select some records from the subform and append them (or their PK(s) with the PK of the main form...
This may be an example of your not understanding the way Access works very well, which is fine, but it still leaves me trying to guess what you mean (I didn't manage to btw). This is poor use of my time. It's very easy for me simply to ignore any thread where I feel the amount of deciphering is not worth my time. Don't get me wrong, I think you're trying your best in a new situation (otherwise I may well have left this already ;)), so I'm cutting you some slack.
Remember, accurate communication is much harder via the medium of a web page than most people are used to, so extra effort is well worth the trouble.

Lecture over ;)

This is on the after update event of blnSelected (checkbox). But, I'm getting data type mismatch error in the criteria section.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub blnSelect_AfterUpdate()
  4.  
  5.     Dim strSql As String
  6.     Dim strMR_ExID As String
  7.     Dim strEx180_ID As String
  8.     strMR_ExID = Me.Parent.MR_ExID
  9.     strEx180_ID = Me.Ex180_ID
  10.     DoCmd.SetWarnings (True)
  11.     If Me.blnSelected Then
  12.         strSql = "INSERT INTO tblMR_ExHist ( [MR_ExID], [Ex180_ID] ) "
  13.         strSql = strSql & "SELECT tblMR_Ex.[MR_ExID], tblEx180.[Ex180_ID] "
  14.         strSql = strSql & "FROM tblMR_Ex INNER JOIN tblEx180 ON "
  15.         strSql = strSql & "(tblMR_Ex.Tail = tblEx180.Tail) AND (tblMR_Ex.ATA = tblEx180.ATA) "
  16.         strSql = strSql & "WHERE tblMR_Ex.[MR_ExID]= " & strMR_ExID & " AND "
  17.         strSql = strSql & "tblEx180.[Ex180_ID] = " & strEx180_ID
  18.     Else
  19.         strSql = "DELETE tblMR_ExHist.[MR_ExID], tblMR_ExHist.[Ex180_ID]"
  20.         strSql = strSql & "FROM tblMR_ExHist "
  21.         strSql = strSql & "WHERE tblMR_ExHist.[MR_ExID]= " & strMR_ExID & " AND "
  22.         strSql = strSql & "tblMR_ExHist.[Ex180_ID]= " & strEx180_ID
  23.     End If
  24.         DoCmd.RunSQL (strSql)
  25.         Debug.Print strSql
  26.     DoCmd.SetWarnings (True)
  27.  
  28. End Sub
To help with this I would need the MetaData of the relevant tables (tblMR_Ex & tblMR_ExHist). If they are basically the same then only one will be required.
I expect one of the references is treating a field as the wrong type, but I can't tell for sure, or which one and how to fix it, until I have the MetaData.


Table Name=tblMR_Ex
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. ???ID; AutoNumber; PK
  3. ???ID; Number; FK
  4. ???; String; FK
  5. ???; Boolean
  6. ???; Number
  7. ???; Date/Time
With the MetaData, I can check out your SQL code even without an overall understanding of your situation. That won't be true of all types of questions obviously.
Mar 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.