Database Design Suggestions Badly Needed!

[gestaltphoenix]

Ok, heres the deal...

I have 3 different Excel reports coming from our SAP database that I need to merge into 1 customized report. In other words, with reports A,B, & C, I need to to merge parts of B&C into A. All 3 reports have a particular column (Material Codes) that need be merged with respect to.

To make it (hopefully) a little clearer...
Report A has material codes with descriptions, selling price, etc...report B has repair prices that need to be matched with the material codes from A. Report C has usage data that needs to be associated with the material codes. They all have to be in one big happy spreadsheet to send out.

Considerations...
1.) I need to automate the process (I was thinking to use an access form which allows a user to type in file paths, and then scripting a macro to do the actual merge).

2.) The reports come out of SAP in a format that is difficult for Access to import and maintain data integrity (as in the field name for a given column takes up cells A1 and A2, and access can only use one row for field names).

I can record a macro that changes formats in Excel, and then import those files into Access in two seperate steps, but ideally I want it to be a streamlined process. I'm leaving soon and want to have remaining group members be able to preform this whole function after I go (i.e. type in file paths, click "ok" and they're done).

So....I'm open to suggestions...I have some scripting experience, but I'm no guru....

Thanx for taking the time!!!!

 

[Bastien]

Hi

Are you sure the Column name takes two fields, that it is not just spilling over into the second column? (if it is just spilling over then the field is still in column one)

Access is a good choice, you might even want to see if you can access the data directly from the SAP application through the table linking property in Access. This makes the whole thing wickedly easy. Check it out.

If not then I suggest that, assuming that the reports are delivered to the same place each time they are created, you hardcode the path to the excel files, and automate the import. Set the primary keys to the the materials code as hopefully it is on all three spreadsheets. Then just write a query to take the required fields from the three tables and export them to anothe excel file.

There will be a lot of VBA code to make this sucker go, but it can be done.

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[gestaltphoenix]

Bastien,

Many thanx for the speedy reply...

It is definitely two rows....its not a spillover...
(i.e. A2="Material" A3="Code&quot If I'm going to do this in Access, that is still a big problem....If it truncates the titles by taking the bottom cell only, then fields like PEC CODE, MATERIAL CODE, and SALES CODE would theoretically all come out as "CODE"....(

I'll check into table linking...I don't know if I have the SAP permissions, but I'll see how that goes.

Also, I know this isnt an Excel forum, but...
I can do the format and merges easy enough in excel, but the automation part gets harder...Any way to do this all in excel??

Thanx

Justin (gestaltphoenix)