Calculation based on row/value selected from list

[Duane Dibbley]

I have a table with a primary key as date, and various other fields with numerical values. I also have a query that calculates running totals for each of these fields, as well as including the fields from the table.


I need to create a report or form in which I can select a date(primary key) from a drop down list.

I need the selected date to determine a row/value from a field (a running total from the query)

I then need to use that value in a formula;

result = sum([field]) - value

What i'm trying to do is calculate the difference between the sum of a field, and a specific row/value in a calculated field containing a running total of the original field. I'm new to Access so appreciate any help/guidance offered!

 

[plog]

Please demonstrate what you want using data. Be sue to include table and field names and enough sample data to demonstrate any special cases.

 

[Duane Dibbley]

Please demonstrate what you want using data. Be sue to include table and field names and enough sample data to demonstrate any special cases.

Apologies I shall try to be more specific;

I have a table called '01905', which has a primary key field of dates called 'aDate', and about 30 other numerical fields. Assume they're called 'field1' etc.

I have created a query based on this table, which contains calculated fields showing the running total of each numerical field in the table. Assume these are called 'rtfield1' etc ('rtfield1' being a calculated running total field of 'field1', 'rtfield2' a running total of 'field2' etc)

e.g If 'field1' contains the values 1,2,3,4,5 then 'rtfield1' contains the values 1,3,6,10,15

This much i have achieved, now what i need to do is create a report or form (i am unsure which is best suited) which contains a drop-down list to select a value from the 'aDate' field within the query.

I need the date chosen on the list to select/indicate a row/value for each 'rtfield' in the query. I then need to perform a calculation based on this value for each field in the table/query, which i need to display in a textbox, one for each field;

result = sum([field1]) - selected value from [rtfield1]

so essentially i need to calculate the total of 'field1' and then minus a selected value from 'rtfield1' and display the result in a text-box

Hopefully this is enough data to understand what i'm trying to achieve thank-you!

 

[Minty]

Can you explain the outline of your purpose - you seem to be going about this in a very complicated way, and my maths skills are weak at the moment

 

[Duane Dibbley]

Can you explain the outline of your purpose - you seem to be going about this in a very complicated way, and my maths skills are weak at the moment

The purpose of the database is financial. Each field/column in the table represents a cost category, and each numerical value in it represents an amount spent or due to be spent. Each row represents a month (hence the primary key being a date field). The first month/row is in 2013, the last 2018. Therefore the first row is the start, and the last row the finish.

The calculated field in the query i created gives a running total showing the total cost from the first month/row, for each row/month after, this is required to display the total amount spent since the start, for each month.

I need to be able to select a month/row and minus the running total amount for that month/row from the grand total for the whole field. This will show how much of the grand total (budget) for a field is remaining to be spent between the selected month and the end (last row/month)

 

[Minty]

You sound like you have not normalised your data very well at all, it sounds as if you have pasted a spreadsheet into a table.

You should have a field to indicate the cost category, not a column for each one.
You can then add another field for projected cost data and have a separate column or even table for spend data.

Try normalising your data then post up any further problems you have. In it's current format you would be better off in a spreadsheet.

 

[Duane Dibbley]

You sound like you have not normalised your data very well at all, it sounds as if you have pasted a spreadsheet into a table.

You should have a field to indicate the cost category, not a column for each one.
You can then add another field for projected cost data and have a separate column or even table for spend data.

Try normalising your data then post up any further problems you have. In it's current format you would be better off in a spreadsheet.

Indeed my task is to convert a spreadsheet into a database.

However your idea of 'better normalization' seems infact much worse.

To hold the data in a table I have the choice of setting the month or cost category in one field, with the other having to be seperated into individual fields. I have chosen months as there's over a hundred of them, and only a dozen cost categories. It is cleary better to have as few fields as possible.

I am seeking help in performing the calculation listed in my post from someone with the mathmatical and access knowledge to do so

 

[Minty]

I know it seems awkward, and initially maybe what you think of as worse, but you will be better of in the long term in many ways if you can put your data into related tables in a repeatable way.

I'm not disputing your need to use the date as a key filter - just how you are are going about it.

Although I have a good idea from your posts can you show your actual data structure? I suspect myself or someone here will be able to show you the best practice for storing and processing this information.

 

[Duane Dibbley]

I know it seems awkward, and initially maybe what you think of as worse, but you will be better of in the long term in many ways if you can put your data into related tables in a repeatable way.

I'm not disputing your need to use the date as a key filter - just how you are are going about it.

Although I have a good idea from your posts can you show your actual data structure? I suspect myself or someone here will be able to show you the best practice for storing and processing this information.

The actual data structure is simple. Its a table on a spreadsheet with each row being a cost element, and columns representing an individual month. each cell has a value for the amount spent for that cost element, for that month.

I have chose to normalize it as a table with a primary key as month, and each field representing a cost element. The only other way i am aware of to do this is to reverse the axis so to speak and this is not viable as there are too many months and it'll put the table above the field limit.

There will be no additional cost elements added, but there may be months added, so it seems best to have to add an entry rather than a new field.

 

[smig]

I did not read all the posts

How do you expact to get sums based on the selected date if it is your primary key ?
Primary key mean you have only one record with this key.

 

[Duane Dibbley]

I did not read all the posts

How do you expact to get sums based on the selected date if it is your primary key ?
Primary key mean you have only one record with this key.

Indeed each record must have a unique value for the primary key field. But each record contains other fields?!?

e.g.

aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
Jan 2014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
Feb 2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
Mar 2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
Apr 2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12

fields calc1,2,3 are running totals of cost1,2,3

I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.

result = sum(cost1) - calc1 selected record value

I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.

i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.