Export to excel then import back to Access?

[MackMan]

Hi all.

I know this has been covered many times, but I'm really at a stumbling block with an idea on the dreaded budgets vs actual.

I'm after some guidance on how to possibly set up a table, where I can change the data in it (on a monthly basis) in order to compare a budget table against an actual spend table.

I have a load of questions, but I shant bombard with them all, so I suppose my first question would be...

I have ...

A table (tblCats) which holds all categories and sub categories.
A table (tblExpenses) that holds all expenditures for said Cats & Sub Cats.

Would I use tblCats to also hold budget data, if so, how would this be stored on a monthly basis?

or.. would I create another table (linked to tblCats), to hold budget data, and again, if so, how would I store monthly budget data for it and how would I calculate whether budgets should be pessimistic, optimistic, or averaged (one budget amount early in month, one budget amount late in the month, or spread out throughout the month by weeks in month)

In comparing Excel to Access (both have stronger and weaker points vs each other) I'm wondering whether to export / import would do the trick?

I just need a couple of columns in a form, where I can list all Cats / Sub Cats, how much has been spent according to month (lets say a combo box listing said month, and what that budget for that particular Cat / Sub Cat Is (baring in mind the need to either enter or amend the budget amount as and when necessary (so I'd need to see all Cat's listed).

Seems quite easy, but I'm really struggling with the concept...

Has anyone had to deal with such a design?

If so, what were your work arounds?

As always, appreciate your time, and experience

 

[spikepl]

For OP (and anybody else reading this)

You've provided a long story. Do you have any idea how hard it is for an outsider to read something like this, understand it, visualize what you are writing about and then consider the alternatives?

Do yourself a favour and make this easy digestible for others: make some pix or spreadsheets or anything, as a visual illustration to support your request

 

[MackMan]

Isn't that the idea? Provide as much information as possible? isn't it better than little information (which I've seen many times?)

I just need a couple of columns in a form, where I can list all Cats / Sub Cats, how much has been spent according to month (lets say a combo box listing said month, and what that budget for that particular Cat / Sub Cat Is (baring in mind the need to either enter or amend the budget amount as and when necessary (so I'd need to see all Cat's listed).

I'll keep looking.

but thanks.

 

[CJ_London]

The problem is your explanation may make sense to you who is familiar with the data, but not to us.

For example

Would I use tblCats to also hold budget data, if so, how would this be stored on a monthly basis?

what budget data? what level of detail is it available in/ needs to be stored? Is this money or volume or both? Does it have fixed, variable or semi variable elements? Is this just a simple budget or to be used for future forecasting based on current performance/ anticipated market conditions?

Short answer is keep it in a separate table or tables - but what that table (or tables) looks like is down to what you want to do.

how would I calculate whether budgets should be pessimistic, optimistic, or averaged

how do you do that at the moment? - sounds like you actually need a weekly or even daily date to manage it

baring in mind the need to either enter or amend the budget amount as and when necessary

As an accountant I would advise budgets never change once set/signed off - what changes is a forecast. Once the forecast further down the line diverges from the budget, that's when alarm bells start ringing, not when you've already gone past that particular post - by then it is too late.

Has anyone had to deal with such a design?

yes many times

If so, what were your work arounds?

no workarounds - generally the higher the granularity the better. Biggest issues are not with the P&L, but the balance sheet and cashflow where you'll need to work out your business rules for payments from debtors and payments to creditors including taxes, payroll etc. In all but the simplest of cash businesses, balance sheet and cashflow are many times more important than P&L to know whether your business is doing OK or not

 

[jdraw]

To get some hands on experience with database and table design, I recommend you work through this tutorial. You will learn about table and relationships based on a clear business description. You have to work through the tutorial but you will learn and you will use a process that can be reused with any database design.
Work through the tutorial, then apply what you have learned to your own set up.

More info on Normalization.

 

[The_Doc_Man]

Let me ask a question that might help me and others to understand what you are trying to do.

I caught some verbiage in the middle of this thread that implies you might wish to revise a budget's elements while viewing the budget as a whole. Are you trying to do the Access analog of an Excel "What If" exercise so that you can "diddle" budgets to try to make them fit new projections?

When we ask for information, our goal is to see YOUR goal, but sometimes folks give us the trees rather than the forest. I think that happened here.

 

[ChrisTheIntern]

Sounds like you are looking for is a crosstab query. I would leave Table Cats to not hold monthly budget data because tables shouldn't be used to calculate sums or anything. On tblExpenses, just make sure you include the date(month and year) on it. The crosstab will create new columns based on the date field.

When you make a crosstab query in design view, I would make the following:
1) First field would be SubCats, total: group by, crosstab: row heading.
2) Second field would be Date, total: group by, crosstab: column heading
3) Third field would budget data or actual (I don't really understand what you want). Total: sum, Crosstab: Value

 

[Rx_]

I have built several general systems that produced Excel documents, allowed for editing, then imported the data back in. They used tools such as Access, VB, .NET and combined. In general it is all the same.

One tool for example created a field regulatory Inspector's report. The agent goes on site, used the Excel on a tablet. Some things are written, other things use the list box, and there might be photos attached.

The export and import were about 10% of the project.
The standard issues of disconnected recordsets apply to dirty data, duplicate data resolution, missing (the inspectors were notorious for turning the results back), and other data management related to quality. In each case, the project resource was much bigger than most imagined. The old "just export, edit and import" has a lot of detailed maintenance to consider that negates the word "just"

What has worked for me over the centuries err... decades is to start the design at 10,000 feet. Even Power Point works for this. Start at the conceptual stage to define the data stores, the data transfer connections including direction, and the business objects in steps.

In many cases, our end product turned out to be much different than the beginning idea. There is a great section on this forum for the Database Design Theory. From there, each section can be broken out.

I hope you take eveyone's advice as a willingness of support.