Use of Query Column Total in an expression

[Hawthorne62]

I am very new at access. I have a Query that lists Medical CoPay's for each medical expense. I now want to use the Total at the bottom of the Copay column and compare that to the budget for the year. How can I create an expression (formula) to make this comparison and report the difference between the two numbers.

[orange]

It would be helpful to readers if you help set the context for your question. A brief description of your "business" and your database, and perhaps a jpg of your tables and relationships.
We only know what you tell us, so the more you tell us, the better the reader's understanding of your situation and request; and the more likely you are to get a more focused response.

Welcome to the forum and good luck with your project.

[alcapps]

I am very new at access. I have a Query that lists Medical CoPay's for each medical expense. I now want to use the Total at the bottom of the Copay column and compare that to the budget for the year. How can I create an expression (formula) to make this comparison and report the difference between the two numbers.

Is the Budget value in a different table or in the current query?

you can make it a subquery or a function call. This should be simple to do.

if both values are in the same query already then use. BudgetComp: [TotalCopay] - [BudgetCopay]
or
BudgetComp: [TotalCopay] - dbSum("tableName", "[BudgetCopay]","[id] =" & [ID] )
something like this would work.

[Hawthorne62]

Thank you "alcapps", I'll give your solution a try

[alcapps]

Thank you "alcapps", I'll give your solution a try

If you could provide your query .. and table structure I can be more helpful.

Al

[Hawthorne62]

If you could provide your query .. and table structure I can be more helpful.

Al

Date Paid By Year	Patient	Pay Type	Provider	Category	SumOfCoPay	BudgetComp
2012	David	$11.59 Not Charged	Southern Me. Orthopedic	Doctor	$0.00
2012	David	Cash	CVS Pharmacy	Pharmacy	$5.05
2012	David	Cash	Hannaford Pharmacy	Pharmacy	$76.73
2012	David	OLP	Doctor Yorkey DO	Doctor	$71.76
2012	David	OLP	Mercy Hospital	Laboratory	$203.02
2012	David & Jo Anne	Payroll Deduction	Cigna	Premium	$6,674.88
2012	Jo Anne	2759	Mercy Hospital	Hospital	$187.46
2012	Jo Anne	2760	Intermed	Doctor	$308.54
2012	Jo Anne	2760	Mercy Hospital	Hospital	$29.09
2012	Jo Anne	2761	Intermed	Doctor	$54.11
2012	Jo Anne	Cash	CVS Pharmacy	Pharmacy	$158.46
2012	Jo Anne	OLP	Mercy Hospital	Hospital	$7.18
2012	Jo Anne	POS	CVS Pharmacy	Pharmacy	$52.70
2012	Jo Anne	POS	Spectrum Medical Group	Doctor	$35.64
2012	Jo Anne	Their Web Site	ER Phy. - Todd Abrahams	Doctor	$35.64
2012	Jo Anne	Visa	Gorham Family Dentistry	Dentist	$131.00
2012	Jo Anne	Visa Debit	CVS Pharmacy	Pharmacy	$76.35
2012	Jo Anne	Visa Debit	Gorham Family Dentistry	Dentist	$131.00
					$8,238.61

I'm looking to take the Total of $8,238.61 in this query and compare it to the annual budget of $9,924.88. The total in the Copay column changes as entries are made so I'd like Access to keep tabs on how much we are spending as compated to the annual budget. How can I best do this? Thank you

[Hawthorne62]

Sorry, I left out the table structure - above is the query table

ID	Date Paid	CoPay	Pay Type	Provider	Category	Patient	Budget
4	2/28/2012	$154.27	2760	Intermed	Doctor	Jo Anne	$9,824.88
5	2/28/2012	$35.64	Their Web Site	ER Phy. - Todd Abrahams	Doctor	Jo Anne
6	4/9/2012	$29.09	2760	Mercy Hospital	Hospital	Jo Anne
7	4/9/2012	$187.46	2759	Mercy Hospital	Hospital	Jo Anne
8	4/9/2012	$154.27	2760	Intermed	Doctor	Jo Anne
9	4/16/2012	$52.70	POS	CVS Pharmacy	Pharmacy	Jo Anne
10	4/23/2012	$2.55	Cash	CVS Pharmacy	Pharmacy	Jo Anne
11	4/25/2012	$131.00	Visa	Gorham Family Dentistry	Dentist	Jo Anne
12	4/28/2012	$54.11	2761	Intermed	Doctor	Jo Anne
13	5/1/2012	$35.64	POS	Spectrum Medical Group	Doctor	Jo Anne
14	5/4/2012	$7.18	OLP	Mercy Hospital	Hospital	Jo Anne
15	5/21/2012	$9.99	Cash	Hannaford Pharmacy	Pharmacy	David
16	6/26/2012	$9.44	Cash	CVS Pharmacy	Pharmacy	Jo Anne
17	7/2/2012	$5.05	Cash	CVS Pharmacy	Pharmacy	David
18	7/8/2012	$9.65	Cash	CVS Pharmacy	Pharmacy	Jo Anne
19	7/11/2012	$0.00	$11.59 Not Charged	Southern Me. Orthopedic	Doctor	David
20	7/22/2012	$2.55	Cash	CVS Pharmacy	Pharmacy	Jo Anne
21	7/24/2012	$18.98	Cash	Hannaford Pharmacy	Pharmacy	David
22	8/5/2012	$18.48	Visa Debit	CVS Pharmacy	Pharmacy	Jo Anne
23	8/27/2012	$19.98	Cash	Hannaford Pharmacy	Pharmacy	David
24	8/30/2012	$71.76	OLP	Doctor Yorkey DO	Doctor	David
25	9/4/2012	$203.02	OLP	Mercy Hospital	Laboratory	David
26	9/16/2012	$6.56	Cash	CVS Pharmacy	Pharmacy	Jo Anne
27	9/23/2012	$9.44	Cash	CVS Pharmacy	Pharmacy	Jo Anne
28	10/8/2012	$10.80	Cash	CVS Pharmacy	Pharmacy	Jo Anne
29	1/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
30	2/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
32	3/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
33	4/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
34	5/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
35	6/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
36	7/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
37	8/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
38	9/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
39	10/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
40	11/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
41	12/1/2012	$556.24	Payroll Deduction	Cigna	Premium	David & Jo Anne
46	10/22/2012	$18.98	Cash	Hannaford Pharmacy	Pharmacy	David
47	10/25/2012	$131.00	Visa Debit	Gorham Family Dentistry	Dentist	Jo Anne
49	10/28/2012	$2.55	Cash	CVS Pharmacy	Pharmacy	Jo Anne
50	11/26/2012	$18.48	Cash	CVS Pharmacy	Pharmacy	Jo Anne
51	11/27/2012	$8.80	Cash	Hannaford Pharmacy	Pharmacy	David
52	12/23/2012	$10.42	Cash	CVS Pharmacy	Pharmacy	Jo Anne
53	12/23/2012	$7.08	Cash	CVS Pharmacy	Pharmacy	Jo Anne
54	12/23/2012	$11.07	Cash	CVS Pharmacy	Pharmacy	Jo Anne
57	12/27/2012	$57.87	Visa Debit	CVS Pharmacy	Pharmacy	Jo Anne
58	10/27/2012	$57.87	Cash	CVS Pharmacy	Pharmacy	Jo Anne
		$8,238.61					$9,824.88

[alcapps]

Are you wanting the subtotal of every row to give you a number left to budget or do you want to compare the totals. if you want to compare the totals you have that at the bottom. Budgetcomp = BudgetTotal - TotalSumofcopay
if you need subtotal. for each row that can be done as well.

Al

Date Paid By Year	Patient	Pay Type	Provider	Category	SumOfCoPay	BudgetComp
2012	David	$11.59 Not Charged	Southern Me. Orthopedic	Doctor	$0.00
2012	David	Cash	CVS Pharmacy	Pharmacy	$5.05
2012	David	Cash	Hannaford Pharmacy	Pharmacy	$76.73
2012	David	OLP	Doctor Yorkey DO	Doctor	$71.76
2012	David	OLP	Mercy Hospital	Laboratory	$203.02
2012	David & Jo Anne	Payroll Deduction	Cigna	Premium	$6,674.88
2012	Jo Anne	2759	Mercy Hospital	Hospital	$187.46
2012	Jo Anne	2760	Intermed	Doctor	$308.54
2012	Jo Anne	2760	Mercy Hospital	Hospital	$29.09
2012	Jo Anne	2761	Intermed	Doctor	$54.11
2012	Jo Anne	Cash	CVS Pharmacy	Pharmacy	$158.46
2012	Jo Anne	OLP	Mercy Hospital	Hospital	$7.18
2012	Jo Anne	POS	CVS Pharmacy	Pharmacy	$52.70
2012	Jo Anne	POS	Spectrum Medical Group	Doctor	$35.64
2012	Jo Anne	Their Web Site	ER Phy. - Todd Abrahams	Doctor	$35.64
2012	Jo Anne	Visa	Gorham Family Dentistry	Dentist	$131.00
2012	Jo Anne	Visa Debit	CVS Pharmacy	Pharmacy	$76.35
2012	Jo Anne	Visa Debit	Gorham Family Dentistry	Dentist	$131.00
					$8,238.61

I'm looking to take the Total of $8,238.61 in this query and compare it to the annual budget of $9,924.88. The total in the Copay column changes as entries are made so I'd like Access to keep tabs on how much we are spending as compated to the annual budget. How can I best do this? Thank you

[orange]

As you can see, responders are guessing at your table structure and your needs. We only know what you tell us, so to get more focused answers, give us more info.
Post a jpg of your tables and relationships.
Do you have a clear statement/definition of what you are trying to do? If so, could you post a copy.

Good luck with your project.

[Hawthorne62]

Sorry to be so confusing! What's left in the budged vs what has been spent would be very nice. How and where can I insert such an instruction - table or query
Thanks so much for your time and for sticking with me on this

[orange]

These links may be helpful:

http://www.rogersaccesslibrary.com/forum/topic238.html
http://support.microsoft.com/kb/304466

Good luck with your project