Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Modules & VBA (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12)
-   -   [SOLVED] Help with text file trim and create 2 tables (https://www.access-programmers.co.uk/forums/showthread.php?t=300423)

lookforsmt 07-05-2018 12:13 PM

Help with text file trim and create 2 tables
 
2 Attachment(s)
Hi! all,
I have a unique problem, not sure if there is any solution for this.

I have a account statement in txt format which runs in quite number of pages, with plenty of spaces and empty rows but has a standard format across all pages. What i am looking is to get the Output result (refer attached snapshot)
The rest of the information in the txt file is not required.

Any help or suggestions to start this pls.

isladogs 07-05-2018 12:34 PM

Re: Help with text file trim and create 2 tables
 
Hi
It's not really a unique problem
Basically, you just need code to read selected parts of your text file.

I demonstrate one way of doing that in this sample database How to create a web version checker

In that, I first download a web page to a text file (which you can omit), search for a specified text string and read the section after that to a variable.
You just need to do that many times but the principle is the same.
In your case, you would need to update your two tables with each of the extracted values

Other people may have different solutions e.g. using the split function

plog 07-05-2018 12:43 PM

Re: Help with text file trim and create 2 tables
 
There's no solution other than choose a programming language and build your own text parser.

Google 'text parser tutorial'. Essentially you will write a program to open your source file, tell it how to identify the start of a new record, teach it how to extract out all the fields in that record and then what signifies the end of the record. You have it loop through that until it reaches the end of the file, then have it spit all the records it found out into a destination file. Then you import the destination file into your database.

Of course you have 2 tables you want to populate so that means doing the above twice.

MajP 07-05-2018 02:47 PM

Re: Help with text file trim and create 2 tables
 
I played with this a little to see what it would take and if it would be overly difficult. Often this depends on how well formatted the data is. I was able to do the first part with the sample data. It is not very bullet proof so it will depend on how truly common your format is. Basically I read it line by line. Then cleaned it up to remove a lot of special characters. Then checked the beginning of the line. Then used split functions to get the values I wanted. Then passed to an insert query. Needs some work, but appears to work

Code:

Public Sub ReadLineByLine(strFile As String)
' ***************************************************
' * Open a Text File And Loop Through It            *
' ***************************************************
  Dim intFile As Integer
  Dim strIn As String
  Dim strOut As String
  Dim strSegment As String
  Dim strMOM As String
  Dim strDepartment As String
  Dim TheSegment As String
  Dim ThePrintDate As String
  Dim ThePage As String
  Dim TheMOM As String
  Dim TheDepartment As String
  intFile = FreeFile()
 
  Open strFile For Input As #intFile
 
  Do While Not EOF(intFile)
    Line Input #intFile, strIn
    strIn = CleanField(strIn)
    If Left(strIn, Len("Segment")) = "Segment" Then
      strSegment = strIn
      'Debug.Print strSegment
      TheSegment = Trim(Split(strSegment, ":")(1))
      TheSegment = Trim(Split(TheSegment, " ")(0))
      ' Debug.Print TheSegment
      ThePrintDate = Trim(Split(strSegment, TheSegment)(1))
      ThePrintDate = Trim(Split(ThePrintDate, " ")(2))
      'Debug.Print ThePrintDate
      ThePage = Mid(strSegment, InStr(strSegment, "Page") + Len("Page"))
      ThePage = Trim(ThePage)
      'Debug.Print ThePage
    ElseIf Left(strIn, Len("MOM ID:")) = "MOM ID:" Then
      strMOM = strIn
      TheMOM = Trim(Split(strMOM, ":")(1))
      TheMOM = (Replace(TheMOM, "  ", ""))
      'Debug.Print TheMOM
    ElseIf Left(strIn, Len("Department Name:")) = "Department Name:" Then
      strDepartment = strIn
      'Debug.Print strDepartment
      TheDepartment = Trim(Split(strDepartment, ":")(1))
      TheDepartment = Split(TheDepartment, "  ")(0)
      'Debug.Print TheDepartment
    End If
    If TheSegment <> "" And ThePrintDate <> "" And ThePage <> "" And TheMOM <> "" And TheDepartment <> "" Then
      InsertRecord TheSegment, ThePrintDate, ThePage, TheMOM, TheDepartment
      TheSegment = ""
      ThePrintDate = ""
      ThePage = ""
      TheMOM = ""
      TheDepartment = ""
    End If
  Loop

  Close #intFile

End Sub
Public Sub testRead()
  ReadLineByLine "C:\Users\500989\Documents\Personal\AccessDatabasesVBA\Text Utilities\sample_text.txt"
End Sub
Public Function CleanField(strField As String) As String
  'turn spaces and nulls to empty strings
  CleanField = strField & ""
 
  ' CleanField = (Replace(strField, "  ", ""))
  'Hidden characters are ASCII 0 to 32 but these are the main ones. You can google the table
  'You can loop them all except 32 since that is a space which could be inside your string
  CleanField = Replace(CleanField, Chr(9), "")  'Tab
  CleanField = Replace(CleanField, Chr(10), "") 'Line Feed
  CleanField = Replace(CleanField, Chr(13), "") 'Carriage return
  CleanField = Trim(CleanField)
  'either leave anwer as "" string or change all to null
End Function
Public Sub InsertRecord(TheSegment As String, ThePrintDate As String, ThePage As String, TheMOM As String, TheDepartment As String)
  'Wrint an insert query here
  Dim strSql As String
  TheSegment = "'" & TheSegment & "'"
  ThePage = "'" & ThePage & "'"
  ThePrintDate = "'" & ThePrintDate & "'"
  '...
  strSql = "Insert Into Table1 (Segment,PrintDate,Page,MOM_ID,Department_Name) VALUES ( " & TheSegment & ", " & ThePrintDate & ", " & ThePage & ", ...)"
  Debug.Print strSql
  'currentdb.executeStrSql
End Sub


lookforsmt 07-05-2018 09:28 PM

Re: Help with text file trim and create 2 tables
 
Good morning all

Thank you for your suggestions and directions to take this forward. I did look over the net but this one is out of the world for me.

Dear Majp, i tried your code in my db, I am getting this error message:

The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.

* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

not sure what am i doing wrong.

MajP 07-06-2018 03:30 AM

Re: Help with text file trim and create 2 tables
 
I doubt that has anything to do with the code. More likely something to do with the form. That error means something on the form is named twice with the same name. You may be able to go into the vba window and hit compile to find the culprit. I would put that code in a standard module not the forms module. To test it put a button on a form. The click event should be

Code:

ReadLineByLine "C:\yourPath\sample_text.txt"
Where "yourpath" is the complete pass. The code does not actually do the insert query at this time. Did not finish, since just a demo of potential approach. But it does correctly get all the values as shown in your example and prints it out in the immediate window. So at least with the sample data it is a viable solution. However, the solution is based on the format of the data so you will only know how bullet proof it is by running a bigger test. Then if it breaks on certain data you will need to add more logic.

The good news if you get this to work the second table looks as easy or easier to make. For example I read line by line and check if "Rej Reason :" is in that line. Then I take that line and can split it a couple of ways to get S.Number, Instrument Number, Amount, and the reject reason.
Code:

1                1              1,000.00    Rej Reason : Sxxxxxxx Ixxxxxx

I will try to put a full working demo for both tables, when I get some time. I will include the file browser so you can click a button and browse for your files. Bottom line, if the rest of the files are formatted as consistently, this should all be easy to do.

lookforsmt 07-06-2018 03:53 AM

Re: Help with text file trim and create 2 tables
 
Thanks MajP for your response, yes you are right i had named the form same as he sub name so getting the error.

One thing i missed to mentioned, the text file uploaded is excatly the same as the original text file and it is pretty consistence in terms of space and format across the data.

I am never done this before therefore clueless on how to go about it. I appreciate your help in this.

I checked the immediate window and it shows the table1 result as i would like to.

lookforsmt 07-06-2018 04:10 AM

Re: Help with text file trim and create 2 tables
 
HI! MajP,

Besides the "Rej Reason :" I also wanted it to capture the next two lines in separate column/field

"Warning : "
"Exception :"

PHP Code:

1    1    1,000.00    Rej Reason Sxxxxxxx Ixxxxxx    Warning Mxxx Pxx Exxxxx      Exception :  Ixxxxxxxxxxx Axxxx 

Will the below be a field name or part of the text
"Rej Reason :"
"Warning : "
"Exception :"

Thanks again.

MajP 07-08-2018 08:50 PM

Re: Help with text file trim and create 2 tables
 
1 Attachment(s)
Here is a working demo. You can browse for a file then it will import into the two tables. To prove it works there is a button to clear out the tables first. It works with the demo text files, but you will have to try bigger data sets and see if it fails. Example: I did not add code if a data element is missing. If that is possible than I would have to account for that.

There is not a lot of "new" code here. I pulled the file browser code from old stuff I had and available on the internet. The code to read a text file can also be easily found on the internet. I also pulled some array code from Pearson's site.

So once you can pick a file and read it the trick is to look for something. You only have to find 2 things. "SEGMENT :" and "Reject Reason :" because once you find "Segment" you take the next 2 lines, and once you find Reject Reason you take the next three lines. You can use an "instr" function to search for these lines. Once you find the lines you want, the trick is to pull out the data elements. I used the "Split" function which lets you take a string and break it into an array based on any delimiter. A good one is ":" because the thing immediately after that is data. But I had to use several string functions. Once I found the data elements I pushed them to a procedure to do an insert query.

Attachment 71283
Attachment 71284

There may be a more eloquent way to do this, but seems to work fine for the test set.

Here is the code

This is the main part of the code. It reads line by line, sees if the line is what You are looking for. If the line is what you are looking for then use some string functions (mostly the split function). Once it gets all the data elements it passes to the insert queries.
Code:

Public Sub ReadLineByLine(strFile As String)
' ***************************************************
' * Open a Text File And Loop Through It            *
' ***************************************************
  Dim intFile As Integer
  Dim strIn As String
  Dim strSegment As String
  Dim strMOM As String
  Dim strDepartment As String
  Dim TheSegment As String
  Dim ThePrintDate As String
  Dim ThePage As String
  Dim TheMOM As String
  Dim TheDepartment As String
  Dim StrReject As String
  Dim strWarning As String
  Dim strException As String
  Dim TheS_No As String
  Dim TheInstrument As String
  Dim TheAmount As String
  Dim TheReject As String
  Dim TheWarning As String
  Dim TheException As String
  Dim TempArray() As Variant
  Dim I As Integer
  intFile = FreeFile()
 
  Open strFile For Input As #intFile
 
  Do While Not EOF(intFile)
    Line Input #intFile, strIn
  ' strIn = CleanField(strIn)
    If InStr(strIn, "SEGMENT :") > 0 Then
      strSegment = strIn
      Debug.Print strSegment
      TheSegment = Trim(Split(strSegment, ":")(1))
      TheSegment = Trim(Split(TheSegment, " ")(0))
      ' Debug.Print TheSegment
      ThePrintDate = Trim(Split(strSegment, TheSegment)(1))
      ThePrintDate = Trim(Split(ThePrintDate, " ")(2))
      'Debug.Print ThePrintDate
      ThePage = Mid(strSegment, InStr(strSegment, "Page") + Len("Page"))
      ThePage = Trim(ThePage)
      'Debug.Print ThePage
      'Next line is MOM ID
      Line Input #intFile, strIn
      strMOM = strIn
      TheMOM = Trim(Split(strMOM, ":")(1))
      TheMOM = (Replace(TheMOM, "  ", ""))
      'Debug.Print TheMOM
      'next line is department nam
      Line Input #intFile, strIn
      strDepartment = strIn
      'Debug.Print strDepartment
      TheDepartment = Trim(Split(strDepartment, ":")(1))
      TheDepartment = Split(TheDepartment, "  ")(0)
      'Debug.Print TheDepartment
      InsertSegment TheSegment, ThePrintDate, ThePage, TheMOM, TheDepartment
      TheSegment = ""
      ThePrintDate = ""
      ThePage = ""
      TheMOM = ""
      TheDepartment = ""
    End If
   
    If InStr(strIn, "Rej Reason :") > 0 Then
      StrReject = strIn
      TheReject = Trim(Split(StrReject, ":")(1))
      Debug.Print "Reject Reason: " & TheReject
      'Get a clean array with only items that are non blank
      TempArray = CleanArray(StrReject)
      TheS_No = TempArray(0)
      Debug.Print "S_No: " & TheS_No
      TheInstrument = TempArray(1)
      Debug.Print "Instrument " & TheInstrument
      TheAmount = TempArray(2)
      Debug.Print "Amount " & TheAmount
      'Move to next line
      Line Input #intFile, strIn
      TheWarning = Trim(Split(strIn, ":")(1))
      Debug.Print "Warning: " & TheWarning
      'Move to next line
      Line Input #intFile, strIn
      TheException = Trim(Split(strIn, ":")(1))
      Debug.Print "The Exception " & TheException
      InsertRejection TheS_No, TheInstrument, TheAmount, TheReject, TheWarning, TheException
      TheReject = ""
      TheS_No = ""
      TheInstrument = ""
      TheAmount = ""
      TheWarning = ""
      TheException = ""
    End If
  Loop

  Close #intFile

End Sub

Once I find the data elements I pass to these functions to do the insert query.
Code:

Public Sub InsertSegment(TheSegment As String, ThePrintDate As String, ThePage As String, TheMOM As String, TheDepartment As String)
  'Write an insert query here
  'Put your real table name below
  Const Table1 = "Table1"
  Dim strSql As String
  TheSegment = "'" & TheSegment & "'"
  ThePage = "'" & ThePage & "'"
  'If you want to save this as a real date will need some more logic
  ThePrintDate = SQL_Date(ThePrintDate)
  TheMOM = "'" & TheMOM & "'"
  TheDepartment = "'" & TheDepartment & "'"
  strSql = "Insert Into " & Table1 & "(Segment,Print_Date,Page,MOM_ID,Department_Name) VALUES ( " & TheSegment & ", " & ThePrintDate & ", " & ThePage & ", " & TheMOM & ", " & TheDepartment & ")"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub
Public Sub InsertRejection(TheS_No As String, TheInstrument As String, TheAmount As String, TheReject As String, TheWarning As String, TheException As String)
  'Write an insert query here
  'Put your real table name below
  Const Table1 = "Table2"
  Dim strSql As String
  TheReject = "'" & TheReject & "'"
  TheWarning = "'" & TheWarning & "'"
  TheAmount = Replace(TheAmount, ",", "")
  'If you want to save this as a real date will need some more logic
  TheException = "'" & TheException & "'"
  strSql = "Insert Into " & Table1 & "(S_No,Instrument_Number,Amount,Reject_Reason, Warning,Exception) VALUES ( " & TheS_No & ", " & TheInstrument & ", " & TheAmount & ", " & TheReject & ", " & TheWarning & ", " & TheException & " )"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

One way to get the data elements would be to read the string character by character and only take those things that did not have more than two spaces in them. I choose to split the string by a single space. Then I used this to create a new array with only those array elements that were not blank.
Code:

Public Function CleanArray(TheString As String) As Variant
  'Send in a string and only return the items that are non blank
  Dim I As Integer
  Dim counter As Long
  Dim TempArray() As String
  Dim TempArray2() As Variant
  TempArray = Split(TheString, " ")
  For I = 0 To UBound(TempArray)
    If Not Replace(TempArray(I), " ", "") = "" Then
      TempArray2 = AddElement(TempArray2, Trim(TempArray(I)))
      counter = counter + 1
    End If
  Next I
  CleanArray = TempArray2
End Function

Code from the internet dynamically add elements.
Code:

Public Function AddElement(ByVal vArray As Variant, ByVal vElem As Variant) As Variant
      ' This function adds an element to a Variant array
      ' and returns an array with the element added to it.
      Dim vRet As Variant ' To be returned
      If IsEmpty(vArray) Or Not IsDimensioned(vArray) Then
          ' First time through, create an array of size 1.
          vRet = Array(vElem)
      Else
          vRet = vArray
          ' From then on, ReDim Preserve will work.
          ReDim Preserve vRet(UBound(vArray) + 1)
          vRet(UBound(vRet)) = vElem
      End If
      AddElement = vRet
  End Function
Public Function IsDimensioned(ByRef TheArray) As Boolean
      If IsArray(TheArray) Then ' we need to test it! otherwise will return false if not an array!
                      ' If you put extensive use to this function then you might modify
                      ' it a lil' bit so it "takes in" specific array type & you can skip IsArray
                      ' (currently you can pass any variable).
        On Error Resume Next
            IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)
        On Error GoTo 0
        'FYI: Erros are not always generated by Ubound & LBOund
        '    when array is empty (when arrays are "made" empty in some "specic way").
        '    So we cant use usual "decide" logic: bool = (err.number <> 0))
        'ie.:
        '    str = VBA.StrConv("", vbFromUnicode) 'generally you should use strconv when
        '                                          you plan converting string to bytearray,
        '                                          (here StrConv actually not needed for "",
        '                                          it's length is 0 anyway)
        '    ByteArr() = str
        '    UBound(ByteArr) => -1
        '    LBound(ByteArr) => 0
        'but:
        '    Erase ByteArr
        '    UBound(ByteArr) => Causes Error
        '    LBound(ByteArr) => Causes Error
        ' NOTE: I'm not sure, but I'm guessing (based on OnErr0r "knowledge" - > http://www.xtremevbtalk.com/showthre...hreadid=105700)
        '    that "ByteArr() = str" will cause ByteArray point to SAFEARRAY in any case,
        '    even if "str" is empty while "Erase ByteArr" will remove that.
        ' QUESTION: can we make some how empty SAFEARRAYS for other types than
        '          ByteArrays as well????  I can't fiqure it out right now...
        '          Maybe doing something in low level...
    Else
        'IsDimensioned = False ' is already false by default
        Call Err.Raise(5, "IsDimensioned", "Invalid procedure call or argument. Argument is not an array!")
    End If
End Function

Regardless of your regional settings and any format, SQL needs dates in MM/DD/YYYY format for a proper insert

Code:

Public Function SQL_Date(strDate As String) As String
  'Turns a dd-mm-yyyy into a mm/dd/yyyy needed for a SQL insert regardless of regional settings
  '04-07-2018 becomes #07/04/2018# for the insert
  Dim aDate() As String
  aDate = Split(strDate, "-")
  SQL_Date = "#" & aDate(1) & "/" & aDate(0) & "/" & aDate(2) & "#"
End Functio

n

lookforsmt 07-09-2018 08:19 AM

Re: Help with text file trim and create 2 tables
 
Sorry for the delay, i just came from work and couldnt wait to respond.
Thanks MajP, for detailed explanation and solution. I simply don't have words to express myself. If i had to write this code it would have taken a light year to get this.
I seriously have to think of learning this code which can make anything happen.

I tested your db on the actual data consisting of 2416 pages and i got the below error. So reduced the pages to 1 and took data which had "Rej Reason" on the second line and still got the below error.

PHP Code:

Run-time error "9":
Subscript out of range 

I think this was because the field "Rej Reason : is sometimes on the 1st line and sometimes on the 2nd line. Also, other fields "Warning"; "Exception" can be on any line or in some cases they may be absent.

Is it possible to get the second table2 to read the line which can be either of the below:
PHP Code:

Exception :
Warning :
Rej Reason :

Below are which i did not see beforebut they also can be part of the lines
Error 

Warning :  (This one is duplicate but has different reason which is fixed "Instrument already entered through ICTM" 

What line of code do i change i need to add another column in the future in table1.

lookforsmt 07-09-2018 10:46 AM

Re: Help with text file trim and create 2 tables
 
1 Attachment(s)
HI! I tried to replicate another sample_text2 with the different scenarios. I tried to copy but it gave me the "Run-time error 9"

attached Sample_Text2
Thanks

MajP 07-09-2018 11:42 AM

Re: Help with text file trim and create 2 tables
 
As I expected, it is much more challenging then I thought.
In the original data I assumed everything will have a rej Reason, Warning, Exception. Like so:
Code:

Rej Reason : Pxxxxxxx Rxxxxxx
Warning :    Mxxx Pxx Exxxxx
Exception :  Ixxxxxxxxxxx Axxxx
Instrument Rejected

So the logic was very simple based on that. But the new data has all kinds of possibilities. I am going to have to add a lot more logic to see if those elements exist or not.
Two warnings:
Code:

Warning :    Mxxx Pxx Exxxxx
Warning :    Instrument already entered through ICTM
Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
Instrument Rejected

Or no Exception
Code:

Rej Reason : Sxxxxxxx Ixxxxxx
Warning :    Mxxx Pxx Exxxxx
Instrument Rejected

Or no reject reason, but an Error and no warning
Code:

Error :      C. I. DR TRAN TO DORMANT
Exception :  Ixxxxxxxxxxx Axxxx BAL EXCP
Instrument Rejected

Adding an extra column for Error will be easy. However, what do you want it to do if you have 2 warnings or 2 of something else? This will take a bit more than a single line of code change. However, this is what is normally expected when doing something like this. You have to code for all the possibilities. I will try to code this sample, but it code will continue to grow as you account for the possibilities. I will take a look when I get a chance.

lookforsmt 07-09-2018 06:54 PM

Re: Help with text file trim and create 2 tables
 
Thanks for your response,
I tried my best to get all the various scenarios from the 2600 odd lines. Just a thought, can the code look for any of the below lines if they match with the list in another table (table3) then put them in the respective fields in table2. This way if i encounter any error. i just need to add that line in table3. I may be wrong or may not be possible.

PHP Code:

Warning :    MXXX PXX EXISTS
Exception 
:  IXXXXXXXXXX AXXXXX BAL EXCP
Warning 
:    Instrument already entered through ICTM
Rej Reason 
Pxxxxxxx Rxxxxxx
Error 
:      CIDR TRAN TO DORMANT
Instrument Rejected                                
(If it reads this put in separate field)
            
No Exception or Warnings encountered   (if it reads this entire sentence then put in separate field as "No Exp or Wrng"


MajP 07-10-2018 04:05 AM

Re: Help with text file trim and create 2 tables
 
Yes. It is going to take a litlle more logic because you no longer know where to start and stop. The new logic will find this line to start looking
Code:

S.No Instrument Number  Amount              Error Description
Then the start of a record will be a line with the first character being numeric
Code:

1                1              1,000.00    Rej Reason : Sxxxxxxx Ixxxxxx
It will then look for any in any order
Quote:

Rej Reason, Error, Warning, Exception, Instrument Rejected, No Exception or Warnings encountered
It will do the insert when it finds either another record (a line starting with an S_No) or stops when it finds the line
Code:

---------------------------
Do you want the fields for:
Code:

Instrument Rejected                               
No Exception or Warnings encountered

To be yes no fields? Since they will be their own field.

Still not sure what you want to do when you have two of something like two Warnings. I can concatenate it like:

Code:

MXXX PXX EXISTS; Instrument already entered through ICTM
Also I am guessing there is something more not being accounted for. Don't you need table 1 and 2 related. I do not know your business rules but it looks like a test is run. That test is uniquely described by a composite key which is the combination of
Segment, MOM_ID, and Test
Then the second table are the test results. I would assume you need to relate those test results back to table one.

Would three tables make sense. I would think you have in table 1, The same fields and maybe a simple Primary Key (so I do not have to deal with a composite key)
Table 2 could just have S_No, Instrument_No, Amount, and a field to relate it back to table1. Table 3 would have all the error codes not in their own fields, but in a single fields of error codes and error code types. This would relate back to table 2..

Table 3 would look like
ErrorCodeType ErrorCode TestRunID_ForeignKey

lookforsmt 07-10-2018 10:36 AM

Re: Help with text file trim and create 2 tables
 
1 Attachment(s)
Thank you for your response. I will try my best to answer your questions. Kindly bear with me please.
PHP Code:

Do you want the fields for:
Code:
Instrument Rejected                                
No Exception 
or Warnings encountered
To be yes no fields
Since they will be their own field

Yes. This would be two fields in table2 "Instrument Rejected" and "No Exception or Warnings encountered" and if they are present in the line then should be a "Yes" in respective fields

PHP Code:

Still not sure what you want to do when you have two of something like two WarningsI can concatenate it like:

Code:
MXXX PXX EXISTSInstrument already entered through ICTM 

Yes, if it is concatenated would be good idea

PHP Code:

Also I am guessing there is something more not being accounted for. Don't you need table 1 and 2 related. I do not know your business rules but it looks like a test is run. That test is uniquely described by a composite key which is the combination of
Segment, MOM_ID, and Test
Then the second table are the test results. I would assume you need to relate those test results back to table one.
Would three tables make sense. I would think you have in table 1, The same fields and maybe a simple Primary Key (so I do not have to deal with a composite key) 

The above if i suggest in table1 if you can split the MOM ID into two fields which is "0001" and another field named as MOM TXT which could be the second part "ABC - Test1" then in table2 also if you can add MOM ID which is again "0001" then we can have a relation between the table1 & table2.
Then we don't need a composite key and a simple Primary key is enough to do the relationship.

Besides this i also wanted additionnal 2 fields in table2 which is below:
Reference Name: PQR
Reference : 1234567891023

Kindly refer to attachment:
a) table1 & table2 field names respectively
b) Relation between table1 & table2
c) table3 field names

PHP Code:

Table 2 could just have S_NoInstrument_NoAmount, and a field to relate it back to table1
Table 3 would have all the error codes not in their own fieldsbut in a single fields of error codes and error code types
This would relate back to table 2..

Table 3 would look like
ErrorCodeType ErrorCode TestRunID_ForeignKey 

In table3 does this mean that it would allow me to add error codes whenever i encounter new errors in the line. If this the functionality and my understanding correct.

Hope i have answered your queries. I thank you for doing this for my project and so grateful.


All times are GMT -8. The time now is 05:25 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World