SubTotals on Forms

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


"Stacey Crowhurst" wrote:

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!

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


"Stacey Crowhurst" wrote:

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

"KARL DEWEY" wrote:

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


"Stacey Crowhurst" wrote:

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!