not all records being query

  • 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.
 
I'm afraid you are starting out with the wrong design! You are
effectively "storing" data in the table name (by selecting the table to
store to based on the month), while you should have only one table with
an extra field for the month. Then, you could build a cross-tab query on
that table, using the month field as the column headings, and the job is
done.
Note: this is just the first problem you will encounter under your
current design; experience says problems due to bad data designs tend to
multiply like an avalanche as application design progresses, so you'd
best get your data design right before you move any further.

HTH,
Nikos
 
Back
Top