Welcome to SPN

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

Sign Up Now!

access database

Discussion in 'Information Technology' started by Karen, Nov 17, 2005.

  1. Karen

    Karen
    Expand Collapse
    Guest

    I have a database with alot of patient information with dates. Instead of
    re-creating the same patient info and add new info for the patient, how can I
    edit the old with new info and keep both.
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 13:40:42 -0800, "Karen"
    <Karen@discussions.microsoft.com> wrote:

    >I have a main table with all columns that are needed for keeping track of
    >patient information of supplements that are sent from different companies.
    >Another table with company Names, another table with supplement names. My
    >company is wanting to add dates we they will know monthly the cost of what is
    >being spent. I have that set up in queries already done. My question is:
    >how can I go to a previous date of patient info that supplements were sent
    >and add a new date and still have the old info stay there. We are wanting to
    >retain price spending.


    It sounds like you need more tables.

    The Patient informtion table should have JUST the patient
    identification info - name, contact information, and so on. It should
    NOT contain ANY supplement information.

    I'm not sure what a "supplement" is, or how it has to do with a date,
    but guessing here: you may want the following tables:

    Patients
    PatientID Primary Key Autonumber (or some unique stable ID)
    LastName
    FirstName
    <other bio information>

    Supplements
    SupplementID Primary Key <a product number, SKU number, or
    autonumber>
    SupplementName Text
    CompanyID <link to the primary key of Companies>
    <info about the supplement including current cost>

    SupplementsProvided
    PatientID <who it was provided to>
    SupplementID <what was provided>
    ProvisionDate Date/Time (default Date())
    ItemCost Currency

    You'ld use a Form to fill in the data, with VBA code on the subform
    based on SupplementsProvided to "push" the current cost into the
    ItemCost field. See the Northwind sample database Orders form for an
    example which does something very similar, or post back.

    John W. Vinson[MVP]
     
  4. Karen

    Karen
    Expand Collapse
    Guest

    "John Vinson" wrote:

    > On Wed, 16 Nov 2005 13:40:42 -0800, "Karen"
    > <Karen@discussions.microsoft.com> wrote:
    >
    > >I have a main table with all columns that are needed for keeping track of
    > >patient information of supplements that are sent from different companies.
    > >Another table with company Names, another table with supplement names. My
    > >company is wanting to add dates we they will know monthly the cost of what is
    > >being spent. I have that set up in queries already done. My question is:
    > >how can I go to a previous date of patient info that supplements were sent
    > >and add a new date and still have the old info stay there. We are wanting to
    > >retain price spending.

    >
    > It sounds like you need more tables.
    >
    > The Patient informtion table should have JUST the patient
    > identification info - name, contact information, and so on. It should
    > NOT contain ANY supplement information.
    >
    > I'm not sure what a "supplement" is, or how it has to do with a date,
    > but guessing here: you may want the following tables:
    >
    > Patients
    > PatientID Primary Key Autonumber (or some unique stable ID)
    > LastName
    > FirstName
    > <other bio information>
    >
    > Supplements
    > SupplementID Primary Key <a product number, SKU number, or
    > autonumber>
    > SupplementName Text
    > CompanyID <link to the primary key of Companies>
    > <info about the supplement including current cost>
    >
    > SupplementsProvided
    > PatientID <who it was provided to>
    > SupplementID <what was provided>
    > ProvisionDate Date/Time (default Date())
    > ItemCost Currency
    >
    > You'ld use a Form to fill in the data, with VBA code on the subform
    > based on SupplementsProvided to "push" the current cost into the
    > ItemCost field. See the Northwind sample database Orders form for an
    > example which does something very similar, or post back.
    >
    > John W. Vinson[MVP]
    >
     
  5. Karen

    Karen
    Expand Collapse
    Guest

    I have 1 main table with all information of patient
    w/ columns/date/name/address/phone#/supply companies as drop down
    boxes/supplements(drink when patient is not eating enough}drop down box and
    in Reports or Form - I have it when they choose a supplement the company will
    automactic fall in the correct area in the form and so forth. I am wanting
    to calculate what is spent monthly for each comppany and I have already
    completed this. But what I am wondering how to do, is - when a patient is
    sent more supplements withing the same months, I would like to just go to
    the previous entery and be able to just edit date and supplement new order
    and keep new and old. How can I do this without overriding previous entry.
    Hope this makes more sense.

    "John Vinson" wrote:

    > On Wed, 16 Nov 2005 13:40:42 -0800, "Karen"
    > <Karen@discussions.microsoft.com> wrote:
    >
    > >I have a main table with all columns that are needed for keeping track of
    > >patient information of supplements that are sent from different companies.
    > >Another table with company Names, another table with supplement names. My
    > >company is wanting to add dates we they will know monthly the cost of what is
    > >being spent. I have that set up in queries already done. My question is:
    > >how can I go to a previous date of patient info that supplements were sent
    > >and add a new date and still have the old info stay there. We are wanting to
    > >retain price spending.

    >
    > It sounds like you need more tables.
    >
    > The Patient informtion table should have JUST the patient
    > identification info - name, contact information, and so on. It should
    > NOT contain ANY supplement information.
    >
    > I'm not sure what a "supplement" is, or how it has to do with a date,
    > but guessing here: you may want the following tables:
    >
    > Patients
    > PatientID Primary Key Autonumber (or some unique stable ID)
    > LastName
    > FirstName
    > <other bio information>
    >
    > Supplements
    > SupplementID Primary Key <a product number, SKU number, or
    > autonumber>
    > SupplementName Text
    > CompanyID <link to the primary key of Companies>
    > <info about the supplement including current cost>
    >
    > SupplementsProvided
    > PatientID <who it was provided to>
    > SupplementID <what was provided>
    > ProvisionDate Date/Time (default Date())
    > ItemCost Currency
    >
    > You'ld use a Form to fill in the data, with VBA code on the subform
    > based on SupplementsProvided to "push" the current cost into the
    > ItemCost field. See the Northwind sample database Orders form for an
    > example which does something very similar, or post back.
    >
    > John W. Vinson[MVP]
    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 17 Nov 2005 07:26:11 -0800, "Karen"
    <Karen@discussions.microsoft.com> wrote:

    >I have 1 main table with all information of patient
    >w/ columns/date/name/address/phone#/supply companies as drop down
    >boxes/supplements(drink when patient is not eating enough}drop down box and
    >in Reports or Form - I have it when they choose a supplement the company will
    >automactic fall in the correct area in the form and so forth. I am wanting
    >to calculate what is spent monthly for each comppany and I have already
    >completed this. But what I am wondering how to do, is - when a patient is
    >sent more supplements withing the same months, I would like to just go to
    >the previous entery and be able to just edit date and supplement new order
    >and keep new and old. How can I do this without overriding previous entry.
    >Hope this makes more sense.


    You're misusing Access.

    Access isn't a document writer. It's a relational database.

    Keeping everything in one main table IS SIMPLY WRONG, for the very
    reason you state - you now have to either overwrite the previous
    supplement information, or add the patient again as a new record,
    reentering all the patient information a second time.

    This is not because Access is limiting you - it's because your table
    design is incorrect!

    Access is a relational database; use it relationally. See the
    resources at
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    for some examples. In your case you need AT LEAST three tables:
    Patients (containing only patient biographical info); Supplements,
    listing the supplements available, their current cost, the company
    that supplies them, etc; and Sales (or whatever you want to call it),
    where each row in the table has a PatientID (indicating who got the
    supplement), SupplementID (what they got), Price (the price as of the
    time they got it), PurchaseDate (when they got it).

    With this design you can very easily copy a record from the Sales
    table to a new record with a different date, and run totals queries to
    sum up the patient's cost over time.

    John W. Vinson[MVP]
     
  7. Karen

    Karen
    Expand Collapse
    Guest

    thank you John

    "John Vinson" wrote:

    > On Thu, 17 Nov 2005 07:26:11 -0800, "Karen"
    > <Karen@discussions.microsoft.com> wrote:
    >
    > >I have 1 main table with all information of patient
    > >w/ columns/date/name/address/phone#/supply companies as drop down
    > >boxes/supplements(drink when patient is not eating enough}drop down box and
    > >in Reports or Form - I have it when they choose a supplement the company will
    > >automactic fall in the correct area in the form and so forth. I am wanting
    > >to calculate what is spent monthly for each comppany and I have already
    > >completed this. But what I am wondering how to do, is - when a patient is
    > >sent more supplements withing the same months, I would like to just go to
    > >the previous entery and be able to just edit date and supplement new order
    > >and keep new and old. How can I do this without overriding previous entry.
    > >Hope this makes more sense.

    >
    > You're misusing Access.
    >
    > Access isn't a document writer. It's a relational database.
    >
    > Keeping everything in one main table IS SIMPLY WRONG, for the very
    > reason you state - you now have to either overwrite the previous
    > supplement information, or add the patient again as a new record,
    > reentering all the patient information a second time.
    >
    > This is not because Access is limiting you - it's because your table
    > design is incorrect!
    >
    > Access is a relational database; use it relationally. See the
    > resources at
    > http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    > for some examples. In your case you need AT LEAST three tables:
    > Patients (containing only patient biographical info); Supplements,
    > listing the supplements available, their current cost, the company
    > that supplies them, etc; and Sales (or whatever you want to call it),
    > where each row in the table has a PatientID (indicating who got the
    > supplement), SupplementID (what they got), Price (the price as of the
    > time they got it), PurchaseDate (when they got it).
    >
    > With this design you can very easily copy a record from the Sales
    > table to a new record with a different date, and run totals queries to
    > sum up the patient's cost over time.
    >
    > John W. Vinson[MVP]
    >
     

Share This Page