Expression in query calculating from Excell spreadsheet.

[Perfac]

Over more than 30 years business, I set up budget and cash flow projections on spreadsheets like in image 1 in Excel. I want our budget and projections to be part of our application. In our application there is one table that stores all transactions, one record after the other, with dates descriptions and amounts, and we are satisfied with the result. Image 2 shows a report. For the budget variance report to be correct, my challenge is to get the actual numbers into a field and the budget numbers next to it. I may manage to set up the budget looking like Image 1(in Access). I am not sure if I will be able to get an expression to set up the report. I must select "from" and "to" dates, when printing the Budget Variance report. Is it possible to set up an expression in a query that return number from an Excel spreadsheet?

[June7]

Surely Image 1 is not the raw data you want to filter? That data does not even show date values.

What do you mean by "return number from an Excel spreadsheet"? If you have data in Access, why would Excel be involved?

[Perfac]

Hi, thank you. Assume that I will put in date values in the field headings on the Excel sheet. The result I want is calling up a report that compares actual numbers with budget numbers. As example "Electricity" on the spreadsheet calculate to 4000 or the first 4 months. Access stores transactions(records) with dates, descriptions and records one after the other, and we manage to set up expressions as we want it. If we posted the budget transactions in a similar way we do the actual ones it will work. I set up a table "Budget Scenarios" because if one scenario changes it adjust the whole budget. If budget transactions are posted like actual ones, I fall short of insight how to connect it to "Budget scenarios" in a way that the budget get adjusted. If I do the budget on a spreadsheet I have insight how to connect it to "Budget scenarios".
Questions
a. Can an expression in a query return amounts from an excel spreadsheet?
b. Assuming the fieldname in Access can not be a date, it can not be used to calculate numbers for a certain period?

[Gicu]

You can link an Excel file to your Access app and you will be able to use it in queries. But you should really try to eliminate the need for it by doing the budgeting in Access (you can present it like your Excel by using a cross-tab query).

Cheers,

[June7]

a. Yes, Access can link to simple Excel sheets and work with them like tables - as sources for queries with calculations

b. Cannot filter data presented in image by date range

When building a relational database, forget everything you know about Excel. Start from scratch. Learn relational database principles, Access functionality, SQL, programming concepts, VBA language.

Store data in normalized structure and manipulate with queries and code to get desired output.

[Perfac]

My wish for our app as far as budget concern must include:
Firstly: Variable budget for testing.
1. To be part of our app without importing or linking to spread sheets(excel).
2. A variable budget(resulting query), not editable system linked to Scenarios(separate Scenario table and query, editable).
3. There may be many more than 50 items that influence the budget, that will be found in the Scenario table. Quantities, Percentages, Amounts, Dates, Maximums, Minimums.
4. The budget must be able to project for at least 60 months, and return a report for any single month or multiple months required.
5. This budget is for testing, and not used for report purpose.

Secondly: Actual Fixed budget, editable to be used for reports, including to compare with actual figures.
1. If the budget above was tested and approved it must append to this different table. No Excel.
2. Fields must be editable, not connected to scenarios. Every form in our app has an enable button and a password needed to edit existing information.
3. Cross-Tab queries will help to return reports as in the attached excel spreadsheet, reports are not my current challenge.
4. This table will store a very high quantity of records if the business use it for say 30 years.

Will write a separate thread trying to clarify my challenge. Hoping or strategical advice which I fall short on.

[Perfac]

Challenges
My assumptions or insight that may be wrong or falling short.
1. If you look at the attached excel spreadsheet with the supporting sheets, that works well for me.
2. I assume that in both cases of my previous thread, I will need to capture one record for every month, for every budget subject, including quantities, percentages, amounts.
3. The way I set up my query for the Variable budget, my experience is the expressions are far too many for one query.
4. I do put in effort to obtain tutorials to learn more, but I think I fall short on strategy here.

[Perfac]

Its been more than 5 years since we started to build and use our app. I only learnt today how to link to an excel sheet, simple. I need to learn how to do expressions in other queries returning numbers from the linked sheet. In my query "BudgetVariance" there are two fields "From Month" and "To Month". If I select Mar18 to Jun18 for the report, bank charges should return 8400. I need help with the expression or VBA.

[Minty]

Your problem will be that you have dynamic field names, due to the un-normalised structure.

If your data was vertically stored e.g.

BudgetSubject, Date, Amount

It would be so much simpler

[Gicu]

Please have a look at the attached sample for a custom VBA function. You can also use a union query to normalize your budget table putting them in a format similar to what Minty showed you above (see example in sample).

Cheers,
Vlad

[Perfac]

It seems that because the many expressions in a query are too much for Access, we will not be able to set up the Budget like I want to, stated in note #6 above. In excel I transposed the info(swapped the columns and rows around), that works. On the form a from date and to date can be selected, which means VBA must recalculate the result. The dates are in text fields on the form, not in a table or query. Let us say there are 50 budget items to be recalculated. Currently Access recalculate one record at a time as I open them on the form. I need the whole query to recalculate when a different date is selected. I have used refresh, it doesn't recalculate the whole query.

[Gicu]

You need to use Me.Requery in the AfterUpdate event of both Start and End Date textboxes.

[Perfac]

Yes I have tried that, but the expressions that need to be recalculated are in VBA on the form. They don't recalculate, they would have if the expressions were in the query. Is it also your experience that expressions in VBA take less time to calculate than expressions in queries? Even though I am busy testing a different strategy, I will still want to learn the answer to this thread.

[Gicu]

Can you please show the form in design view? I assume you mean you used the custom function I've sent you in the control source of a textbox, is that right? Expressions using built in functions should be faster then the ones using custom VBA functions if that is what your asking.

Cheers,

[Perfac]

I am not sure why the total in your query add to 8000, shouldn't it be 8400? My 23y.o. son had 3 years training in college, he works far away now. I trained myself during the past 5 years, having to run a business as well. Our development is doing a good job for us, but this budget section I fall short on strategy. Even though I used quite a few Modules, and no real training on VBA, I understand most of the time, the help is worth a lot. I will test what you suggest.

In excel I transposed the information like on the image. It took a few hours, since I copied cell by cell. That is not what I really want (see quote #6) because if a budget item is to be added, it is quite a bit of work. On the image you will see cumulative numbers. When I select dates on the form let us say month 4 to month 12 the expressions would lookup the amount in month 12 less the amount in month 3. But I am not too happy with that either, will do it differently.

Example of an expression in VBA. Me!AdminFee = IIf(Me.T005 = 1 And Me!EntityName103 = "AdminFees", DSum("AdminFees", "Excel03", "MnthInBus=" & Me.T009), IIf(Me!EntityName103 = "AdminFees", DSum("AdminFees", "Excel03", "MnthInBus=" & Me.T009) - DSum("AdminFees", "Excel03", "MnthInBus=" & Me.T006), 0))