Summing Columns to populate a form

[leedub]

Sum Data im a Columns to populate a textbox on a form

Hello Im working in Access 2007.

So i have query based on 1 table that populates a Form. The primary key for that table is Entity ID. Therefore once the query has been run I have multiple records that i can scroll through in my form distiguished by their Entity ID.

I have a second table that has a Entity ID column, AFE Available column, and many others. The primary key for this table is called Match ID. This table contains records that have the same Entity ID.

My goal is to display on the form the Sum of the "AFE Availible" for each Entity ID. so as you scroll through the records the Entity ID is changing and you are able to see a the Specific "AFE Availible" Sum related to the current Record showing on the form.

I couldn't figure out a way to have a query based off both tables where the records are only uniquely defined by the Entity ID in Budget Info. What was happening is the query was displaying all the records that had the same Entity ID because of the AFE Spent table. That way when you scroll through the records the form shoes records with the same entity id.

Maybe im doing it all wrong and you dont need the tables attached to the same query. That would make it easier i think. So you would have two queries populating different text boxes on a form. Is that even possible???

Any help is apreciated thanks!

 

[leedub]

Thanks! I figured if out.

Ok so now i have this table that displays all my associated records to each Entity ID. It displays it in a table. Is there any way to display in form view?

 

[Mihail]

Pat has answered before you ask (again)

Based on your description, tbl2 contains EntityID and that is what relates it to tbl1. So add both tables to the relationship window and draw a line between the two tables to connect EntityID and that will create the relationship. It will be defined as 1-many because the FK in tbl2 points to the PK in tbl1.

You need a form with a subform. The form shows data from tbl1. The subform shows data from tbl2. As long as the relationship has been defined, Access will automatically populate the master/child links and that is what it uses to sync the two forms. You would add subtotals to the subform. If you are using A2010 (and maybe A2007), use the sigma button to add the totals line. If you are using something else, add controls to the subform's footer and use those to sum the amount.

=Sum([AFE Available])

 

[leedub]

Sorry i dont think i was clear enough. I have the subform on my main form with the correct data. But when I view the main form in form view. The subform display the data in a table.
I was wondering if there is way so that the subform displays the data in a form.

 

[leedub]

Thnaks for you help i was able to figure is out