SubTotals on Forms

[Stacey Crowhurst]

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[Stacey Crowhurst]

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[Stacey Crowhurst]

Lets see....

1. Form (Transactions) uses the Transaction General Table
2. SubForm 1 (Details) uses the Transaction Details Table
3. SubForm 2 (Check Total) uses the Check Total Query
4. SubForm 3 (Invoice Total) uses the Invoice Total Query

On the form the Transactions General is in the header. The Transactions
Detail is a suform in the details section of the form. The Check Total is a
suform in the details section of the form. The Invoice Total is a suform in
the details section of the form.

Let me know if you need any other information. Thank you for your help.
Stacey

I removed [Check No] as you can not use it when summing invoices.

How many queries are you using for your form and subform? It seems as
though this query was used in the details.
--
KARL DEWEY
Build a little - Test a little

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[KARL DEWEY]

when I go to open my form it wants me to enter the parameter value for
"Check No".
Do you have a text box with "Check No" as source in the SubForm 3? If so,
remove it.
--
KARL DEWEY
Build a little - Test a little

Lets see....

1. Form (Transactions) uses the Transaction General Table
2. SubForm 1 (Details) uses the Transaction Details Table
3. SubForm 2 (Check Total) uses the Check Total Query
4. SubForm 3 (Invoice Total) uses the Invoice Total Query

On the form the Transactions General is in the header. The Transactions
Detail is a suform in the details section of the form. The Check Total is a
suform in the details section of the form. The Invoice Total is a suform in
the details section of the form.

Let me know if you need any other information. Thank you for your help.
Stacey

I removed [Check No] as you can not use it when summing invoices.

How many queries are you using for your form and subform? It seems as
though this query was used in the details.
--
KARL DEWEY
Build a little - Test a little

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

:

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[Stacey Crowhurst]

No. SubForm 3 draws on the invoice query and only has two items on the
subform.

1. label "Invoice Total"
2. text box "sumoflineamount"

This subform works just fine when I open it. It shows the correct invoice
totals. But when I open the main form it is blank.

Here is the SQL on the query for SubForm 3:
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID]=[Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

"Check No".
Do you have a text box with "Check No" as source in the SubForm 3? If so,
remove it.
--
KARL DEWEY
Build a little - Test a little

Lets see....

1. Form (Transactions) uses the Transaction General Table
2. SubForm 1 (Details) uses the Transaction Details Table
3. SubForm 2 (Check Total) uses the Check Total Query
4. SubForm 3 (Invoice Total) uses the Invoice Total Query

On the form the Transactions General is in the header. The Transactions
Detail is a suform in the details section of the form. The Check Total is a
suform in the details section of the form. The Invoice Total is a suform in
the details section of the form.

Let me know if you need any other information. Thank you for your help.
Stacey

I removed [Check No] as you can not use it when summing invoices.

How many queries are you using for your form and subform? It seems as
though this query was used in the details.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

:

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[KARL DEWEY]

Back a ways you said '...when I go to open my form it wants me to enter the
parameter value for "Check No"...'

Do you have your form and suborm Master/Child links set?
--
KARL DEWEY
Build a little - Test a little

No. SubForm 3 draws on the invoice query and only has two items on the
subform.

1. label "Invoice Total"
2. text box "sumoflineamount"

This subform works just fine when I open it. It shows the correct invoice
totals. But when I open the main form it is blank.

Here is the SQL on the query for SubForm 3:
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID]=[Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

when I go to open my form it wants me to enter the parameter value for

"Check No".
Do you have a text box with "Check No" as source in the SubForm 3? If so,
remove it.
--
KARL DEWEY
Build a little - Test a little

Lets see....

1. Form (Transactions) uses the Transaction General Table
2. SubForm 1 (Details) uses the Transaction Details Table
3. SubForm 2 (Check Total) uses the Check Total Query
4. SubForm 3 (Invoice Total) uses the Invoice Total Query

On the form the Transactions General is in the header. The Transactions
Detail is a suform in the details section of the form. The Check Total is a
suform in the details section of the form. The Invoice Total is a suform in
the details section of the form.

Let me know if you need any other information. Thank you for your help.
Stacey


:

I removed [Check No] as you can not use it when summing invoices.

How many queries are you using for your form and subform? It seems as
though this query was used in the details.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

:

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[Stacey Crowhurst]

How can I check the Master/Child links?

Back a ways you said '...when I go to open my form it wants me to enter the
parameter value for "Check No"...'

Do you have your form and suborm Master/Child links set?
--
KARL DEWEY
Build a little - Test a little

No. SubForm 3 draws on the invoice query and only has two items on the
subform.

1. label "Invoice Total"
2. text box "sumoflineamount"

This subform works just fine when I open it. It shows the correct invoice
totals. But when I open the main form it is blank.

Here is the SQL on the query for SubForm 3:
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID]=[Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

when I go to open my form it wants me to enter the parameter value for
"Check No".
Do you have a text box with "Check No" as source in the SubForm 3? If so,
remove it.
--
KARL DEWEY
Build a little - Test a little


:

Lets see....

1. Form (Transactions) uses the Transaction General Table
2. SubForm 1 (Details) uses the Transaction Details Table
3. SubForm 2 (Check Total) uses the Check Total Query
4. SubForm 3 (Invoice Total) uses the Invoice Total Query

On the form the Transactions General is in the header. The Transactions
Detail is a suform in the details section of the form. The Check Total is a
suform in the details section of the form. The Invoice Total is a suform in
the details section of the form.

Let me know if you need any other information. Thank you for your help.
Stacey


:

I removed [Check No] as you can not use it when summing invoices.

How many queries are you using for your form and subform? It seems as
though this query was used in the details.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

:

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!

 

[Stacey Crowhurst]

I figured it out! I had to put my invoice total subform on the detail
subform so that it could link by invoice number. The check total subform can
stay on the general form becuase it can link by check number there. Thanks
for leading me down the right path!

Stacey

Back a ways you said '...when I go to open my form it wants me to enter the
parameter value for "Check No"...'

Do you have your form and suborm Master/Child links set?
--
KARL DEWEY
Build a little - Test a little

No. SubForm 3 draws on the invoice query and only has two items on the
subform.

1. label "Invoice Total"
2. text box "sumoflineamount"

This subform works just fine when I open it. It shows the correct invoice
totals. But when I open the main form it is blank.

Here is the SQL on the query for SubForm 3:
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID]=[Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

when I go to open my form it wants me to enter the parameter value for
"Check No".
Do you have a text box with "Check No" as source in the SubForm 3? If so,
remove it.
--
KARL DEWEY
Build a little - Test a little


:

Lets see....

1. Form (Transactions) uses the Transaction General Table
2. SubForm 1 (Details) uses the Transaction Details Table
3. SubForm 2 (Check Total) uses the Check Total Query
4. SubForm 3 (Invoice Total) uses the Invoice Total Query

On the form the Transactions General is in the header. The Transactions
Detail is a suform in the details section of the form. The Check Total is a
suform in the details section of the form. The Invoice Total is a suform in
the details section of the form.

Let me know if you need any other information. Thank you for your help.
Stacey


:

I removed [Check No] as you can not use it when summing invoices.

How many queries are you using for your form and subform? It seems as
though this query was used in the details.
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl. I tried the SQL below and when I go to open my form it wants me to
enter the parameter value for "Check No". Any other ideas?

Thanks.
Stacey

:

Try this --
SELECT [Transaction - Details].[Invoice No], Sum([Transaction -
Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - Details].[Invoice No];

--
KARL DEWEY
Build a little - Test a little


:

Hi. I have a form that shows transactions (checks paid to vendors). In the
header I have the check number and check date. In the detail section I have
a subform that shows the transaction detail (i.e. invoice number, dollar
amount, account charged). I want to have two totals at the bottom of the
form: check total and invoice total. I need the distinction because often
one check will pay a vendor for two or more invoices. The check total refers
to a query and runs fine. I have the query set up for the invoice total but
it doesn't work as I'd expect.

For Example:
AAA Car Washing was paid $555 with check 2233. There are five lines of
transaction detail for that check.
1. Invoice 10 $5 labor
2. Invoice 10 $245 soap
3. Invoice 11 $5 labor
4. Invoice 11 $200 soap
5. Invoice 11 $100 fragrance
So the check total is $555; invoice 10 total is $250 and invoice 11 total is
$305.
As I click through the five lines of the subform I expect the check total to
always read $555 which it does. I expect the invoice total to read $250
until I reach line 3 when it should change to $305.

Here is the invoice total SQL:

SELECT [Transaction - General].[Check No], [Transaction - Details].[Invoice
No], Sum([Transaction - Details].[Line Amount]) AS [SumOfLine Amount]
FROM [Transaction - Details] INNER JOIN [Transaction - General] ON
[Transaction - Details].[Trans ID] = [Transaction - General].[Trans ID]
GROUP BY [Transaction - General].[Check No], [Transaction -
Details].[Invoice No];

I’m open to any suggestions you may have. Perhaps I shouldn't have totals
on my forms? Anyway, thanks!