Data manipulation

[Peter Bellamy]

Please help me get my head around this !
Access 2003, tables are all lnked (across a network).

I have produced a table from data in a spreadsheet so it is table of data in rows under column headings.

It takes the form of:
Part No, 01/06/07, 01/07/07, 01/08/07, 01/09/07 (header)
itemno, qntydate1, qntydate2, qntydate3, qntydate4 (data)

To enable it to be 'read' in to another database (a purchased stock control program) the designeers have requested we provide the data in the following form:
PartNo1, 01/06/07, 123
PartNo1, 01/07/07, 234
PartNo1, 01/08/07, 345
PartNo1, 01/09/07, 456
PartNo2, 01/06/07, 987
PartNo2, 01/07/07, 876
PartNo2, 01/08/07, 765
PartNo2, 01/09/07, 654

And so on.
The dates are always the first of the month and start with the next month and run forward 4 months.

We will have to convert the data this way every month, so it will have to be simple process.

Can anyone please suggest how I can easily do this!

Cheers
Peter

 

[modest]

Easiest way, use 4 new insert queries:

Create a new table (PartNum, Date, Whatever that number is)

INSERT INTO NewTbl(PartNum, Date, WhateverNumIs) VALUES (SELECT itemno, qntydate1, wheverthatnumberis FROM CurrentTable)
INSERT INTO NewTbl(PartNum, Date, WhateverNumIs) VALUES (SELECT itemno, qntydate2, wheverthatnumberis FROM CurrentTable)
INSERT INTO NewTbl(PartNum, Date, WhateverNumIs) VALUES (SELECT itemno, qntydate3, wheverthatnumberis FROM CurrentTable)
INSERT INTO NewTbl(PartNum, Date, WhateverNumIs) VALUES (SELECT itemno, qntydate4, wheverthatnumberis FROM CurrentTable)

 

[Peter Bellamy]

Thanks for your reply.
I think it is a little more complicated than your answer allows for.

The dates are in the header, ie the field names of the table. They are not in the records!


Peter

 

[modest]

Then use VBA.

Dim strSQL     As String
Dim rs As      DAO.Recordset
Dim db As      DAO.Database

Set db = CurrentDB
Set rs = db.OpenRecordset("Current Table Name",dbReadOnly)

rs.MoveLast
rs.MoveFirst

Do Until rs.EOF
    strSQL = "INSERT INTO [NewTbl]([PartNum], [Date], [WhateverNumIs]) VALUES (SELECT [itemno], #" & rs.Fields(1).Name & "#, [wheverthatnumberis] FROM [CurrentTable])"
    DoCmd.RunSQL strSQL
    strSQL = "INSERT INTO [NewTbl]([PartNum], [Date], [WhateverNumIs]) VALUES (SELECT [itemno], #" & rs.Fields(2).Name & "#, [wheverthatnumberis] FROM [CurrentTable])"
    DoCmd.RunSQL strSQL
    strSQL = "INSERT INTO [NewTbl]([PartNum], [Date], [WhateverNumIs]) VALUES (SELECT [itemno], #" & rs.Fields(3).Name & "#, [wheverthatnumberis] FROM [CurrentTable])"
    DoCmd.RunSQL strSQL
    strSQL = "INSERT INTO [NewTbl]([PartNum], [Date], [WhateverNumIs]) VALUES (SELECT [itemno], #" & rs.Fields(4).Name & "#, [wheverthatnumberis] FROM [CurrentTable])"
    DoCmd.RunSQL strSQL
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

 

[Peter Bellamy]

Thanks again for your help.
SQL is new to me, I have just glimpsed it when building queries!

I see how most of this code works, and found the missing object for the DoCmd.runSQL command, but it falls over on the SELECT data section.

Please see the attached I built to test the code.

Peter

 

[modest]

Here is the code and the attached database.

Realize that the destination month field is a Date datatype, so a date must be given (it would have been easier if it was a text datatype and we could just insert the field's name). This meant that we had to force the date to be the 1st of each month and the year is always going to be the current year the sub is run.

On Error GoTo Err_Dataconvert_Click

    Dim strSQL      As String                   'Stores SQL string to update
    Dim rs          As DAO.Recordset            'Table object
    Dim db          As DAO.Database             'Database object
    Dim iFldNum     As Integer                  'Field number for recordset retrieval
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Promonth 2 Herschel", dbReadOnly)
    
    rs.MoveLast
    rs.MoveFirst
    
    DoCmd.SetWarnings False                     'Stops warning message from popping up when updating
    Do Until rs.EOF
        For iFldNum = 4 To 8 Step 1
            strSQL = "INSERT INTO [Promonth Import File](" & _
                        " [promonth_partno]" & _
                        ",[promonth_date]" & _
                        ",[promonth_qnty]) " & _
                     "VALUES (" & _
                        "'" & rs.Fields("CodeNo") & "'" & _
                        ",#" & Format(CMonthNameToNum(rs.Fields(iFldNum).Name) & "/1/" & Year(Now), "mm/dd/yyyy") & "#" & _
                        ",'" & rs.Fields(iFldNum) & "')"
            DoCmd.RunSQL strSQL
        Next
        rs.MoveNext
    Loop
    DoCmd.SetWarnings True                      'Turns warning messages back on
    
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

Exit_Dataconvert_Click:
    Exit Sub

Err_Dataconvert_Click:
    MsgBox Err.Description
    Resume Exit_Dataconvert_Click
    
End Sub


Public Function CMonthNameToNum(MonthName As String) As Integer
    Select Case Left$(MonthName, 3)
        Case "Jan": CMonthNameToNum = 1: Exit Function
        Case "Feb": CMonthNameToNum = 2: Exit Function
        Case "Mar": CMonthNameToNum = 3: Exit Function
        Case "Apr": CMonthNameToNum = 4: Exit Function
        Case "May": CMonthNameToNum = 5: Exit Function
        Case "Jun": CMonthNameToNum = 6: Exit Function
        Case "Jul": CMonthNameToNum = 7: Exit Function
        Case "Aug": CMonthNameToNum = 8: Exit Function
        Case "Sep": CMonthNameToNum = 9: Exit Function
        Case "Oct": CMonthNameToNum = 10: Exit Function
        Case "Nov": CMonthNameToNum = 11: Exit Function
        Case "Dec": CMonthNameToNum = 12: Exit Function
    End Select
End Function

 

[Peter Bellamy]

Thanks yet again, that looks really neat.
I will download and run it and see if I can fully understand how it works!

Peter

 

[Peter Bellamy]

Thanks again for the code, it works well and is easy to understand !
I have altered the date format as we have dmy in England but I have a couple of questions.
Why do you have:
rs.MoveLast
rs.MoveFirst
Is this to gaurantee it always starts at the first record?

Also, I am running this on a split database, data on a server, forms etc in a front end on local machines.
Will CurrentDb see a linked table or will I have to substitute it with the path and name of the db on the server?

Cheers
Peter

 

[Peter Bellamy]

I have had a chance to run and tinker with the code and I still have one more problem!
The final file has to be a CSV text file so I have added a maro and lines in the routine to produce this.

For some reason the date is appearing with the year as 2007 when I have used yy in the formay and with the time, 00:00;00 in the text file?
Which I do not understand as the code uses Year(Now) ?

Do you have any suggestions?

Cheers
Peter

 

[modest]

Why do you have:
rs.MoveLast
rs.MoveFirst
Is this to gaurantee it always starts at the first record?

MoveLast forces all records to be loaded in the recordset before the code continues. If your tables are large it could be that the system is still retrieving records from memory while the program goes on with next line of code, which can lead to unforeseen errors and incorrect data. Once the recordset is finished loading, the pointer is on the last record of the recordset.

MoveFirst points to the first record of the recordset. Programmatically you could remove this and go from the last record to the first record, but I put the MoveFirst in there purely for logistical reasons - it's easier for me to think as a programmer starting from the first record and moving to the last, rather than the last to the first. Doing this is actually adding an unnecessary step, which when you're in a bind for performance, you should remove.

Will CurrentDb see a linked table or will I have to substitute it with the path and name of the db on the server?

Your linked tables are local, which the CurrentDb will see. Think of it as a pointer to the foreign database, which can do almost everything the backend database can do. The current database manages the connection.

For some reason the date is appearing with the year as 2007 when I have used yy in the formay and with the time, 00:00;00 in the text file?
Which I do not understand as the code uses Year(Now) ?

The format() that I used was not actually storing the data as that format in the table. I was mainly putting it in there in case you were going to convert the date field to a text field (the formatting would hold as a text field). If you wish to add a particular formatting when exporting, you will have to use the format() function when exporting the data, or add a format to the field in the table's design.

 

[Peter Bellamy]

Thanks for the detailed explanation.

One more question, I hope!
I have added the code to export the table to a csv using:

strSpec = "Promonth Import File Export Specification"
trFile = "Promonth Import File"

DoCmd.TransferText acExportDelim, strSpec, strFile, "Promonth Import File.txt", True

My PC is setup with UK regional settings, the Specification File states 'dmy' with 4 digit year unchecked, yet the output is still mmddyyyy !

I tried to run it on my home PC but it will not let me examine the export spec' as it wants a missing wizard, yet they are all installed !

Puzzled.....again.

 

[modest]

Try making a query for the export, instead of exporting directly from the table. Use the Format() function on the date field in the query.