Full Version: Formula for Specific Items Within A Drop Down
UtterAccess Forums > Microsoft® Access > Access Forms
Karol
I have a form with a drop down. Lets say Id 1 is Flavor 1, Id 2 is flavor 2. I want to be able to give a sum total for each flavor (which would be the description). So the example would be flavor 1 with an ID of 1 has 50 hits for the day so it will total 50. However, Flavor 2 with an id of 2 only has 25 hits so its total for the day is 25. I do have a table (tblprovide) that the drop down pulls from. So my question is within the form text box how would I create a formula so it would calculate Id 1 within a text box with the label of flavor 1 and a formula for flavor 2 within another text box with the label of flavor 2?
xample: Flavor 1 50
Flavor 2 25
niesz
Define "hits" ... and how it is derived.
My guess is the row source for the combobox would be based off of an aggregate query.
merlicky
I guess I’m a little confused by your request. My interpretation is that you want to count how many times each flavor was selected from a ComboBox and display the result in another TextBox. If I understand correctly, there are a couple things you would need to do:
. You need to store how many times each flavor was selected. To do this you could use a “Hits” field in your table for each flavor. You could use the ComboBox’s AfterUpdate Event to write code to increase the value in the “Hits” field.
2. You need to have the ControlSource for your TextBox based on a Query that combines the “Flavor”, “ID”, and “Hits” fields into one value.
Karol
OK. I do want to count how many times each flavor was selected from the combobox. I would have a query that would add 1 for each record. So, If I have another combo box on the the form by month. The user select the month of July the total records for flavor 1 would show a total of 50.
So, what would the formula look like?
merlicky
There is no magic formula in this instance. It sound like you will need to create a table to store the hits and the date of the hits for each flavor. Then, it entirely depends on how you have your table/s, query/s, and form/s set up and what your intention is.
Karol
Now, I am confused. There has to some sort of formula to count the number of records for flavor 1. Somehow I am thinking that within the control box for flavor 1 it would have =[Hits].Query!Flavor1
Would that work?
merlicky
You are not going to store each individual hit as a seperate record, the Hits field will keep the total number of hits for each flavor.
Yre not going to store each individual hit as a seperate record, the Hits field will keep the total number of hits for each flavor.
Your table would need fields like these:
| Flavor | Hits | HitDate |
The SQL you would need is something like: "SELECT Hits FROM MyTable WHERE HitDate=MyInputDate"
or
"SELECT Sum(Hits) AS TotalHits FROM MyTable WHERE Month(HitDate)=Month(MyInputDate) AND Year(HitDate)=Year(MyInputDate)"
But again, where you put this SQL is going to depend on how you have your database set up. What is the RecordSource for your form? Are the results and the ComboBox where you keep track of hits on the same form? ect...
mike60smart
Hi
Can you post your Db so we can take a look?
Mike
Karol
Here is a copy of a test db.
jmcwk
See attached and open the Query
Karol
OK. I see what you did with the query. Now how do I show this by description on the form? I want to show one text box with the sum total for flavor 1 and another text box with the total for flavor 2?
jmcwk
Is this just for informational purposes ? By Total do you mean the Total excluding the Date Grouping ? Flavor 1 would have 50 and flavor2 would have 24
Is far as displaying I would use a Listbox and then set your Criteria in the Query against the FlavorID See Attached, open your form and select April from the Combo
mike60smart
Hi
The only way you can show the two flavor's is by means of a Pivot Table
See attached
Open frmTest
Mike
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.