Subform that shows Year Budget, YTD spent, & Remaining Budget

[GodivaGirl]

I would like to put a subform on my main form, frmPOinput, that will display the Year Budget, the YTD Spent, and the Remaining Budget. There are 2 different Budget categories that need to be totaled.



I have an order table, tblOrders, that has all of the items ordered and an OrderNo that differentiates each separate order. I have a query, qryOrders that has a Total column with the criteria: [tblOrders].[Quantity]*[tblOrders].[UnitPrice] to total the amount for each item spent.

I have a report that creates the Purchase Order and in the footer of a subreport, I have a text box with the following Control Source: =Sum([UnitPrice]*[Quantity]) to give the total of that order.

I have tried writing a query to get this information but am not successful.

All help is greatly appreciated.

[June7]

Why not successful, what happens - error message, wrong results, nothing?

Post attempted query.

I expect will involve maybe two aggregate queries and then maybe joining those two.

Alternative is domain aggregate functions (DSum, DCount, DAvg, etc).

[GodivaGirl]

When I run the query or view the form I get nothing on the query view or for the fields on the form.

I have been looking at the query further and still am getting no results. Here is a screen shot of my query for the form. Help please. :-)

Supply Data Base Query Screen shot.doc

[June7]

The Quantity * UnitPrice calculation does not go in Criteria row. It goes on Field row. This will create a calculated field in query. Can bind a textbox to that constructed field.
.

[GodivaGirl]

Okay. I tried what you will see in the screen shot, but I still don't get anything when I run it.

Supply Data Base Query Screen shot2.doc

I'm not sure the file attached correctly. Let me know. Thanks so much.

[June7]

There is no need for the [tblOrders] prefix in the expression. tblOrders is not even in the query.

What happens if you remove the filter criteria?

[GodivaGirl]

I tried this and I still do not get anything when I run the query.

Supply Data Base Query Screen shot3.doc

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.