sum two columns

[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];

 

[Troy]

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];

 

[Duane Hookom]

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

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];

 

[Troy]

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

= [qry Total Consignor Paid]![GrandTotal]

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

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];

 

[Duane Hookom]

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

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

= [qry Total Consignor Paid]![GrandTotal]

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

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];

 

[Troy]

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!

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

= [qry Total Consignor Paid]![GrandTotal]

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

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];