Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Nashville TN
    Posts
    2

    Unanswered: Old Army Guy needs help

    Hello
    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.
    Name
    Immunization
    Date.
    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
    Thanks
    SSG Frank Insalaco

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Frank,

    A very simple solution would be as follows:

    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.

    Mike

  3. #3
    Join Date
    Dec 2003
    Location
    Colorado
    Posts
    24
    SSG :

    Assuming the "date" field is expiration date, try :

    SELECT * FROM tblName WHERE tblName.Date < Now()

    This should retrieve every record with all fields where the expiration date occurs before today.
    Ray Burrows

  4. #4
    Join Date
    Apr 2004
    Location
    Nashville TN
    Posts
    2
    Thank you both for your responses. I will try both this week and hopefully please a few Docs.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would take an entirely different approach.

    I would suggest using three tables to make your life a bit easier.

    Members
    ----------
    member_id (Primary Key)
    fname (text)
    lname (text)

    Shots
    ---------
    shot_id (Primary Key)
    name (text)
    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)
    date (date/time)


    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:
    Code:
    SELECT *
    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 
                      (SELECT admin_id 
                       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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •