Internal Audit hammering me! Help!

[holgi123]

Hi there,

not yet desperate but in huge help. We made an aquisition and got a bad legacy system

I got out of a legacy system a file which I got into Access and it looks like this:

Col1 Col2 Col3 Col4 Col5 Col6
1 05.05.04 $20
2 03.07.05 $30
3 PCN007 0
4 01.03.03 $50
5 05.09.06 $90
6 PCN003 0

It is an inventory file and has transaction data. There are only two thing for absolutely certain:
# Whenever col2 has something that DOES NOT look like a date, than it is a part number.
# That part number (in above PCN007, PCN003) is always the last row for that item. I need to convert the table like this:

Col1 Col2 Col3 Col4
1 PCN007 $20 05.05.04
2 PCN007 $30 03.07.05
3 PCN007 0 Empty
4 PCN003 $50 01.03.03
5 PCN003 $90 05.09.06
6 PCN003 0 Empty


The big question is how do I do this???
Any help would be greatly appreciated.

Thank You in advance.

Cheers

 

[Craig0201]

Excel and a few formulas.

 

[rjoubert]

Try this...

1. Create a copy of your source table (structure only, not data).

2. Modify the copy and change Col1 to be an autonumber field.

3. Paste the following code into a module.

Public Sub FixData()

    Dim rstSource As DAO.Recordset
    Dim rstDest As DAO.Recordset
    Dim tempArray(99) As String
    Dim tempArray2(99) As String
    Dim i As Integer
    Dim j As Integer
    
    Set rstSource = CurrentDb.OpenRecordset("MyTable")
    Set rstDest = CurrentDb.OpenRecordset("MyTableCopy")
    
    rstSource.MoveFirst
    
    Do While Not rstSource.EOF
        i = i + 1
        tempArray(i) = rstSource("Col3")
        
        If Not IsDate(rstSource("Col2")) Then
            For j = 1 To 99
                If tempArray(j) = "" Then
                    Exit For
                Else
                    rstDest.AddNew
                        rstDest("Col2") = rstSource("Col2")
                        rstDest("Col3") = tempArray(j)
                        rstDest("Col4") = tempArray2(j)
                    rstDest.Update
                End If
            Next j
            
            i = 0

        Else
            tempArray2(i) = rstSource("Col2")
        End If
        
        rstSource.MoveNext
        
    Loop
    
    Set rstSource = Nothing
    Set rstDest = Nothing
    
End Sub

4. Call the FixData() method.

Note: As long as there is no more than 99 records per than PCN number, this should do the trick.

Good Luck!

 

[rjoubert]

Additional Note: You will need to modify the code a bit to change the name of your source table...I've called it "MyTable" in the code. And whatever you name the copy, you'll need to use that name in place of "MyTableCopy" in the code.

 

[JerryKlmns]

The other way would be to create 2 new files, ready to be imported to the access db.
Read the original file line by line,
Determine the IsDate logic
TRUE--> Write the values to File#2 plus an incrementing number
FALSE--> Write the part number to File#1 plus this incrementing number

The incrementing number changes when you write a part number and starts at 1

So
File#1
1 PCN007
2 PCN003

File#2
1 $20 05.05.04
1 $30 03.07.05
2 $50 01.03.03
2 $90 05.09.06

File#1 is the master and File#2 is the details, joined by the incrementing number. If you include the current date you have a PrimaryKey using incrementing number + current date in your global transactions table. Import the files and ...

 

[Golom]

Then there's some messy SQL

SELECT Col1
          , Col2
          , Col3
          , NULL As [Col4]
FROM Parts P
WHERE Col2 NOT LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]'

UNION ALL

Select P.Col1

        , (Select TOP 1 Col2 From 

             (SELECT DISTINCT Col1, Col2 FROM parts
              WHERE Col2 NOT LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]') As B

            Where P.Col1 < B.Col1
            ORDER BY B.Col1) As [Col2]
                 
        , P.Col3
        , P.Col2 As [Col4]

From Parts P

Where P.Col2  LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' 

ORDER BY 1

 

[holgi123]

Hi Everyone,

thank you to all who helped and got me some ideas. I could not solve the problem in Access but with the suggestions of using Excel to better prepare the data I finally got things in a workable shape and can now further use them.

Thanks again for everybody's help. Much appreciated.

Cheers

 

[holgi123]

Hi Everyone,

after having first an interim solution working and than got rjoubert approach working I now have several other files with the same problem.

However, there is one big file, where the sequence in Col2 is different. This time the partnumber is the 1st row and than followed by the date values.

I tried changing rjoubert code but it does not give me the desired result (or better I do not know how to do it).

Any additional help would again be much appreciated.

Regrds