Total in Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to create a total at the bottom of a data page (not report)?
Also, I am trying the following Running total and get #Error. I would
appreciate the help.

SELECT CurrentMonth.invoicenumber AS InvAlias, Sum(CurrentMonth.GP_Dollars)
AS SumOfGP_Dollars,
Format(DSum("GP_Dollars","CurrentMonth","[invoicenumber]<=" & [InvAlias] &
""),"$0,000.00") AS RunTot
FROM CurrentMonth
GROUP BY CurrentMonth.invoicenumber;
 
Splendalisa said:
Is it possible to create a total at the bottom of a data page (not report)?
Also, I am trying the following Running total and get #Error. I would
appreciate the help.

SELECT CurrentMonth.invoicenumber AS InvAlias, Sum(CurrentMonth.GP_Dollars)
AS SumOfGP_Dollars,
Format(DSum("GP_Dollars","CurrentMonth","[invoicenumber]<=" & [InvAlias] &
""),"$0,000.00") AS RunTot
FROM CurrentMonth
GROUP BY CurrentMonth.invoicenumber;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There are Data Access Pages, which are web forms. Is that what you mean
by "data page"? When a query is just opened from the Database window
it opens in Datasheet view. Is that what you mean by data page?

If you mean a Datasheet view, then, no, totals can't be appended to the
bottom line of a datasheet view. You'll have to use a form or report or
Data Access Page (usually the total goes in the Footer section).

Running sum subqueries (that's what the DSum() acts as) have to refer to
the main query identifiers. Just alias the table name & use that alias
in the DSum():

SELECT InvoiceNumber AS InvAlias, Sum(GP_Dollars) AS SumOfGP_Dollars,
DSum("GP_Dollars","CurrentMonth","InvoiceNumber<=" & C.InvoiceNumber) AS
RunTot
FROM CurrentMonth As C
GROUP BY InvoiceNumber

If InvoiceNumber is a string data type put single-quote delimiters
around it (be aware of line wrap):

DSum("GP_Dollars","CurrentMonth","InvoiceNumber<='" & C.InvoiceNumber &
"'")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi+AfoechKqOuFEgEQKcqgCffnmRP62TUBYYNLp5NxRR4YqIna0AnAhe
jBOZ3RUQLsjHkgjSRiBkgFHP
=/Yuo
-----END PGP SIGNATURE-----
 
Back
Top