proper database setup

[Guest]

I have a new database that I have to setup. It basically involves accounts
and billed amounts. When the data is done, one would call up a record by
account number to obtain the current billing info and also to track the
billing history from year to year. A form would pop up to reveal the
appropriate information.

The account # and billed amount info comes from an outside source already in
Excel format, this can't be changed. So I would import the sheet into Access
and go from there. No problem so far.

My question is: for each year, should I have a separate table, and just have
the form based on a query? Or should I add the new year's information to the
existing previous year's table, and have one big database with a separate
field for each billed year? Which would make maintaining the data easier in
the years to come? One issue is that account numbers can change, be added, or
be removed from year to year. Billed amount can also change from year to
year. So the records would not necessarily be the same every year. These
changes already come this way in the Excel file.

Thanks if anyone has any suggestions.
Dino

 

[Joseph Meehan]

I have a new database that I have to setup. It basically involves
accounts and billed amounts. When the data is done, one would call up
a record by account number to obtain the current billing info and
also to track the billing history from year to year. A form would pop
up to reveal the appropriate information.

The account # and billed amount info comes from an outside source
already in Excel format, this can't be changed. So I would import the
sheet into Access and go from there. No problem so far.

My question is: for each year, should I have a separate table, and
just have the form based on a query? Or should I add the new year's
information to the existing previous year's table, and have one big
database with a separate field for each billed year? Which would make
maintaining the data easier in the years to come?

Neither of the above.

One table for the accounts (name address etc.)
One or more tables for transactions (likely one but there may be
conditions that would call for more than one)

Those tables would be related so you can easily fine all the
transactions for a single account and never run out of fields for additional
transactions.

DON'T make new tables for new years, it will just make things harder.
Make sure you have a date field and you can then always select this year's
data or any date range.

One issue is that
account numbers can change, be added, or be removed from year to
year. Billed amount can also change from year to year. So the records
would not necessarily be the same every year. These changes already
come this way in the Excel file.

There are ways of handling those issues. What you may want to do will
depend on the specific needs.

 

[UpRider]

Dino, clarification. Does the data from Excel happen just once each year?
If not, how often would you import data? If more than once, would changes
come as duplicates (with a changed field or two) of existing records?
UpRider