Append only Selected Records / Update Tables 1

[mbarkley9872]

I’ve spent months trying to figure out how to accomplish what seemed like the easiest of objectives, but every time I think I’ve got it figured out I seem to hit another major stumbling block. So, before I throw it all away and forever deem myself as a failure, I’m hoping someone here can take a look at my situation and perhaps get me started on the right path…

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. The most relevant piece for my question(s) is the historical events – we need to include all like-events which have occurred within the last 180 days.

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 until the 10th of each month, at which time all data for the previous calendar month is “locked”.)

The Morning Report process begins by the analyst downloading a txt file ‘ExDwn’ of all events within a selected date range 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, 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 along with my questions…

tblMR_Ex - The new events (from last 24 hours) will be appended to tblMR_Ex. This table will have additional fields which capture analysis results such as category type, last service date, open projects on that system, etc. See Question 1, below.

tblMR_ExHist – For each event in tblMR_Ex, 180 days of event history. Here’s where it starts getting mind-numbing… See Question 2, below.

frmMR_Ex shows the events for last 24 hours.
sfmMR_ExHist shows 180 days of history for those events 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. See Question 3, below.

tblMR_Ex 1-to-many relationship with tblMR_ExHist.

Q1: 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 a status field in tblMR_Ex, would reflect if record has been added, deleted, or updated, and subsequently update the fields which were revised. I found 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. 2) I first need to create a table in Access before comparing and updating. 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?

Q2: What would be the record source for tblMR_ExHist – ExDwn? A temp table containing 180 days of history? Or, tblExHist detailed below?

Q3: How can I enable users to select only relevant records in sfmMR_ExHist and append them with the MR_ExID from frmMR_Ex to tblMR_ExHist?

Additional table and key field information:

Key fields available from ExDwn .txt - Tail, Date, Time, ATA (code for effected system), Gtwy (location of problem occurrence), Description, and ActionTaken.

tblExDwn - linked to downloaded txt file which contains last 180 days of Events. No unique field, but 3 fields (ExDate, ExTime, Tail) can be indexed to identify unique records. Currently, during the updating of tblExHist, these fields are concatenated into at text field [ExID] and is set as Primary Key

tblExHist - stores all event history and is updated periodically by running the ExDwn download, and then running delete and append queries to overwrite records within the same date range as was used for the download. This is essentially our main table that we use for multiple purposes, currently none of which is generating the Morning Report. It’s used for trending, analysis, monthly reporting, year over year reporting, etc.

tblMR_Ex - main data table to store events reported on Morning Report. Has all fields from tblExDwn plus a dozen or more additional fields which captures research and analysis findings.

tblMR_ExHist - For each event, the user reviews 180 days of event history and determines if any are prior occurrences or are related to the event under review. If so, this table captures those historical events.

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

Mike

 

[mbarkley9872]

Too much info? I'll try to break it down into smaller chunks.

If I have...

tblMR_Ex:
- MR_ExID PK
- Tail
- Date
- ATA

tblMR_Ex180:
- MR_Ex180ID PK
- Tail
- Date
- ATA

tblMR_ExHist:
- MR_ExID FK
- MR_Ex180ID FK

frmMR_Ex – recordsource is tblMR_Ex
sfmMR_Ex180 – recordsource is tblMR_Ex180; linked to frmMR_Ex by [Tail] and [ATA]

How can I select 1 or more records (or none) in sfmMR_Ex180 and append [MR_Ex180ID] and [MR_ExID] (from the main form) to tblMR_ExHist?

Thanks for any assistance!

Mike

 

[mbarkley9872]

[purple]I've got a bunch of stars I'd love to give to somebody![/b]

For anybody looking for info on appending only selected records from a subform, I've also posted that question in the Forms forum.

Mike

 

[MajP]

This is how I did it. There is probably a cleaner way. In my table tblMR_Ex180 I added a field "blnSelected". This is kind of a dummy field because it is only a place holder that you check and then use it to move data to the History table. My subform has this as a checkbox. As I check it it loads the values into the history table. If I uncheck it it removes the values from the history table.

On the current event of the main form, I get any values from the history table and update my check boxes.

Mainform code:

Option Compare Database
Option Explicit
Private Sub Form_Current()
   Call updateFromHistory
End Sub

Public Sub clearSelection()
 Dim strSql As String
 DoCmd.SetWarnings (False)
 strSql = "UPDATE tblMR_EX180 SET tblMR_EX180.blnSelected = False "
 strSql = strSql & "WHERE tblMR_EX180.blnSelected = True"
 DoCmd.RunSQL (strSql)
 DoCmd.SetWarnings (True)
 'Debug.Print strSql
End Sub

Public Sub updateFromHistory()
  Dim strSql As String
  Dim lngMR_EXID As Long
  lngMR_EXID = Me.MR_EXID_PK
  DoCmd.SetWarnings (False)
  strSql = "UPDATE tblMR_EX180 INNER JOIN tblMR_EXHIST ON "
  strSql = strSql & "tblMR_EX180.[MR_EX180ID PK]=tblMR_EXHIST.[MR_EXID180 FK] "
  strSql = strSql & "SET tblMR_EX180.blnSelected = True "
  strSql = strSql & "WHERE tblMR_EXHIST.[MR_EXID FK]= " & lngMR_EXID
  DoCmd.RunSQL (strSql)
  DoCmd.SetWarnings (True)
  Debug.Print strSql
End Sub

sub form code

Private Sub blnSelected_AfterUpdate()

  Dim strSql As String
  Dim lngMR_EXPK As Long
  Dim lngMR_EX180PK As Long
  lngMR_EXPK = Me.Parent.MR_EXID_PK
  lngMR_EX180PK = Me.MR_EX180ID_PK
  DoCmd.SetWarnings (False)
  If Me.blnSelected Then
    strSql = "INSERT INTO tblMR_EXHIST ( [MR_EXID FK], [MR_EXID180 FK] ) "
    strSql = strSql & "SELECT tblMR_EX.[MR_EXID PK], tblMR_EX180.[MR_EX180ID PK] "
    strSql = strSql & "FROM tblMR_EX INNER JOIN tblMR_EX180 ON "
    strSql = strSql & "(tblMR_EX.Tail = tblMR_EX180.Tail) AND (tblMR_EX.ATA = tblMR_EX180.ATA) "
    strSql = strSql & "WHERE tblMR_EX.[MR_EXID PK]= " & lngMR_EXPK & " "
    strSql = strSql & "AND tblMR_EX180.[MR_EX180ID PK] = " & lngMR_EX180PK
  Else
    strSql = "DELETE tblMR_EXHIST.[MR_EXID FK], tblMR_EXHIST.[MR_EXID180 FK]"
    strSql = strSql & "FROM tblMR_EXHIST "
    strSql = strSql & " WHERE tblMR_EXHIST.[MR_EXID FK]= " & lngMR_EXPK & " AND "
    strSql = strSql & " tblMR_EXHIST.[MR_EXID180 FK]= " & lngMR_EX180PK
  End If
    DoCmd.RunSQL (strSql)
    Debug.Print strSql
  DoCmd.SetWarnings (True)
End Sub

Now to really simplify this, build all of the queries and then call them. I wrote the queries in code just to show how to build them.

 

[MajP]

In the main form the sub Clearselection is not needed. That was a leftover from doing the update on the form current event instead of the blnSelected on update.

 

[mbarkley9872]

Since both forms are basically using the same data (both tblMR_Ex and tblMR_Ex180 are built from the same downloaded .txt file), is this the best to set up my tables?

1) tblExDwn linked to txt file containing 180 days history
2) Append from tblExDwn the new events for last 24 hours to tblMR_Ex(or if I can, compare & update).
3) Append all records from tblExDwn to tblMR_Ex180

Since it will likely take me a day or so to shoehorn your code into my application (since I'm green, and have other must-do's), I want to be sure my basic structure is good before I go too far, AGAIN...

Thanks a million!

Mike

 

[MajP]

I am not sure if I fully understand your process, but I think if I was doing this I would have only one event table. Normally when you normalize a database one table contains one type of data. To me it looks like "Events" are "Events" reardless if they happened today or 180 days prior. I will call it "tblEvents". All events go into this table. The table is self referencing with tail and ATA. This table sounds like it gets continually updated so I would review the sql "NOT IN" key words. Since you want to only add new records not duplicate those already in the table.

Your main form is then based on tblEvents where the date is within 24 hours of NOW. Your subform is based on tblEvents where ATA = ATA and Tail = Tail and the date is whitin 180 days of the main form event.

I still think you need the history table like you had it especially if you need a snapshot in time. The code I presented can be radically simplified if you make the queries correctly.

Not sure if this makes sense to your model.

 

[mbarkley9872]

If I understand correctly, you're saying to...

1. download 180 days, append to tblMR_Ex180
2. frmMR_Ex filtered to last 24 hours (>= 07:00Z yesterday)
3. sfmMR_ExHist all 180 days, linked to main by Tail and ATA
4. apdMR_ExHist appends PK of frmMR_Ex and PK of selected records on sfmMR_ExHist based on self-join of tblMR_Ex180

Sound good minus one small issue. If history table is "updated" with new information, when I re-run a report from last week it won't be the same as when the report was delivered. It's got to be same.

I'm thinking in order to preserve the report data that I need to store the entire record for each on main form to 1 table "tblMR_Ex", and the entire record for each selected history record on the subform to another table "tblMR_ExHist". tblMR_ExHist would capture PK from the reported event, and the selected records from the history.

That's where my question about appending the download to 2 tables comes in? tblMR_Ex is only going to get new records from last 24 hours. tblMR_ExHist can't be based on that table, because an event that was used as history yesterday may be different today.

More info...

I do keep an updated table of all events (we call them exceptions, hence "Ex"). This table "tblExHist" is used for many things. However, everything about this report is a snapshot of the situation at that time.

Each morning a snapshot is taken of the previous 180 days. For each event which has occurred since 07:00Z yesterday, we identify it's history (and many other things) and provide this report to upper management. If we need to print a report that was created last week, it needs to be as reported last week. Whereas, if I use the updated tblExHist, the historical data would no longer be what it was when delivered last week.