Create Table

[Geoff Codd]

I have a table of data with a column for the date followed by 48 colums for the half hour intervals for the day. what I need to do is to place this in a new table with only 2 columns 1 for the date and time and the second for the value. any ideas anyone

thanks
geoff

 

[dcx693]

and the second for the value

What do you mean by the value? One of the 48 columns?

 

[Geoff Codd]

instead of having the value in the each of the 48 time columns, the table would have one column for the date and time and a second for the value of the time column

 

[dcx693]

one column for the date and time and a second for the value of the time column

Sorry if I'm not understanding you, but if you have "one column for the date and time" and "second for the value of the time column", dont you have the time stored in two separate places? Is that what you want? Perhaps you can post some sample data with your desired output.

 

[Geoff Codd]

Here's a sample of what I require, should make sense now

thanks
geoff

 

[billyr]

I looked at the Excel spreadsheet you attached. Do you want an Access table to duplicate the columns labled "To This"?

 

[Geoff Codd]

I currently have a table in access show as "change from this", which i need to change "to this".

I showed it in xl as that was the easiest way

thanks
geoff

 

[billyr]

OK first go back to Excel and copy and paste the "To this" including the column labels to sheet2. In access use "Get External Data", "Import". Select sheet2 to import. Access will create the table and populate it with the data from the "To This" columns (sheet2).

 

[Geoff Codd]

I think you have misunderstood me I don't have the data in xl it is in an a2k table, I have attached a db with 2 tables tbl1 is the data I currently have tbl2 is how I want the data to look.

Thanks
Geoff

 

[Rich]

The values in the spreadsheet to this don't match the existing records, is that a mistake or deliberate?

 

[billyr]

Geoff, I looked at both tables. Tbl2 looks like it does what you want. Help me understand your problem. Is the data in tbl2 just hand entered samples and you want to move records from tbl1 to tbl2? Is it a data entry format or form for tbl2? Whatever the specific problem is, its solvable.

 

[Geoff Codd]

I have created tbl2 by hand just to give you an example of what I need. tbl1 is actual data which I need to convert into the format as shown in tbl2

thanks
geoff

 

[billyr]

Progress!

And now I need help. I made a form with a cmd button and put the following code behind the button. I can't get the time part of the date/time field to work. Almost there. HELP (and thanks in advance for Geoff and me).

Private Sub Command0_Click()
Dim dbs As Database, rst1 As Recordset, rst2 As Recordset
Dim i As Integer, timevar As Variant
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("tbl1")
rst1.MoveLast
rst1.MoveFirst
Set rst2 = dbs.OpenRecordset("tbl2")
Do While rst1.EOF = False
For i = 0 To 47
With rst2
.AddNew
!DataSetID = rst1.Fields(1)
!value1 = rst1.Fields(5 + i)
timevar = CDate(rst1.Fields(5 + 1))
!DataDate = Format(rst1.Fields(3) & timevar, "mm/dd/yy h:mm")
.Update
End With
Next i
rst1.MoveNext
Loop
Set rst1 = Nothing
Set rst2 = Nothing
Set dbs = Nothing
End Sub

 

[Jon K]

[b]I can't get the time part of the date/time field to work.[/b]

To reference the time field names 00:30 etc, you need to use .Name. I have slightly modified your DAO code as follows.

Private Sub Command0_Click()
   Dim dbs As DAO.Database
   Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
   Dim i As Integer, timevar As Variant
   
   Set dbs = CurrentDb
   dbs.Execute "Delete * from tbl2"

   Set rst1 = dbs.OpenRecordset("tbl1")
   Set rst2 = dbs.OpenRecordset("tbl2")
  
   Do While rst1.EOF = False
     For i = 0 To 47
       With rst2
         .AddNew
         !DataSet_ID = rst1.Fields(1)
         !value1 = rst1.Fields(5 + i)
      
         If rst1.Fields(5 + i).Name = "24:00" Then
          ![Date] = rst1.Fields(3) + #11:30:00 PM#
         Else
           timevar = CDate(rst1.Fields(5 + i).Name) - #12:30:00 AM#
           ![Date] = rst1.Fields(3) + timevar
         End If
         .Update
       End With
     Next i
     rst1.MoveNext
   Loop
   Set rst1 = Nothing
   Set rst2 = Nothing
   Set dbs = Nothing

End Sub

 

[Geoff Codd]

Thank you both, this works a treat only a few lines of code to do something that seems so difficult

 

[Geoff Codd]

just one other thing how can I specify a particular dataset and or time frame

thanks
geoff

 

[billyr]

Was this conversion we just finished a 1 time deal? It makes a differrence.
A form with some text boxes will do the trick either way. Oh what a dummy(me). I was using the .name and somehow deleted it trying to find a format scheme that would work. I learned how to create a date/time data string. Good post!