Breakup a Table

[jobrien4]

A customer provides a forecast in a table which is not formatted how I like it. They have a table in which column 1 has the Part. Then they have a separate column for each week for the rest of the year. The fields show the pieces (if any) for that part that are due that week.

Giving me the data this way makes it difficult to do things with. I would much rather see each each field as its own record.

For instance, now it shows:

Part | 2/23 | 3/1
123 | 5 pcs | 10 pcs
456 | 0 pcs | 5 pcs

And I would like it to see it as:

Part | Pieces | Due Date
123 | 5 | 2/23
123 | 10 | 3/1
456 | 5 | 3/1

Is there any way to quickly reformat that? I'm open to options in Excel as well.

 

[plog]

Is there any way to quickly reformat that?

No. You will need to write a parsing script, something that reads in the data as it is now and spits it out to the right structure. Gonna involve recordsets, looping and INSERT statements.

 

[jobrien4]

Thanks for quick response. I'll have to figure something else out

 

[spikepl]

A macro in Excel.

Copy column 1 and Column X
Paste at the bottom of sheet in first column
Add 3rd column containing header of column X
Repeat for next column

 

[jdraw]

Have you considered asking if they could change their format to simplify the interaction with your database/system?

 

[jobrien4]

Have you considered asking if they could change their format to simplify the interaction with your database/system?

I can ask but my experience has been that multi-billion dollar corporations aren't very receptive to requests for changes to IT systems by relevantly tiny suppliers :banghead:

 

[CJ_London]

I'll have to figure something else out

it's not that difficult

Assuming you have either imported the data to a table or linked to it - I'll call it tblSource, and your destination table tblDestination

put this code in the onclick event of a form button

 dim I as in integer
 dim rst as dao.recordset
  
 set rst=currentdb.openrecordset("tblSource")
 if not rst.eof then
     for I=1 to rst.fields.count-1 'field 0 is the first column and same for all appends
         currentdb.execute "INSERT INTO tblDestination (Part, Pieces, DueDate) VALUES (" & rst.fields(0) & ", " & rst.fields(I) & ", " & cdate(rst.fields(I).name) & ")", dbfailonerror
     next i
 end if
 set rst=nothing

I've assumed that the column name for the duedate is a full date value so you may need to tweak it a bit

 

[CJ_London]

revisited this this morning and realised an error

the execute line should be

currentdb.execute "INSERT INTO tblDestination (Part, Pieces, DueDate) SELECT [" & rst.fields(0).name & "], [" & rst.fields(I).name & "], " & cdate(rst.fields(I).name) & " FROM tblSource", dbfailonerror

as before, the duedate column may need some work