total column in a query

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

Guest

My table is like ths:

Account Wages TrxDate
316-71600 $5000 7/1/2004
316-71600 $500 7/20/2004
317-71600 $6000 7/14/2004
320-71600 $5000 6/30/2004
335-71600 $7000 7/20/2004
316-72300 $40000 7/30/2004
317-70500 $200 7/27/2004

In a query, I am trying to create a "total wage" column that sums all
accounts containind "71600" for the month of July. Can anyone help?
 
SELECT Sum([Wages]) As TotalWages
FROM MyTable
WHERE Mid([Account], InStr([Account], "-") + 1) = "71600"
AND Format(TrxDate, "yyyymm") = "200407"
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT Account, Sum(Wages) As TotalWages
FROM table_name
WHERE Month(TrxDate) = 7
AND Right(Account,5) = "71600"
GROUP BY Account

This will probably be slow-ish, 'cuz using Month() & Right() cause the
query to scan the whole table in order to parse the column values
requested by those functions. If you wanted the query to run faster you
could create new columns that hold the data you want: one column holds
the last 5 digits of the Account # and the other column holds the month
of the TrxDate. Then index the new columns and change the WHERE clause
to:

WHERE TrxMonth = 7 AND SubAccount = "71600"

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

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

iQA+AwUBQRaDyIechKqOuFEgEQLsQQCY5q0WS5hnHC8ejx/vqA9EIYm40wCg8o6P
l8lGZaZQrMiWPhfkUfM98Ck=
=3Y+a
-----END PGP SIGNATURE-----
 
Back
Top