Query Pulling Information From a Filled Field

[dr_destructo]

Greetings!
I have run into an issue, which I hope someone here will be able to assist with. Essentially, I have created a db for inputting catering orders. I have a form with a subform. The main form contains basic data: time, guest count, date, contact info, etc. The subform contains the menu items ordered along with quantities, individual costs and total costs. What I'm trying to do, is find an per person average using a query.

The problem I'm running into, is that my total/subtotal fields use filled data from expressions instead of inputted from a table.

My question is: How can I 'force' fill a control that can be read by the table in order to run my query? I assume that this will take some VBA(which I'm not very good at), but I'm just looking at where to start. Many thanks in advance!

 

[TexanInParis]

All you need to do is repeat the expression you're using to "calculate" values in your query trying to find an average. For example, if the amount of the purchase is Qty * UnitPrice, then you simply need Sum(Qty * UnitPrice) to total the order. It would help to know the structure of your tables and the final result you're trying to achieve.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[dr_destructo]

My apologies, but I should have mentioned this earlier. My subform which I am wanting to calculate the total of is a continuous subform, which is where my initial problem is stemming from. Thoughts on that piece?

 

[TexanInParis]

You can use text boxes in the form footer of a Continuous form to calculate using any of the Aggregate functions (Sum, Min, Max, Avg, etc.). For example, if you want the sum of the calculated item cost, do:

=Sum([Qty] * [Price])

That will do the calculation across all rows displayed in the form, and will filter appropriately if the form is in a subform with Link properties set correctly.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)