Formula for members in a field

[ringmaker]

Hi - I am a MS Access beginner. I am using access instead of excel (where I am much more savvy) because my data set is huge and moving slowly. I have successly created an Access Pivot Table Form however I would like to create a formula which subtracts the items/members within one field from others within that field.

Here is an example of my data:

Proj Account Oct Nov Dec
ProjA Rev 100 150 200
Wages 25 35 40
Travel 5 3 1
Other Costs 3 0 1
ProjB Rev 200 300 400
Wages 50 70 80
Travel 10 6 2
Other Costs 6 0 2

I would like to create a formula of items within the account field. Namely Gross Profit = Rev - Wages - Travel - Other Costs. I'd like this new calculation to sit within the account field. I know this must be a snap but I can't seem to figure it out. Thanks in advance for your help.

 

[mdmilner]

My suggestion is start small - such as with a simple query instead of a pivot table which has somewhat more complex rules on structure. Am just suggesting you test the below before you try adding it to the pivot table.

Access syntax for adding fields is in the QBE wizard is:

CalcFieldName: [fieldname1] + [fieldname2] +[fieldnameN]

Concatenation (grabbing multiple fields and connecting them into a single field is:

ConcatFieldName: [fieldname1] & [fieldname2] & [fieldnameN]

Inserting fun things like spaces between them would be:

ConcatFieldName: [fieldname1] & " " & [fieldname2] & " " & [fieldnameN]

The [] brackets are also useful when you want to guarantee that Access identifies your word/string as a field...particularly should you choose to use normally reserved words like 'Date' for a field. Access will let you use 'Date' but if you just try typing that in, it may confuse it with the Date() function.

Mike

 

[ringmaker]

Mike - thanks for your quick response. Before I get started with your suggestions, I wanted to make sure these formulas relate to calculations of the items within a field as opposed to summing individual fields. So in a query, under the field called account, how do I refer to unique items within the account field. Thanks for your help!

 

[mdmilner]

They don't - they're for the whole field.
To retrieve portions of fields, you have to go after text values (strings) and use functions such as LEFT/RIGHT/MID/InStr. Best place to look at these is inside the built-in VBA help but...

Where X is an integer

Left([fieldname1],X) ' gets the leftmost X values

Mid([fieldname1],X,Y) ' start a position X, take the Y number of characters

InStr([fieldname1],X) ' Where X is a string enclosed in quotes looking for a match...InStr returns a position for an item such as a 'comma'

InStr([fieldname1],",")

And can be used to do things like:

Left([fieldname1],InStr([fieldname1],",")-1)

This would find the position of the FIRST comma, and then return all characters starting from left to right up to 1 position before the comma.

ABCD,ADAH would return "ABCD" only.

This is not how you would go after numeric values - I'm assuming if you're extracting portions of a field the entire field must be a text value. Doing things like extracting integer values out of a double format would be done completely differently but probably isn't relevant right here.

Personally, unless you're using the field as a key index value, I would recommend not combining multiple data items within a single field. It's probably that there's a better way to organize the data into multiple tables as a relational database.

Mike

 

[Andrew Fergus]

Hi, I'm not sure why you want to the calculation in the "account" field - can you explain this further?

Explanations and data structures aside (I'm not sure that you have an optimal data structure - see this webpage - it has some useful links for database design) - but design aside, the "gross profit" for a project is the sum of all costs less the revenue, multipled by -1 (I know, a long winded way of saying revenue - expenses ).

the conceptual difference here is that, via a query, you can add all of the values except for the item with an account of "rev", then subtract this value from the "rev" value from another query. I'm not sure if my answer is on track but it may be another way of looking at your problem.

HTH, Andrew.

 

[ringmaker]

Andrew/Mike - Thanks for your helpful responses. I am still hanging out in idiot land so hopefully for all of our sakes, the lightbulb will soon go off. I did read the article on database design quickly (and I promise to go back and memorize it later - it was very helpful). I also bought a 50 lb MS Access book today - so I am trying but I could still use some guidance. Here is a bit more about my database endeavors.

I have extracted revenue and expense data from a general ledger. So I have a table with the following fields:

Project
Account - (this includes each revenue and expense account but no calculations, ie gross profit is not an account)
Month
Amount

I have also added a table to create an account hierarchy (account groups and account classes) and a table to create an organizational hierarchy (projects pointing to subunits and units).


And then I created a query to put info from the three tables together.

And then from my query, I created an Access Pivot Form with account and unit down the side and months across the top

And my access pivot form was wonderful except, it didn't have a way to present gross profit since gross profit is not an account in the account field.

So I kind of understand that I might accomplish the Gross Profit calculation in a separate query, but I am having a hard time visualizing how to make this happen since the calculation I am attempting does not sum two different fields - instead it is calculating items (rev-exp accounts) within one field. That is why I keep thinking I need the calculation to exist in the account field of the query. In an excel pivot table, I would simpy create a formula to calculate data items (instead of data fields) and the new item resulting from the formula would exist in the account field.

Sorry if I am being completely dense. Thanks again for your time and help.

 

[Andrew Fergus]

Hi - thanks for the explanation - it is all crystal clear and no you are not being dense.

"Profit" is calculated for each "project" rather than each "account". Your current crosstab query design is attempting to calculate "profit" in the wrong dimension (for want of a better word). If you had projects in the rows and months in the columns then you could calculate the profit per project per my previous suggestion (although it wouldn't show the detail). With your current report (and data) setup the projects are one dimension, months are a second dimension and accounts is a third dimension - so calculating "gross profit" won't work with your current setup given that Access struggles with a third dimension.

However, you can get around this problem by adding the "profit" value into the main table so that it is available for the report. This has it's own issues but it can be done by creating an "append" query that calculates the "profit" for each "project" for each "month" and adds a new record into your data table (same project, same month, account = "gross_profit" and value is calculated). This new line item will then appear in your crosstab report.

A quick explanation of how to do this :

Create a new query, add the main data table, add the fields project & account & month & value, view totals, enter "total_revenue :" before the value variable (without the quotes, this changes the name of the variable for later use), change group by for value to sum, enter "revenue" (with the quotes) into the criteria section under account. Save.

Create a 2nd new query, add the main data table, add the fields project & account & month & value, view totals, change group by for value to sum, enter "total_expenses :" before the value variable (this also changes the variable name), change group by for account to where, enter <>"revenue" (with the quotes) into the criteria section under account, and untick the "show" box for account. Save.

NB : be sure to use your actual revenue name where I have used the variable "revenue". If there are multiple names used for the revenue accounts (e.g. sales, income, revenue etc.), then add the account hierarchy table and use the generic name for revenue per your account hierarchy (ask if you are not sure).

Create a 3rd query, add the first and second queries, create links between the project and month fields, Click Query -> Append Query -> Select the main data table -> OK, add the project from the first query (append to project), add the month from the first query (append to month), enter "gross_profit" (with the quotes) into third column (append to account), enter "[total_revenue] - [total_expenses]" into the 4th column (without the quotes, append to value), Save and Run.

This last query will put the gross profit values by project by month into the main table. These values should now show on your report.

Please note the following :
I have assumed (probably incorrectly) that where there is income that there are also expenses (and vice versa) and that you don't get instances where there is revenue without expenses (and vice versa).
I have also assumed (incorrectly) that there will be no more data, that you are populating this table only once and that you won't be changing any of the values.
I have assumed that where the profit is zero that you actually want that value to show.

Whilst I have made some pretty bad assumptions, this should get you started and we can address issues of revenue without expenses (and vice versa) later, as well as the impact of adding more data or changing any of the existing values.

HTH, Andrew.

 

[ringmaker]

Andrew - You are wonderful! That was a perfect step by step illustration. It worked beautifully and I have learned a great deal from you. Thanks for being so kind and so patient and so brilliant! I want you to know that I advertise this website to anyone willing to listen. It is an amazing resource and I am a big fan!!!!!

 

[Andrew Fergus]

You're welcome but please be aware that the solution I provided is a little clunky and you should watch our for changing or new data or data where there is sales without costs and vice versa. Let us know if you get stuck there.
Andrew.

 

[ringmaker]

Andrew - I actually did get stuck. As you guessed, I do have items with rev and no cost and vice versa. And I do at some point want to refresh data. Thanks for your continued guidance.