Subtraction Function

[sabloomer]

I looked over the other threads about subtraction and I couldn't find one I thought I could apply to my problem. I have a spreadsheet that I am trying to split and have all of the data and data entry parts in Access and use an External Data Range to feed all of the formulas. I have it 99% done except that when I replaced the data entry sheet I thought all the formulas where based on the SUM function. It turns out that 1/2 of the formulas are based on subtraction. In Excel I have a column for each year and a Balance Sheet value. I made a table in Access with three columns, Year, Balance Sheet Type, and Amount. At first I thought that I could multiple by (-1) and add them but when I try that to -100, -50, and -30 I get 180 instead of -20. I need the users to be able to add a new record each year without having to make changes to the database or the Excel sheet. Can anyone offer any suggestions on how to subtract multiple records from each other?

My only thought was to write a function that opens a recordset, with all of the records for that type, and move through each record and subtract it from the previous. I would have to make sure the records were sorted in the right order to be able to do that. I can do this, but I was hoping that there was an easier way that didn't taking opening and closing so many recordsets.

Thank you for your time,

sabloomer

 

[MichaelRed]

mostly not clear


"Data Entry" shouldn't involve normally involve calculation. Using a spreadsheet -but in WHAT manner? All I can GUESS at from your discription is that you MIGHT be attempting to use Access FORMS to input data to an (Excel?) spreadsheet - and expect it to then generate a calculated value based on a formula? Why go there? Why not justr make it an Access app? Just put the input data into a form and set a control to indicate the 'calculation' desired, set up the form to 'trigger' hte calculation based on the entries (values and formula indicated).


I am guessing that you are more of a Spreadsheet user than a db type, so I might also suggest that at least Excel can implement "Forms", so you shouldn'y actually need Ms. A. at all,




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sabloomer]

Sorry for the confusion, let me see if I can explain better. I started with a spreadsheet that has about 20 sheets of extensive financial calculations. Most of the data, 99%, is already being dumped out of our ERP system that has very bad financial reports. That data is imported into Access and then Excel links (External Data Range) to that data and feeds all of the formulas on the different sheets. The type of calculations and reports, I think, lend themselves better to Excel. My issue is with that other 1% of data that requires data entry. I would rather hold that data in a database, I am more of a DB person, then Excel. When I moved the data into a table my query returned the right values for all of the Excel formulas that had been sums, but not for the formulas that had been subtracted. Is there a better way to perform the subtraction calculation then opening a recordset for each type and cycling through each record?

Thanks for your help.

sabloomer

 

[MichaelRed]

Excel, by default, would export the resulte (visible values) from any sheet, so the issue must be either in some detail of your routine, or -more probably- lie elsewhere in the process. A few issues you might check:

[tab]look at the sheet(s) before the import, do they reflect the 'correct' values?, or is there some problem at that point.

[tab]Check the actual import 'statement' for proper syntax.

[tab]Have you looked into adding the Excel Library to the Ms. A. app and just using the 'financial' functions from Excel directly in Ms. A? I (usually) find this much easier than 'linking'.

[tab]What part of the process defines which (Excel) function applies to the individual records? Have you THOROUGHLY checked this? An ACTUAL step through to show what is being dne?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sabloomer]

I think I am still confusing you. I want to keep the Excel part because all of the calculations are already done and are in the format I want. I agree that I could do everything in Acess, but it seems like Excel is a better fit for the style of reports I have. Being a database person it was not easy for me to say that. Here is a problem. In my spreadsheet there is a sheet that is used to keep year end totals for the balance sheet. Right now there are 4 coulmns. One is for the total and the other three for the last three years ending values. I entered the year ending values into a table. In Excel there were two formulas using this data. A1 = B1 + C1 + D1 and A2 = B2 - C2 - D2. In Access I was able to use the SUM function to arive at the same result as A1 = B1 + C1 + D1. I am asking what is the best way to get the same result as A2 = B2 - C2 - D2.

Thanks,

sabloomer

 

[MichaelRed]

I am still not 'getting it'. The formula 'looks like' an excel expression, not an Ms. A. function. The SUM function in Ms. A. is an Aggregate function in SQL, and would normally operate on a "column" (or field) in a query, not hte "row" syntax shown. Perhaps others are more attuned to the situation.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sabloomer]

YES! I have two Excel expressions that I am trying to find the Access counter-parts for. I am using the SUM function on a field to perform the same calculation in Access. A1 = B1 + C1 + D1 is the same as sum([Amount]). How do I perform the A2 = B2 - C2 - D2 calculation in a query? Can I even do that? Do I need a VBA solution?

Thanks for hanging in there,

sabloomer