Best way to setup

[griz47]

I'm new to access and I have been attempting to setup a series of queries to produce an output I can manually upload via excel into SAP. Basically, I am looking to produce output for a spreadsheet I can use to upload. I receive the data in one format in excel, I then need to upload this information to access, update some fields, potentially run some calculations and then produce a specific output to a new excel file. What would be the best way to set this up? I'm currently attempting to use a series of queries but so far I'm having difficulties getting the output I need. Any suggestions on a better setup? Thank in advance for any assistance!

[June7]

You need to be specific about the 'difficulties'. What is it you need to do in Access that can't be done with the data in the original Excel file?

[griz47]

I need to upload my initial spreadsheet to access, have it calculate incentives based on date range and company criteria, then produce a completely different output for upload into SAP. The initial spreadsheet I receive is not the same as the spreadsheet I need for my sap upload. This is standard for the company that sends it to me and they unfortunately cannot change it as they have other clients. I need completely different fields and other information the initial spreadsheet does not have.

The initial company send a spreadsheet with:

Amount, customer information, company it is intended for and terms of the payment.

I need to add:

incentives based on timing of payment and more specific company information based on the information sent to me initially. Also must rearrange to very specific field arrangements for upload to SAP.

I'm just looking for a more streamlined process of producing the spreadsheet I need for upload.

Thanks again for any feedback!

[June7]

Rearranging and renaming fields is easy enough within Excel, can even write VBA procedure behind Excel to accomplish. Is there data in Access that is not within the source Excel that is needed for the calculations? Excel VBA could 'retrieve' the needed info from Access. Depending on your data structure, might be able to do all from the Excel side. I have an Excel workbook that does something like this. It pulls raw data from Access and manipulates it to a structure that can be imported to a program called gINT. In your case the raw data starts in Excel. Whether you pass it through Access to perform the manipulations is dependent on the nature of the manipulation and if you need to retain manipulated data in Access db. I can post link to the workbook later if you are interested. Code would not run because the source db would not be available but you could examine the code.

[griz47]

Yes, I would love to see what you have to help me get on the right track. Thank you so much! I was not aware excel had those same capabilities. I guess I could have the regular upload cells, then have it update a different sheet. Thanks again

[June7]

Here is link to downloadable workbook. http://www.box.net/shared/iig01rg0ut
Some of the worksheets are hidden and protection might be on but there is no password, just hit Enter through the password prompt.

I believe all of the MS Office modules (Excel, Access, PowerPoint, Word, Outlook, etc) are VBA enabled.