sum two columns

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

I'm trying to get the sum of two columns so I can display it in text box.
Instead of showing the total of both columns it shows the total for each
record. I want the total for both columns for each consignor.


SELECT Credit.[Cash Refund], Credit.[Store Credit], Sum([cash
refund])+Sum([store credit]) AS GrandTotal, Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Cash Refund], Credit.[Store Credit], Credit.[Consignor
Number];
 
Hmm, this shows my totals like I want, but when I try to reference the expr
from a text box I get the dreaded #Name? error. What am I doing wrong? I
feel I am close.

SELECT Sum([cash refund])+Sum([store credit]) AS GrandTotal,
Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Consignor Number];
 
You mention a text box but don't provide the control source or the section of
the report (or is it a form) containing the text box. Is the sql you provided
the Record Source of the report (or is it a form)?
--
Duane Hookom
Microsoft Access MVP


Troy said:
Hmm, this shows my totals like I want, but when I try to reference the expr
from a text box I get the dreaded #Name? error. What am I doing wrong? I
feel I am close.

SELECT Sum([cash refund])+Sum([store credit]) AS GrandTotal,
Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Consignor Number];



Troy said:
I'm trying to get the sum of two columns so I can display it in text box.
Instead of showing the total of both columns it shows the total for each
record. I want the total for both columns for each consignor.


SELECT Credit.[Cash Refund], Credit.[Store Credit], Sum([cash
refund])+Sum([store credit]) AS GrandTotal, Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Cash Refund], Credit.[Store Credit], Credit.[Consignor
Number];
Click to expand...
Click to expand...
 
Sorry, here is the control source of the text box on my form.

= [qry Total Consignor Paid]![GrandTotal]


Duane Hookom said:
You mention a text box but don't provide the control source or the section of
the report (or is it a form) containing the text box. Is the sql you provided
the Record Source of the report (or is it a form)?
--
Duane Hookom
Microsoft Access MVP


Troy said:
Hmm, this shows my totals like I want, but when I try to reference the expr
from a text box I get the dreaded #Name? error. What am I doing wrong? I
feel I am close.

SELECT Sum([cash refund])+Sum([store credit]) AS GrandTotal,
Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Consignor Number];



Troy said:
I'm trying to get the sum of two columns so I can display it in text box.
Instead of showing the total of both columns it shows the total for each
record. I want the total for both columns for each consignor.


SELECT Credit.[Cash Refund], Credit.[Store Credit], Sum([cash
refund])+Sum([store credit]) AS GrandTotal, Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Cash Refund], Credit.[Store Credit], Credit.[Consignor
Number];
Click to expand...
Click to expand...
Click to expand...
 
You can't bind a control to a query. Place the query in the form's record
source and set the control source to:
=[GrandTotal]
If the form must be editable, you can use a subform or DLookup() to display
the value.
=DLookup("GrandTotal","[qry Total Consignor Paid]")
--
Duane Hookom
Microsoft Access MVP


Troy said:
Sorry, here is the control source of the text box on my form.

= [qry Total Consignor Paid]![GrandTotal]


Duane Hookom said:
You mention a text box but don't provide the control source or the section of
the report (or is it a form) containing the text box. Is the sql you provided
the Record Source of the report (or is it a form)?
--
Duane Hookom
Microsoft Access MVP


Troy said:
Hmm, this shows my totals like I want, but when I try to reference the expr
from a text box I get the dreaded #Name? error. What am I doing wrong? I
feel I am close.

SELECT Sum([cash refund])+Sum([store credit]) AS GrandTotal,
Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Consignor Number];



:

I'm trying to get the sum of two columns so I can display it in text box.
Instead of showing the total of both columns it shows the total for each
record. I want the total for both columns for each consignor.


SELECT Credit.[Cash Refund], Credit.[Store Credit], Sum([cash
refund])+Sum([store credit]) AS GrandTotal, Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Cash Refund], Credit.[Store Credit], Credit.[Consignor
Number];
Click to expand...
Click to expand...
Click to expand...
 
hey nevermind I thought of a better way to display the total. I added a text
box in the footer of my subform and caculated the sum there using
=Sum([cash refund]+[store credit])
works like a champ!

Troy said:
Sorry, here is the control source of the text box on my form.

= [qry Total Consignor Paid]![GrandTotal]


Duane Hookom said:
You mention a text box but don't provide the control source or the section of
the report (or is it a form) containing the text box. Is the sql you provided
the Record Source of the report (or is it a form)?
--
Duane Hookom
Microsoft Access MVP


Troy said:
Hmm, this shows my totals like I want, but when I try to reference the expr
from a text box I get the dreaded #Name? error. What am I doing wrong? I
feel I am close.

SELECT Sum([cash refund])+Sum([store credit]) AS GrandTotal,
Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Consignor Number];



:

I'm trying to get the sum of two columns so I can display it in text box.
Instead of showing the total of both columns it shows the total for each
record. I want the total for both columns for each consignor.


SELECT Credit.[Cash Refund], Credit.[Store Credit], Sum([cash
refund])+Sum([store credit]) AS GrandTotal, Credit.[Consignor Number]
FROM Credit
GROUP BY Credit.[Cash Refund], Credit.[Store Credit], Credit.[Consignor
Number];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top