Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

proper database setup

Discussion in 'Information Technology' started by Dino, Jul 28, 2006.

  1. Dino

    Dino
    Expand Collapse
    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
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Dino wrote:
    > 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.

    >
    > Thanks if anyone has any suggestions.
    > Dino




    --
    Joseph Meehan

    Dia duit
     
  4. UpRider

    UpRider
    Expand Collapse
    Guest

    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

    "Dino" <Dino@discussions.microsoft.com> wrote in message
    news:6AF55741-A050-4FD4-A0BE-FF03FEB69E3D@microsoft.com...
    >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
    >
     

Share This Page