Help Trying to create an access db and have excel update it.

[Danglez]

Hi all, I am an Access newbie and I am not too familiar with VBA, but I've been learning a bti as I've delved into this project.

I have created an Access table with 7 fields:
Transaction Date, Counterparty, Branch, Type, Amount, Interest Rate, Value Date & Maturity Date.

These are with regards to financial transactions and the overall goal is to be able to calculate cashflow for each day.

I have also created queries that calculate the cashflow for the following day.

What I am being asked to do is to create an excel spreadsheet that acts as a form to feed into the access table. At the same time, this spreadsheet must also have one cell that states the cashflow position at the beginning of the day, which was calculated using the query from the previous day's transactions.

Any insight would be helpful,

Thanks

 

[smandoli9]

I surmise that you have some Accountant-types who don't believe they're doing anything if they're not using the spreadsheet program. What a shame. It would be GREAT if you could sell them on an Access form.

Otherwise, you're in for some coding, and fairly complex from a starter's point of view.

_______________________________
_{Never confuse movement with action -- E. Hemingway}
_______________________________

 

[BNPMike]

Danglez

Access can easily do this but it's not clear what role Access is playing since you seem to be doing all the calculations in your spreadsheet.

It seems like you want to enter a day's transactions then send them to Access. Then either from Access or from the previous spreadsheet you carry forward the day's total to tomorrow's spreadsheet. So what would you want to do with the Access data - is it just an archive?

 

[Danglez]

The reason why the Access form is not wanted is because my superiors would like to leave the spreadsheet up and immediately enter in the nbformation, if even partially but have it in front of their eyes in a format they are used to.

The only calculation done in Excel would be the current cashflow position wish is a simple subtraction formula. The rest of the calculations would be done in Access, such as the next day's cashflow or for the next 30days.

So far I have thought of doing the following:
Manipulate their current spreadsheet to feed into a non formatted one that would be imported into access.

Run the calculations needed in access and then feed those numbers back into excel (although I only have a vague idea of how I am going to approach that (Using trnsferspreadsheet I believe).

 

[HunterOfTheShadows]

smandoli9 is right. You are in for some coding if your bosses don't want anything but excel. I used to work for an accounting firm as an IT mgr so I know what you are going through.

there are some things to consider. realize that when they enter information in excel and update the db it will create a record. have you thought about how you are going to report a specific date range back to excel? building this type of rpt in access is quite simple, but to link from the outside in is a different animal.

I know that non-technicals don't always consider the work that needs to be done, and sometimes don't care, but keep this in mind: "Change is difficult, but most of the time working around people's fear of it is exponentially more difficult."

I would consider how you would do whatever you need to do inside access and then linking from an excel spreadsheet. I think the difference in difficulty and TIME will convince you and them to seriously consider access. remember you can design a form any way you want. if you have office pro (w/access) you can have a shortcut on their desktops running through the local runtime. If you do not have office pro or access for everyone, you might consider purchasing the MS Office Developer's Edition where you can create your own .exe pkgs, then none of your users need access.

After I got my firm to change from spreadsheets to DB's, all the users loved how much easier it was looking at a form rather than a spreadsheet (all the lines hinder the eyes). And the reporting capabilities & speed of creating them were phenominal compared to excel.

I know this is not a solution, but I hope it helps you anyway.

 

[Danglez]

Thank you all for your thoughtful words, I have spoken to my superiors and called them out on being "accountant-types" which they found pretty funny. I was told that I can try to skip excel completely which is nice.

The ideal Access form for me would require the following:
1. In a spreadsheet format so that the records entered for that day are visible at all times while new ones are being entered.

2. A cell with an opening position. And a row that would display an updated figure as a new record is entered. Meaning if I start with 65 million and I enter a record in for 10 million I would see the position as 55 Million in the cell next to that record.

I would like to know if this is feasible and any pointers as to make this process easier for me would be appreciated. Otherwise I will start fiddling around with it now. I thank you all for your support and insight.

 

[HunterOfTheShadows]

Danglez,

here some ideas for your new found Access Freedom! (congrats)

1. When creating the form, choose datasheet view. this will make it look like excel. their eyes must be used to it!

2. create a field for your - Amt1, in your table. Use a qry to qry all fields on your table. In Qry Design View then add a field based upon Amt1 using a formula that will calculate based on Amt1 and the entry field. A-B=C.

OR

2. create the form and then add an unbound text box. in the ctrl source use a basic formula to calculate the amount the belongs. place it anywhere on the screen that you want.

--Good Luck