Budgets and Graphs in Access (1 Viewer)

mounty76

Registered User.
Local time
Today, 10:47
Joined
Sep 14, 2017
Messages
341
Hello,


I have made an accounting database, main table is called accounts, it is simple and has the normal stuff on there, including date, payment type (combo), accounting code(combo), description, and amount.

I would like now to be able to input a budget for each accounting code (17 groups in total) for each month, then somehow compare the budget Vs the amount spent for that particular month in the accounts table. Then to present this data in a graph on a report.

I'm a little unsure where to start really, I was thinking of a budget table with columns MonthStartDate, then each accounting code (127 in total, 1.01, 1.02, 1.03, 2.01, 2.02, 3.01, 4.01, 4.02...etc I notice I can't use the .dot which is unfortunate but can live with it) as the other columns, I would then have one record per month for the budget.

I would then have to create a query for each accounting group (17) which totals up the budget for that group for each month over a years period and compares it to the equivalent data for that month from the accounts table.

Sounds simple but not so sure it is, any advise before I attempt would be greatly appreciated!!

Thanks in advance!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I follow all that, but I get the impression that you're thinking about something like an Excel spreadsheet layout for your table. If so, I would say don't do it. There has to be a better way. If you would like to elaborate on what you're thinking, perhaps you could post some screen mockups to help us understand your question.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:47
Joined
Jul 9, 2003
Messages
16,244
As mentioned by DB guy it does appear that you have got an Excel head on where MS Access demands a different way of thinking about how your data relates.

I have written a blog about this problem, the problem of thinking in terms of MS Excel whilst trying to develop a database in MS Access. The blog is here:-


Hopefully it should give you some insight into the issues, and possible solutions.
 

mounty76

Registered User.
Local time
Today, 10:47
Joined
Sep 14, 2017
Messages
341
Hi Gents,

Your correct in that I'm really after an excel type graph that compares two sets of data, I originally started this project in Excel but it soon became messy due to the amount of data......the DB I've done works great and it's able to extract everything perfectly, it produces reports in the way the CFO wants them and reports that I need, easy.

That is all good for the data entry and recovery of data and some simple sums.

My issue is I need to be able to enter a budget, the current DB has all the physical items paid for, so its a true/actual accounts, however I need to be able to insert a budget somehow and compare the budget Vs what has been physically spent, so the DB has all the data for one line of the chart, the budget would hold the other....see below:

Would it be better to export the amounts actually spent then complete the budget and graph comparisons in Excel? It would be the easiest way I think but if at all possible I'd like to keep it all in access.

1606004351941.png
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:47
Joined
Jul 9, 2003
Messages
16,244
The budget in excel looks like this
Your budget spreadsheet looks somewhat similar to this pull out the percentages spreadsheet HERE:-


I suggest you study the the Google presentation which explains how to convert the percentage pull-out sheet into something suitable for MS Access and and see if you can work out how to do that with your your budget sheet.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:47
Joined
Feb 19, 2002
Messages
42,971
@mounty76 ,
You have two choices.
1. Use Excel
2. Use Access

As the others have mentioned. Access is NOT Excel and if you try to implement an Excel solution with Access, you will make yourself a ton of extra work and still be disappointed.

Do you think accounting packages like Quickbooks keep their data in Excel? NO, they do not. They use relational databases such as Access (technically Jet/ACE) to hold the data and some other programming platform to create the forms/reports/graphs. Access is quite capable of doing what you need. I haven't used graphs in Access for a while so I don't know the current state of the graphing module. If it turns out that you can't make the graphs you want in Access, it is a simple matter to export the data to an Excel template where Excel will create a beautiful graph.
 

CarlettoFed

Member
Local time
Today, 18:47
Joined
Jun 10, 2020
Messages
119
You should create:
a tblAccountingGroup table​

IDAccountingGroup (Automatic Numbering)
AccountingGroup (Text, size 10)​

a tblMonths table​

IDMonth (Automatic Numbering)
Month (Text, size 15)​

a tblBudgets table​

IDBudget (automatic numbering)
BudgetYear (Numeric, Integer)
IDMonth (Numeric, Long Integer)
IDAccountingGroup (Numeric, Long Integer)
BudgetValue (Currency)​

replace the accounting code field with IDAccountingGroup in the Accounts table
then create the relationships between​

Accounts -> tblAccountingGroup
tblBudgets -> tblAccountingGroup
tblBudgets -> tblMonths​

and at this point through query you can get the result you want.
If you attach a sample file, with a minimum of non-sensitive data, it is possible to show you how this can be done.
 

mounty76

Registered User.
Local time
Today, 10:47
Joined
Sep 14, 2017
Messages
341
Your budget spreadsheet looks somewhat similar to this pull out the percentages spreadsheet HERE:-


I suggest you study the the Google presentation which explains how to convert the percentage pull-out sheet into something suitable for MS Access and and see if you can work out how to do that with your your budget sheet.
Perfect, thank you, makes sense
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:47
Joined
Jul 9, 2003
Messages
16,244
Perfect, thank you, makes sense

Glad to be of help.

You may also find this useful:-

I provide a tool to perform this transposition of the data from Excel format into MS Access format. You can download the tool from here:- https://gum.co/NiftyTransposeTool
I'm happy for you to have a free copy as long as you subscribed to my YouTube channel at the following link:-

• Get the Latest MS Access YouTube's HERE:-

Once you have subscribed to the YouTube channel use coupon code:- t0cpajo and you will be able to download the transpose tool for free...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:47
Joined
Feb 19, 2002
Messages
42,971
Here is a rather advanced example of how you can make a properly normalized table look like a spreadsheet using a real bound form. The original of this sample was designed to facilitate budget inputs by the user who wanted a 12-months across view.
 

Attachments

  • BoundDenormalizedForm20201022.zip
    1.5 MB · Views: 541

Users who are viewing this thread

Top Bottom