Calculation using a calculated field

Hello,

I am in need of some guidance.

I have a budget database that tracks purchases. I have a query that
totals each persons purchase and I have a field in that query that
uses that SUM to determine the amount remaining in that person's
budget. It works but when I run the query it prompts me for the
name(which I want) and it prompts me for the Sum amount(which I do not
want it to do) and it doesnt matter what I put in that box it still
comes out with the correct amount remaining. - How can I get it to
not prompt for the SUM amount? I think it does this because the
query has not run and it has not been provided with the amount until
after you hit Enter.

I would also like the amount remaining displayed on a form when that
persons budget is selected. I am not sure where to start with this
one.


I appreciate any help you all can provide.


Thanks,
Tobias

Re: Calculation using a calculated field

Tobias wrote in
news:1193338985.911235.65680@z9g2000hsf.googlegroups.com:

> Hello,
>
> I am in need of some guidance.
>
> I have a budget database that tracks purchases. I have a query
> that totals each persons purchase and I have a field in that query
> that uses that SUM to determine the amount remaining in that
> person's budget. It works but when I run the query it prompts me
> for the name(which I want) and it prompts me for the Sum
> amount(which I do not want it to do) and it doesnt matter what I
> put in that box it still comes out with the correct amount
> remaining. - How can I get it to not prompt for the SUM amount?
> I think it does this because the query has not run and it has not
> been provided with the amount until after you hit Enter.
>
> I would also like the amount remaining displayed on a form when
> that persons budget is selected. I am not sure where to start
> with this one.
>
>
> I appreciate any help you all can provide.
>
>
> Thanks,
> Tobias
>
For anybody to help you, you'll have to post the SQL of the query.
Open the query, change to SQL view, copy and paste the SQL into a
message.




--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Calculation using a calculated field

Here is the SQL

--
SELECT AcqFY08.Budget, Sum(AcqFY08.[Actual Cost]) AS [SumOfActual
Cost], Budgalloc08.[Total Allocation], [Total Allocation]-[SumOfActual
Cost] AS [Remaining Funds]
FROM AcqFY08 INNER JOIN Budgalloc08 ON AcqFY08.Budget =
Budgalloc08.Budget
GROUP BY AcqFY08.Budget, Budgalloc08.[Total Allocation], [Total
Allocation]-[SumOfActual Cost]
HAVING (((AcqFY08.Budget) Like [Enter Budget(name or area) ] & "*"))
ORDER BY AcqFY08.Budget;
--


I appreciate any help you can provide.


Thank you,
Tobias




On Oct 25, 4:35 pm, Bob Quintal wrote:
> Tobias wrote innews:1193338985.911235.65680@z9g2000hsf.googlegroups.com:
>
>
>
> > Hello,
>
> > I am in need of some guidance.
>
> > I have a budget database that tracks purchases. I have a query
> > that totals each persons purchase and I have a field in that query
> > that uses that SUM to determine the amount remaining in that
> > person's budget. It works but when I run the query it prompts me
> > for the name(which I want) and it prompts me for the Sum
> > amount(which I do not want it to do) and it doesnt matter what I
> > put in that box it still comes out with the correct amount
> > remaining. - How can I get it to not prompt for the SUM amount?
> > I think it does this because the query has not run and it has not
> > been provided with the amount until after you hit Enter.
>
> > I would also like the amount remaining displayed on a form when
> > that persons budget is selected. I am not sure where to start
> > with this one.
>
> > I appreciate any help you all can provide.
>
> > Thanks,
> > Tobias
>
> For anybody to help you, you'll have to post the SQL of the query.
> Open the query, change to SQL view, copy and paste the SQL into a
> message.
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com

Re: Calculation using a calculated field

Tobias wrote in
news:1193405540.090137.39170@50g2000hsm.googlegroups.com:

> Here is the SQL
>
> --
> SELECT AcqFY08.Budget, Sum(AcqFY08.[Actual Cost]) AS [SumOfActual
> Cost], Budgalloc08.[Total Allocation], [Total
> Allocation]-[SumOfActual Cost] AS [Remaining Funds]
> FROM AcqFY08 INNER JOIN Budgalloc08 ON AcqFY08.Budget =
> Budgalloc08.Budget
> GROUP BY AcqFY08.Budget, Budgalloc08.[Total Allocation], [Total
> Allocation]-[SumOfActual Cost]
> HAVING (((AcqFY08.Budget) Like [Enter Budget(name or area) ] &
> "*")) ORDER BY AcqFY08.Budget;
> --
>
>
> I appreciate any help you can provide.
>
>
> Thank you,
> Tobias

SELECT AcqFY08.budget, Sum(AcqFY08.[actual cost]) AS [SumOfActual
Cost], Budgalloc08.[total allocation], [Total Allocation]-
[SumOfActual Cost] AS [Remaining Funds]
FROM AcqFY08 INNER JOIN Budgalloc08 ON AcqFY08.budget =
Budgalloc08.budget
GROUP BY AcqFY08.budget, Budgalloc08.[total allocation]
HAVING (((AcqFY08.budget) Like [Enter Budget(name or area) ] & "*"))
ORDER BY AcqFY08.budget;

The group by [Total Allocation]-[SumOfActual Cost] is what was
causing your problem. To fix in the query builder change the group
by to expression under the Remaining Funds column

Q


>
> On Oct 25, 4:35 pm, Bob Quintal wrote:
>> Tobias wrote
>> innews:1193338985.911235.65680@z9g2000hsf.googlegroups.com:
>>
>>
>>
>> > Hello,
>>
>> > I am in need of some guidance.
>>
>> > I have a budget database that tracks purchases. I have a query
>> > that totals each persons purchase and I have a field in that
>> > query that uses that SUM to determine the amount remaining in
>> > that person's budget. It works but when I run the query it
>> > prompts me for the name(which I want) and it prompts me for the
>> > Sum amount(which I do not want it to do) and it doesnt matter
>> > what I put in that box it still comes out with the correct
>> > amount remaining. - How can I get it to not prompt for the SUM
>> > amount? I think it does this because the query has not run and
>> > it has not been provided with the amount until after you hit
>> > Enter.
>>
>> > I would also like the amount remaining displayed on a form when
>> > that persons budget is selected. I am not sure where to start
>> > with this one.
>>
>> > I appreciate any help you all can provide.
>>
>> > Thanks,
>> > Tobias
>>
>> For anybody to help you, you'll have to post the SQL of the
>> query. Open the query, change to SQL view, copy and paste the SQL
>> into a message.
>>
>> --
>> Bob Quintal
>>
>> PA is y I've altered my email address.
>>
>> --
>> Posted via a free Usenet account fromhttp://www.teranews.com
>
>
>



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Calculation using a calculated field

Perfect.

Thank you!

--Tobias




On Oct 26, 5:22 pm, Bob Quintal wrote:
> Tobias wrote innews:1193405540.090137.39170@50g2000hsm.googlegroups.com:
>
>
>
> > Here is the SQL
>
> > --
> > SELECT AcqFY08.Budget, Sum(AcqFY08.[Actual Cost]) AS [SumOfActual
> > Cost], Budgalloc08.[Total Allocation], [Total
> > Allocation]-[SumOfActual Cost] AS [Remaining Funds]
> > FROM AcqFY08 INNER JOIN Budgalloc08 ON AcqFY08.Budget =
> > Budgalloc08.Budget
> > GROUP BY AcqFY08.Budget, Budgalloc08.[Total Allocation], [Total
> > Allocation]-[SumOfActual Cost]
> > HAVING (((AcqFY08.Budget) Like [Enter Budget(name or area) ] &
> > "*")) ORDER BY AcqFY08.Budget;
> > --
>
> > I appreciate any help you can provide.
>
> > Thank you,
> > Tobias
>
> SELECT AcqFY08.budget, Sum(AcqFY08.[actual cost]) AS [SumOfActual
> Cost], Budgalloc08.[total allocation], [Total Allocation]-
> [SumOfActual Cost] AS [Remaining Funds]
> FROM AcqFY08 INNER JOIN Budgalloc08 ON AcqFY08.budget =
> Budgalloc08.budget
> GROUP BY AcqFY08.budget, Budgalloc08.[total allocation]
> HAVING (((AcqFY08.budget) Like [Enter Budget(name or area) ] & "*"))
> ORDER BY AcqFY08.budget;
>
> The group by [Total Allocation]-[SumOfActual Cost] is what was
> causing your problem. To fix in the query builder change the group
> by to expression under the Remaining Funds column
>
> Q
>
>
>
>
>
> > On Oct 25, 4:35 pm, Bob Quintal wrote:
> >> Tobias wrote
> >> innews:1193338985.911235.65680@z9g2000hsf.googlegroups.com:
>
> >> > Hello,
>
> >> > I am in need of some guidance.
>
> >> > I have a budget database that tracks purchases. I have a query
> >> > that totals each persons purchase and I have a field in that
> >> > query that uses that SUM to determine the amount remaining in
> >> > that person's budget. It works but when I run the query it
> >> > prompts me for the name(which I want) and it prompts me for the
> >> > Sum amount(which I do not want it to do) and it doesnt matter
> >> > what I put in that box it still comes out with the correct
> >> > amount remaining. - How can I get it to not prompt for the SUM
> >> > amount? I think it does this because the query has not run and
> >> > it has not been provided with the amount until after you hit
> >> > Enter.
>
> >> > I would also like the amount remaining displayed on a form when
> >> > that persons budget is selected. I am not sure where to start
> >> > with this one.
>
> >> > I appreciate any help you all can provide.
>
> >> > Thanks,
> >> > Tobias
>
> >> For anybody to help you, you'll have to post the SQL of the
> >> query. Open the query, change to SQL view, copy and paste the SQL
> >> into a message.
>
> >> --
> >> Bob Quintal
>
> >> PA is y I've altered my email address.
>
> >> --
> >> Posted via a free Usenet account fromhttp://www.teranews.com
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com