Expired vs. Current

[fixious123]

Hello all,

I have put together a simple database of immunizations and now tweaking for better efficency. I want to know whos expired and whos current with an end result of a pie graph or percentage output. I have the date taken box and using the DateAdd function for the expire date based on the shot frequency. How can I calculate this? Thanks for your time

 

[The_Doc_Man]

Don't forget that a graph can be based on a query. So the way I would approach this is...

First, as you point out, your immunizations have lifetimes based on what kind of immunization it was. So I would have a table like this:

tblImmTyp
-- ITYPE: code (PK) - something you like for the key, not necessarily an autonumber
-- INAME: text - long name of immunization type
-- IMAXAGE: integer or long - number of months this particular immunization is good

Then of course you need a person table and an immunizations table

tblPerson
-- PNUM: number (PK) - this might well be autonumber unless you already have another scheme
-- other personal data

tblImmHist
-- PNUM: number (FK)
-- ITYPE: code (FK)
-- WHEN: date

OK, now write a query that joins tblPerson to tblImmHist on PNUM. Include the ITYPE and WHEN fields.

Now write a query that joins the prior query to the tblImmType table to include the IMAXAGE field, at least in a comparison, which maybe looks like this:

Iif( (DateDiff( "m", [WHEN], Now ) - IMAXAGE) > 0, 1, 0 )

Which will be a number less or greater than zero. (Check my math because I shot this one from the hip and I always get the arguments backwards in a DateDiff...). But the idea is that you can include the little IIF in the query to show 1 for expired immunizations and 0 for still-valid ones.

Look up IIF and DATEDIFF in the help files to make sure you get the arguments in the right order, 'cause I wouldn't trust me to get it right the first time off the top of me head like this...

 

[Mile-O]

I always get the arguments backwards in a DateDiff...).

You can always use the Abs() function to correct that.

 

[fixious123]

expire vs. current

Thanks I'll give er' a shot..