TR Forums

Hey guys,

I'm creating a database for a vaccination clinic in a slum and have never used access before so am a little confused about the Nz and IIF functions.

So I have a table called Table Children and in it I have a Date of Birth field and a Age at the time of first joining the program field. Then I have various vaccinations (tick boxes) and date field next to each. So say P1 (yes/no), Date of P1, P2 (yes/no), Date of P2 etc.

The thing is parents don't know their child's date of birth a lot of the time so they'll give the child's age which is then entered in the field 'Age at the time of first joining the program'

To be calculate a child's age at each vaccine I need date of births.

I want to create a query that basically copies Date of Birth where it is given and where the Date of Birth field is null it subtracts the child's age (retrieved from Age at the time of first joining the program) from the Date of entry (i.e. the day the child joined the program) – i'm assuming that will give me the DOB.

Any help would be appreciated!
Thanks guys!

First, how could a parent not know the birth date of a his or her own child?!

Second, this doesn't seem to be an ideal situation for Access to be used as the database. Are you going to be the only one who ever touches the database forever and ever? Access 2007 is really lacking in the security department and you're just leaving a can of worms around for anybody to open up.

Since you're still in the database design stage, you can go back to the drawing board and pick something a bit more appropriate. There are plenty of free options out there. MySQL, for example, is relatively easy to design, administer and use. (And, no, you don't have to have a Linux box to run it. There is a Windows version as well.)

In fact, it is strongly recommended by professionals that Access not to be used for confidential and/or sensitive data.

Access has a place. It isn't in medical records.

To answer your problem, though, you're looking at flow control. Here's a link I got from Google using "access if then" as the search parameters: http://www.techonthenet.com/access/func ... f_then.php

Note that you can use Access as a front end GUI to another database (in MySQL, the free MS SQL Express, or anything else) using linked tables. And you can start in Access and move the data over later, too.

Subtracting age from the date they entered the program will give you something as long as you use the appropriate units -- if "Age" is in years then you'll want to do something like Years(EntryDate)-Age -- but you're ending up with pretty arbitrary dates for DOB since it's really just the month and day they entered the program, translated into the past. Though I guess that's better than nothing.

(It doesn't come up here, but note you want to avoid subtracting a date from another date -- there's a DateDiff function for that. Subtracting a time span such as an age from a date is fine, though. )

Hi Titan and UberGerbil,
Thanks for the help! So yes I would prefer not to use Access but I'm not a tech person (I majored in History at college!) and everyone else here is health. So we don't know much about tech - we've been using Access because someone here learnt it at college and it's all we know. I would be willing to learn a computer language and code something of my own but I have absolutely no idea how to get started. Also, I don't understand how I'd use the IF THEN ELSE statement.

It's very likely that using Access here violates some policy or HIPAA rule. If you can't convince pepole to spend money doing the database security correctly, at least cover your own ass. Consider writing several emails to people in charge about your concerns, so that's documented fi somethign hits the fan.

Also, there are a few other things that you can do even with Access. Two that come ot mind are (1) change that Access password frequently, and (2) store the .mdb file in a directory that only a few people in the clinic have privileges to. Using encryption at the OS level may be worth the effort as well.

It's not straight-forward to calculate age in Access/VBA because of rounding. This page has a good example, except use your field [Vac1Date] in place of Now(): http://office.microsoft.com/en-us/acces ... 11033.aspx

n09 wrote:

Also, I don't understand how I'd use the IF THEN ELSE statement.

I thought Titan's link had good examples at the bottom. Did that example make sense to you? If so, what are you having trouble with? (Nitpick with the second example of the page: the catch-all Else clause is sloppy because inputs like 'NE' and 'JJJ' still would produce a 'West' region. Especially databases like yours will have a lot of dirty data.)

n09 wrote:

Hi Titan and UberGerbil,
Thanks for the help! So yes I would prefer not to use Access but I'm not a tech person (I majored in History at college!) and everyone else here is health. So we don't know much about tech - we've been using Access because someone here learnt it at college and it's all we know. I would be willing to learn a computer language and code something of my own but I have absolutely no idea how to get started. Also, I don't understand how I'd use the IF THEN ELSE statement.

Pick up Learning MySQL. It's pretty easy to understand. It doesn't cover some of the more advanced topics, but it does teach you how to start with a good DB design, how the SQL language works, and how to use MySQL. (MySQL has begun to become a favorite free DB of mine, once again, as PostgreSQL is a bit more complicated when it comes to designing a database and managing user privileges.)

The problem with MySQL is you'd really need to have a graphical front-end to use in combination with it. For me, it'd be pretty easy to set up a Web page that handles most, if not all, needs. That gets to be pretty involved.

However, the granularity a "real" database offers and the removal of potential user error is worth the effort. For most day-to-day uses, simple read (select), update and insert privileges are all that are required. (I don't know if Access is capable of such granularity.) My primary concern would be everyone having the capability to delete an entry, thereby diminishing the accuracy of the patient records.

As for using the IF...THEN...ELSE... flow control, you'd be testing to see if a certain set of conditions exist, then performing the appropriate actions.

Some pseudo-code would be (pseudo-code being a manner of outlining how the real code should operate):

IF (DOB is not empty and has a valid value)
Return value of DOB
ELSE
Return (Date of first visit minus age)