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.
John Vinson said: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]
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 said: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]