473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Append Selected Records to Another Table

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
3 7993
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: Jonathan Buckland | last post by:
Can someone give me an example how to append data without having to load the complete XML file. Is this possible? Jonathan
2
by: JMCN | last post by:
hi i have a general question regarding append queries in access 97. each week i need to update my table(tblonlinereg) with new or modified records. firstly, i import the text file into my...
1
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to...
2
by: Steve B | last post by:
I am trying to design a Call Log Detail form that will allow users, in a multi-user environment, to select various data/records, not necessarily in sequential order, from a subform that will, when...
2
by: Mark | last post by:
I have a FE/BD 2002 DB on a XP pro platform. I know this is ugly but it works for me...... A text file is produced from our Oracle WMS. (Average 20k records) A command button deletes all records...
6
by: deejayquai | last post by:
Hi I'm attempting to append multiple values into a new record, using multiple criteria from a listbox. I've got the basics for the code below but I get an "Error 3085 Undefined Function" for...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
7
by: wade.wall | last post by:
Hi all, I am having a problem appending data to an existing table. I have two tables with identical fields and I want to append the data from one table (T2) to the first (T1). T1 has 136...
1
by: chaurous | last post by:
hi guys, i have a problem of building a function and from the result i should append the results to another table. function required: my first idea is query but it is not working. how should...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.