Database Design Suggestions Needed 2

[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!!!!

 

[LittleSmudge]

If you can guarantee the SAP will always output the Excel files in the same format ( column order etc ) then you can import them into Access sans headings on a simple positional basis.

Then use VB code ( not macros ) to combine the three together

Assuming ReportA become tblA etc. then you can run a SQL command along the lines of :-


Dim strSQL As String
strSQL = "SELECT tblA.*, tblB.FieldB1, tblB.FieldB2, tblC.FieldC1, tblC.FieldC2 " _
& "INTO tblResult " _
& "FROM (tblA LEFT JOIN tblB ON tblA.MaterialCodes = tblB.MaterialCodes) " _
& "LEFT JOIN tblC ON tblA.MaterialCodes = tblC.MaterialCodes;
DoCmd.RunSQL strSQL

That will create a table called tblResult which has all of the fields from tblA and the fields from tblB & tblC that you specify - WHERE the MaterialCodes values match in each table.


HOWEVER, if a particular MarterialCodes value appears more than once in tblB or tblC then you've blown your Relational structure.
You'd be better of keeping the three tables and then using the SELECT statement above in the RecordSource of the report itself. You can then use Grouping and Sorting within the Report design to structure, group and tidy up the output.



'ope-that-'elps.

G LS

 

[gestaltphoenix]

'smudge,

Nice perspective....I think I've been staring at this for too long ...I think I can definitely use this!

Thanks for the help!

Regards,

gestaltphoenix

 

[LittleSmudge]

Glad to be of help - it's easy to get bogged down in the detail. One of beuties of this site is getting a fresh perspective on the problem.

G LS