Currently I was asked by my medical team to help them make a database to track when people in our unit would be due shots. I still cant figure out how to make the query work. I made a table containing the following fields.
Different immunizations have different renewal times i.e. some are good for a year another is good for3 years etc.
I realize I am probably way out of my league here, but they need this database to work and I am at my wits end. Any help would be so appreciated
SSG Frank Insalaco
Add another field to your table and make it a number field. Let's call that field DaystoNext. In this field you enter the number of days to the next shot. In your Date field enter the date that the shot was done.
Now make a query on the table and add a calculated field which adds your Date field to the new field DaystoNext. Let's call this new field DueDate.
To make such a field in the query is simple.
Just type in the field section of the query grid DueDate:[Date]+[DaystoNext]
Thus if the date of the shot is May 1 and the next shot is due in 90 days then the new created field will show a date that is 1/5/04 plus 90 days.
You can have a form based on this query and add a label to click on or command button which we might call Due for Next Shot. A simple macro is added to the label (attached to OnClick) or the command button.
This macro will contain an openform action which will open your form where the date in the new created field DueDate is the same as the current date.
[DueDate] Like Date()
You could also add another calculated field to the query which we might call OverDue and that will be OverDue:[DueDate]+15 or whatever number of days is deemed suitable as a reminder.
Set the properties of these created field as one of the date display options
Such a data base will be about like a car that has no air conditioning, no seats and you sit on a box to drive it etc. In other words it will get you from A to B.
Refinements that might be added would be a drop down list of the type of shot and calculated field in the query that automatically knows that injection type abc is due in 90 days and injection type xyz is due in 365 days etc.
Basically, the refinements are about automating the data base and also limiting the entry errors. For example if injection type abc is due every 90 days and the 90 is entered automatically then there is no chance that the operator enters a wrong number.
When a shot is given then instead of having someone enter the date of the shot you give them a command button or label that runs a simple macro with a SetValue action. The item would be [Forms]![InjectionForm]![Date] and the expression line would be Date()
By the way I would change the name of your Date field to another name such as DueDate or whatever. Having a field name called Date is bound to cause problems in the future.
I would suggest using three tables to make your life a bit easier.
member_id (Primary Key)
shot_id (Primary Key)
half_life (this would most likely be in months, so this would be an integer field)
Admin (for administered, obviously you can rename these tables to anything you'd like)
admin_id (primary key)
member_id (foreign key)
shot_id (foreign key)
Ok, so given the above table structure, you can create records in the Admin table that will allow you to reference the half-life of any given shot. Then to query for shots that are do, you could use something similar to this:
FROM Member LEFT OUTER JOIN Admin ON Member.member_id = Admin.member_id
LEFT OUTER JOIN Shots ON Shots.shot_id = admin.shot_id
WHERE Shots.shot_id IS NULL OR Admin.admin_id NOT IN
FROM admin INNER JOIN Shots ON Admin.shot_id=Shots.shot_id
WHERE Date() BETWEEN Admin.date AND DateAdd("m", Admin.date, Shots.half_life))
Essentially that query returns any records from the where there is not a follow up shot within the half-life of the original administration OR the shot has never been given.