Vaccine Data BASE HElp

[julius edlavitch]

I have done computers prior to windows
but not database. I am pediatrician and am trying to learn access to make
electronic medical records for our group practice.

I am making a table. It seems easy. I use patient's unique account number as
key code. then last name, first name, and birthdate. These are fields in
table.

Now the confusing part
A child gets 5 DTaP vaccines scattered throughout first five years of life.
A DTaP shot has`a unique medical code. When we enter the bills in for the
past 10 years our billing data base has each shot entered using this unique
code and the date given.

When i design a table i list 5 DTaP shots each in a field with the data to
be entered as a date. I may use the unique code rather than the word DTaP BUT
I'd like to have booth. Because when i print out the vaccine record for this
child for school i want the print out to say DTaP but i also want the field
to relate to the unique billing code so all the shots in the past will fit
ino the date spaces of the five shots.

Now here is my problem if you followed the above
a table will not let you enter more than one DTaP field or more than one
unique medical DTaP code moreover the 5 shots are given at 5 different dates
so if i want to relate this to five unique dates What do i do?

I would like the form or report to look like this

Unique Child's account code 666666
Last Name Doe
First Name John
Birth Date 06/06/1998
DTaP 65789 08/06/1998 (65789 is unique medical colde for DTaP)
DTaP 65789 12/06/1998
DTaP 65789 02/06/2000
DTaP 65789 08/06/2004

once i get this concept down
then i can add all the other childhood shots and
Voila I made a vaccine data base to be printed out for schools and a
paperless beginning to a child's medical record

Julius Edlavitch
http://www.pedschat.org

 

[Douglas J. Steele]

In actual fact, you should have 2 tables. One table would contain the
information about the child (Account Code, Last Name, First Name and Birth
Date), which the other table would contain one row for each shot the child
has. (something like Account Code, Shot Name, Medical Code and Shot Date)

 

[julius edlavitch]

Thanks for your prompt reply Doug

I will try this tomorrow anmd see how it goes
I will need to see how to make multiple rows
for same shot but different dates
I guess this would mean every shot given for every child would be in a row
in this table

what if i add other vaccines
like
comvax
chicken pox
MMR
Hept B
Hept A
etc
do i make a field for each of these with their medical codes
or a separate table for each vaccine type
It would be nice to have a form at each visit for the nurse to enter the shots
of course they would be entered automatically when charge ticket is data
entered

But if all kids`have their sots in one table
what if i just wanted to see john does whole shot record

I feel this should all be easy and obvious
but i am a real newbie but love to learn

DR J

Dr J

 

[MacDermott]

I think you will love the power of queries when you learn how to use them to
pull together just the data you want.

Suppose you have a table, as Doug suggested, with a row for each shot given
to each child.
A query can show you as many fields as you like from that table, restricted
by whatever criteria you like.
If you want to see only the DTaP shots, you can do that.
If you want to see only the shots given to one particular child, you can
do that.
If you want to see all the shots given in a particular date range, you
can do that.
If you want to see a list of all the children who have received fewer
than the full 5 DTaP shots, you can do that.
You can also "mix and match" these various criteria.

HTH
- Turtle

 

[julius edlavitch]

Thank You
Excited to use both of you ideas
Julius

 

[julius edlavitch]

I am making progress

I made three tables
1.basic info acct #, name, brth date, SS #
2.Acct #, vaccination, vaccination code, Date Given
3. two fields vaccination and vaccination code (put in all possible
vaccine types with their specific identity code

I learned how to relate them in relationship window

I then put in some test data in the table
two fake patients with basic info and a few shots

I then made a form
THIS IS Tonights question

The form automatically whe the key account number was entered showed
precisely the vaccines and dates given this account number had automatically
Cool
the link worked

UNCOOL
The name and birthdate of the account number did not fill in automatic on
the form like the vaccines did
Shouldn't this happen automatically
julius

 

[MacDermott]

That sounds pretty good so far.
If it were me, I'd leave the vaccination field out of your second table, and
just store the vaccination code.
That way you wouldn't be storing things in two places.
Those codes don't change, do they?
I mean, there can be new ones, but a code like 12345 won't mean DTaP
today and something else next year, will it?

I'm not at all sure how your form is set up.
I'd suggest a form based on your first table, with the second table as a
subform.
(If you've set up the relationship correctly, creating an autoform from
the first table should automatically put the second one in as a subform, but
of course this can also be done manually.)
In Design View, I'd open the subform and change the code textbox to a
combobox (I'll call it cboCode), using the third table as a rowsource.
Make sure you set its Columns property to 2.
(please post back if you need help with this.)
Then add another textbox to the subform, and set its controlsource like
this:
= cboCode.Column(1)

Now the top of your form should display patients, and the subform all the
shots for whichever patient is displayed.
I'm guessing that this is the form you'll use most often.
However, if you do something like a smallpox clinic, where you're giving the
same shot to different patients all day, it would be more convenient to set
it up with the shot on top and just be able to add patients in the subform.
Please post back if this is a need.

HTH
- Turtle

 

[julius edlavitch]

Turtle
What you are describing is what I need to do
But it may be above my head just yet

I am going to library and doing tutorial 2 hours daily
so I may get there

We don't do the small pox or any shots in that mass mode
maybe flu shot but none available to give this year

hope never to have to give small pox shot like you asked
that would mean those terrorist got the upper hand
Piss on that

I am going to continue tutorials and play with your last idea

YOU HAVE BEEN A GREAT HELP!!!

DR J
http://www.pedschat.org
winner of the 2001 indv achiev distance learning in medicine award from
USDLA.ORG

 

[julius edlavitch]

Turtle
What you are describing is what I need to do
But it may be above my head just yet

I am going to library and doing tutorial 2 hours daily
so I may get there

We don't do the small pox or any shots in that mass mode
maybe flu shot but none available to give this year

hope never to have to give small pox shot like you asked
that would mean those terrorist got the upper hand
Piss on that

I am going to continue tutorials and play with your last idea

YOU HAVE BEEN A GREAT HELP!!!

DR J
http://www.pedschat.org
winner of the 2001 indv achiev distance learning in medicine award from
USDLA.ORG

 

[MacDermott]

Glad you're continuing to enjoy this.

One extremely important step in creating a working database -
a step often overlooked by beginners -
is the design phase, where you decide what it is that you want the
database to do.
In a simple way, that's what we've just done -
I've suggested a form layout, and asked about a need which would not be
well served by this layout.
You've confirmed that the suggested layout meets your needs, and that
the other need is quite rare for you. (It can still be added later, if the
need arises.)

If you have specific questions about implementing my suggestions, please
post back.

- Turtle

 

[julius edlavitch]

Thank you for your kindness and help
Julius Edlavitch

 

[julius edlavitch]

Dear Mac
I am starting with learning and the vaccine part
If this goes well
I hope to See if I can design an electronic Medical record for our office
but one step at a time
Dr J

 

[julius edlavitch]

Dear HTH
I spent most of my Sunday trying to make the form

Unfortunately
I felt I was close but not successful

Table one has basic patient info
Account number ( Primary key)
name birthdate ss number

Table two
patient account number primary key
Shot specific Code ( Polio 90713 would be number here if polio was given)
Date shot given

Table three
Shot Code Column one
Name for each shot code column two
90713 Polio
90710 Measles
Shot code is primary key

I then related tables
tasble one to two using patient account number
table two to three using shot code

as related fileds

I then created a form
with table one
and then a subform with
tables two and three
the sub form

had
shot code
immunization
shot date

I then tried to do what you suggested for hours but was not sucessful

I put cboCode in the control source for shot code
and put a 2 in Column Count ( ?? if this is correct place)

i created a 4th text box and put in cboCode.Column(1)
in control source

This is what happened
In Form view you see the header and none of the detail part of the form

In the Data Form

you have the combo box come down and the vacine names are listed rather than
the shot code
You can not click them anyway they are just there
also
#name? fills in the row for immunization name and shot code despite the
combo box

I probably learned a lot but got no where

I want the form to be a place where the combo box comes down and the nurse
clicks Polio and the date given fills in and the shot code fills in
automatically

then the nurse gives a second shot that day and that is entered and then she
gives a third shat that date and that is entered
into a form

Also once I get this done I want to make a report for each patient that
shows all thier shots

each kids gets a lot of shots eventually

so the report at 7 yo would show 4 polio's two MMR's etc etc

Hope this makes sense

Julius

 

[MacDermott]

Indeed, it sounds as if you were almost there!

First of all, you said you related the tables.
Did you enforce referential integrity with cascade updates?
(Let me know if those aren't terms you're familiar with.)

The subform should be based only on Table2.
Initially, let's just have a textbox for shotcode.
And another one which can be filled in with today's date.
These should be bound (using ControlSource) to the appropriate fields in
Table2.

Post back once you get that working, and we'll convert the shotcode textbox
to a combobox.
Then we can move on to further parts.

HTH
- Turtle

 

[julius edlavitch]

I saw these terms in a video at 3am this morning
I think I can check these boxes

I'll let you know

Dr J

 

[julius edlavitch]

Simps Turtle

After all the time i spent Sunday wrking in forms
It took about 5 minutes to do your last request

I had to disconnect the relationship between two and three tables to change
shot code from number to text although it is a 100% number always with no
hyphens

I also wish I understood the terms referential integrety better and cascading
I will watch the movie again

Anyway it is done and looks good
I put no header on form
I have a table with basic data on top and the two items in a subtable
the first record has a makeup name and data
the sub form two lines i added one vaccine code and date
but when i clicked enter a new line automatically opened up
but entering the same or different vaccine code with a different date would
not be accepted
It had message that it would now allow repeat info
"the changes you requested to the table were not sucessful because they
would create duplicate values in the index primary key or relationships.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the idex to allow duplicate entries"
i even entered different code and date


it would be nice if the date box had todays date as the default date
that could be changed later if needed

thanks
DRJ

 

[julius edlavitch]

Dear HTH

Do you like tea?
If you trust me with your mailing address, I'd like to send you a holiday
present to thank you for all your patient help

Dr J

 

[MacDermott]

I'm thinking it might be time for us to move this discussion out of the
newsgroup.
If you don't mind posting your e-mail (using "dot" for "." and "at" for "@"
will make it less likely to get harvested), I'll contact you there.

- Turtle

 

[julius edlavitch]

edlav001attcdotumndotedu
I'm not sure what harvesting means