Calculating Field on a Form

[tstowe]

I have three tables: Region, Country, City

In TBL_City I have information that I need to perform math functions and represent in a graph (if possible) to compare the information from two separate cities.

On the Form I will first select the Region (North America, Europe, Middle East, etc); in the first Sub-form I will select the corresponding country based on the Region selected; in the second Sub-form it provides an opportunity to Add/Delete/Change Cities for the selected country and to make quantity changes to the four fields, which are: Troop Strength, Armor, Air Defense and Artillery.

My first problem is having a field on the FORM, not the Sub-form, giving a total sum of each of the above four fields for the selected country based on the information provided in the city sub-form.

My second problem is how can I compare the Total quantities from two (possibly up to four) selected Cities and then present this data in a graph? The graph isn't completely necessary but it would add a quick reference. The comparison would allow me to see that City One has 150% more defense over City Two.

Thanks ahead of time and if you happen to have any additional comments please add them.

Tony

 

[sillysod]

I imagine a pivot table is what you need.

Check the access help file for what they are/what they allow you do do.

Once you have looked into pivot tables post back here and let us know if you thnk it will do what you want it to do.

Any probs let us know

 

[tstowe]

I look now. Thanks.

I have figured out how to show the quantity of one city from the sub-form on the form, but I haven't been able to have the field to SUM the quantities of all cities within that country onto the form. I hope that made sense.

Thanks for the suggestion. I will read through the information now and reply soon.

Tony

 

[sillysod]

base the subform on a query which calculates the value.

just have two fields in your query, the country and quantities (maybe this is in a separate table which holds the info on quantities for each city, if so bring in both table and link them)

the first field will be country the second your quantity.

now click the sigma button on toolbar (its the same button as the autosum button in excel, like a back to front 3)

in the new row that appears select group by for country and sum for quantity

 

[tstowe]

I created the query and is successfully providing a SUM of the four fields for all city quantities, grouped by Country. Perfect!

Now, I went back into my form and tried to pull that number onto the form and I am still getting an error message. In the field I have "=[QRY_SUM of Units]![SumOfSoldier]" to pull the information onto the form, however I get a #Name error message. My other question relates to the the Region:Country selection and this field. As I change Region:Country will this field change its value to represent the correct information for the selected Country?

Thanks

Tony

I am playing with the PivotTable and its use will prove beneficial, however I have never used this part of Access and it will take me some time to really understand the pro's/con's of its use. I would like to ask, that once I create a PivotTable and it represents the data as a graph, how do I put that graph onto my original Form?

So many questions. . . .

 

[sillysod]

in all honesty im not that clued up about pivot tables, so i will step aside and let some one who knows that area answer that question.

regarding the query, well the datasource for the form should be set to the query you have just created, so you should be able to just drag the field from field list onto the form. theres no need to enter any further calculations on the form or in the control box that you want to show the value