More Query Questions

  • Thread starter Thread starter JG
  • Start date Start date
J

JG

ok, I have 5 tables representing the acount information for each month.
Starting from Jan06 to May06. Each month the size of the table increase as
we board more accounts. So starting from Jan06 it had about 1070 account
records and each month it would increase. May06 accounts total 1290. Heres
my question, why is it when i do a query for all the months the total
records shown only adds up to 1070. The query schema is as follows.

tblJan06
pk-mid
gross income
net income

tblFeb06
pk-mid
gross income
net income

tblMar06
pk-mid
gross income
net income

tblApr06
pk-mid
gross income
net income

tblMay06
pk-mid
gross income
net income

all tables are linked by the pk-mid. I want to extract the Income for each
month, but the total records will not go higher than 1070. Any help on this
would be great. Thanks.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would help if you showed the query you are running.

OTOH, your design is incorrect. You should have only 1 table w/ a date
column:

Foo (table name?)
account_nbr
account_date
gross_income
net_income

The Primary Key would be the account_nbr and account_date columns.

Then your query would get all months/dates (January to March data):

SELECT Sum(gross_income) As SumGross, Sum(net_income) As SumNet
FROM Foo
WHERE account_date BETWEEN #1/1/2006# And #3/31/2006#

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

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

iQA/AwUBRKMTMYechKqOuFEgEQL46wCgvYaDrv/ETzL8zJ1OcNXzTey9TjgAn2QC
DHRE+3J0KinSx4pU1qNePNrO
=0Iij
-----END PGP SIGNATURE-----
 
all tables are linked by the pk-mid.
Is this a UNION query or did you JOIN on pk-mid?

If you joined from [tblJan06].[pk-mid] and it has only 1070 records then
that is the problem.

You need to use a single table instead of one for each month. Add a field
that designates the date (Month) and append them into one single table.
 
Back
Top